Pounds & Ounces

[Bob....]

How do I convert a number such as 15.152 to pounds and ounces where 15
is the pounds and .152 is 15 ounces and 2 one hundredths? What is the
formula for this?

Thanks in advance,

Bob....

 

[Don Guillett]

multi posted

 

[Bob....]

I posted this first on a news group I didn't know it would post here as
well.

Sorry again,

Bob....

 

[Don Guillett]

Best to post in ONE cuz most that will help look at all. MISC would have
probably been best for this. I played with it and couldn't figure it out.
Good luck.

 

[Bob Phillips]

Bob,

Is this what you want

=INT(A1)&" lbs"&INT((A1-INT(A1))*100)&" ounces"&IF(INT(A1*100)/100<>A1,
TEXT((A1-INT(A1*100)/100)*1000,"0")&" hundredths","")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Ron Rosenfeld]

How do I convert a number such as 15.152 to pounds and ounces where 15
is the pounds and .152 is 15 ounces and 2 one hundredths? What is the
formula for this?

Thanks in advance,

Bob....

1. I'm wondering if you are expressing your problem correctly. I see no way
to differentiate, for example, two one hundredths from twenty one hundreths in
your entry. Excel would treat .152 the same as .1520

2. What do you want the result to look like?


--ron

 

[Bob....]

Okay what I want is the following. I"m trying to write a spreedsheet for
a local bass clubs fishing tournament to keep up with the weights per
tournament and for the year tyo date totals.

Their scales reads out as say a 5.56 pound bass at 5.05.6 pounds.
(Five pounds, five ounces and 6 one hundredths of an ounce). If I enter
the weight in as 5.056 I can convert it with the
=TEXT(INT(H85),"0") & " lbs " & TEXT(MOD(H85,1)*100,"0.0") & " ozs"
formula which would change it to read: 5 lbs 5.6 ozs.

The problem is that once the ounces goes over 16 it will not convert
the 16 ounces to a pound but go to 100 ounces. I also want to be able
to total a running weight for the year to date standings. With the
=TEXT formula it will not sum the values of the weights.

If I do a =CONVERT I still get the same problem with the 16 not
converting to a pound but to 100 ounces.

Bob....

 

[Ron Rosenfeld]

Okay what I want is the following. I"m trying to write a spreedsheet for
a local bass clubs fishing tournament to keep up with the weights per
tournament and for the year tyo date totals.

Their scales reads out as say a 5.56 pound bass at 5.05.6 pounds.
(Five pounds, five ounces and 6 one hundredths of an ounce). If I enter
the weight in as 5.056 I can convert it with the
=TEXT(INT(H85),"0") & " lbs " & TEXT(MOD(H85,1)*100,"0.0") & " ozs"
formula which would change it to read: 5 lbs 5.6 ozs.

The problem is that once the ounces goes over 16 it will not convert
the 16 ounces to a pound but go to 100 ounces. I also want to be able
to total a running weight for the year to date standings. With the
=TEXT formula it will not sum the values of the weights.

If I do a =CONVERT I still get the same problem with the 16 not
converting to a pound but to 100 ounces.

Bob....

Bob,

I'm still confused.

What would the scale read if the fish weighed Five pounds, five ounces and six
tenths of an ounce?




--ron

 

[Bob....]

Scale would read as 5.05.6 but I know I can't enter the weight with 2
decibel points so I enter the weight as "5.056".

Thanks,

Bob...

 

[Ron Rosenfeld]

Scale would read as 5.05.6 but I know I can't enter the weight with 2
decibel points so I enter the weight as "5.056".

Thanks,

Bob...

Still confused. You're saying the scale will read the same for two different
weights, and that you'll enter the same numbers:


You've written:

5.05.6 pounds (Five pounds, five ounces and 6 *one hundredths* of an ounce)
enter the weight in as 5.056

I then asked about:

Five pounds, five ounces and six *tenths* of an ounce

And you responded:

Scale would read as 5.05.6

enter the weight as "5.056"

----------------
So according to your system there is no difference between 6 tenths of an ounce
and 6 one hundredths of an ounce.

I think that will make conversions very difficult.


--ron

 

[Bob....]

Okay I meant to say 10th of an ounce instead of 100th of an ounce.

Bob...

 

[Ron Rosenfeld]

Okay I meant to say 10th of an ounce instead of 100th of an ounce.

Bob...

Ok, Now I have some ideas.

For the purpose of summing the weights, I would convert them to decimal
numbers, and them convert back to lbs, oz's and tenths of oz's for the display.

One way to do this would be to set up three columns:

Column A: Individual Weights entered as lbs & oz's
Column B (or some hidden column): Column A converted to decimal pounds

Formula: =DOLLARDE(A2,16)

If this function is not available, and returns the #NAME? error, install and
load the Analysis ToolPak add-in.

Column C: A running total of the weights expressed as lbs, oz's and tenths of
oz's.

Formula (in C2)

=INT(dollarfr(SUM($B$2:B2),16))&" LBS "&
TEXT(MOD(dollarfr(SUM($B$2:B2),16),1)*100,"0.0 \o\z\s")

and drag/copy down the formulas in B2 and C2


--ron

 

[Ron Rosenfeld]

Formula (in C2)

=INT(dollarfr(SUM($B$2:B2),16))&" LBS "&
TEXT(MOD(dollarfr(SUM($B$2:B2),16),1)*100,"0.0 \o\z\s")

and drag/copy down the formulas in B2 and C2

One other thought. If you don't mind a display of 14 lbs 6.2 ozs looking like

14 lbs 06.2 oz's, you could use the simpler formula in C2:

=dollarfr(SUM($B$2:B2),16)*100

and then use the Custom Format:

#" lbs "#0.0" ozs


The value in the cell would still be a number which you could manipulate if
that's important.



--ron

 

[aali]

Use this formula to convert a number into Pounds and Ozs.

=INT(CONVERT(CELL REF,"kg","lbm"))&" lbs " &INT( MOD(CONVERT(CELL
REF.,"kg","ozm"),16))& " oz"

It will help you out.

Thanks

 

[Bob....]

Thanks for all your help I've got it working now.

Bob....