need numbers to be in weight format

Posted by Tracy on November 16, 2001 9:19 AM

I am not very experienced with spreadsheets so I hope someone can help me. I am working on a spreadsheet and have a column of numbers that I would like to be in weight format. I am adding 6 oz to each line and when the number gets to 16.6, I would like it to read 1 lb 6 oz and so on. Is there a way to do this?
thanks in advance

Posted by Barrie Davidson on November 16, 2001 9:34 AM

How will 1 lb 12 oz show up in the cell?

BarrieBarrie Davidson

Posted by IML on November 16, 2001 9:34 AM

If your number is in A1, the formula
=FLOOR(A1,16)/16&" lb(s) "&ROUND((A1-INT(A1))*10,0)&" ozs"
would convert to your desired result. But I think you may have a bigger problem

How would 1 lbs 1 ounce be shown = 16.1
How would 1 lbs 10 ounce be show = 16.10 ???

How are you adding 6 ounces?

Posted by tracy on November 16, 2001 9:58 AM

Let me see if I can explain better

In column B1 I have 10 (ounces)
in columb B2 I have =B1+0.6 and produces 10.6

This continues until 31 oz. I would like these number to show up as 1 lb 3.6 ounces etc.

Am I making sense?
Thanks

Posted by IML on November 16, 2001 10:24 AM

I think I got it.
Try
=FLOOR(B1,16)/16&" lb(s) "&(ROUND(B1-FLOOR(B1,16),1)&" ozs")
in cell B1 and copy it down.

Posted by Daniel P. on November 16, 2001 10:40 AM

I think this will work. I wrote the example using this formula in cell B1, reading weights in A1.

=IF(A1>16,CONCATENATE(ROUNDDOWN(A1/16,0)," lbs. ",ROUNDDOWN(A1-(ROUNDDOWN(A1/16,0))*16,1)," oz."),CONCATENATE(A1," oz."))

Hope this helps!

Posted by Tracy on November 16, 2001 10:49 AM

Thanks for your help, I REALLY appreciate it!