# Excel - pounds and ounces



## Gram123 (Mar 15, 2001)

Hello,
I need to create a graph which shows body weights in pounds and ounces. To do this, I'm going to need to make Excel understand that e.g. 6st 11oz = 107lb.

Or, I could enter the weights all in pounds, but I still need the graph to show pounds and ounces on the axis.

Any help?

Cheers,
Gram


----------



## XL Guru (Aug 30, 2003)

Gram,

Try pre-formatting the input cells as Number -- Fraction -- sixteenths. Or custom number format of # ??/16.

Rgds,
Andy


----------



## Gram123 (Mar 15, 2001)

Okay, I've used *# ?"st "?"lb"/16* as the format, so when I enter =107/16 into the cell, it displays *6st 11lb/16*. This is great, but I need to hide the */16* part at the end.

I thought of perhaps changing the text colour of it to white, so it is not visible, but if you enter 
*# ?"st "?"lb"[White]/16*, it just moves the colour to the beginning of the cell format, so the whole cell value is displayed in white.

Is there a way to hide or colour just the /16 part???

Gram


----------



## Gram123 (Mar 15, 2001)

Also, I've noticed, it doesn't quite work if the pounds add up to whole stones.
Entering 112 pounds, gives *7* instead of *7st 0lb* or even just *7st*.

Gram


----------



## XL Guru (Aug 30, 2003)

I'm lost as to what you're doing (& how) ; 107lb <> 6st 11oz or even 6st 11lbs. However, (6*16)+11 does = 107 ; ???

I managed to find a custom format of

0 "lbs" 00 "ozs"

This will read input 506 as "5 lbs 06 ozs" (health warning - it'll accept 1234 & read it as "12 lbs 34 ozs", so you need to watch for that).

I found the format would work on a chart axis (see attached), but was temperamental (arbitrary rescaling).

Please confirm whether you need to sum (initially you were just charting) as this would require more work.

Rgds,
Andy


----------



## Gram123 (Mar 15, 2001)

Oh my god, I'm such an eejit. Stones and ounces, what was I thinking? Yes, of course it should have been Stones and Pounds, 14lb to a stone (not 16!!).

No, I wouldn't need to do any maths on it, no sums etc - I may need to do some averages, but I can do that in pounds only before converting it for display on the graph (chart).

Thanks Andy, I think your cheeky custom format will suffice.

Gram


----------

