pounds, ounces, grams

[Katieg918]

Hi,
I am trying to set up a formula in a recipe where ingredients are measured by weight. Most things are measured in pounds, but where there are amounts less than one, i would like the remainder to show up as ounces or grams. example below:

 

[rpaulson]

in B2 =(A2-INT(A2))*16
in C2 =B2*28.35

fill down as needed

 

[Fluff]

How about

Book1
	A	B
1
2	25	0
3	0.75	12
4	1.75	12
5	0.5	8
HPV

Cell Formulas
Range	Formula
B2:B5	B2	=DOLLARFR(A2-INT(A2),16)*100

 

[Fluff]

For grams as well

Book1
	A	B	C
1
2	25.1	1.6	45.359237
3	0.75	12	340.1942775
4	1.75	12	340.1942775
5	0.5	8	226.796185
HPV

Cell Formulas
Range	Formula
B2:B5	B2	=DOLLARFR(A2-INT(A2),16)*100
C2:C5	C2	=CONVERT(A2-INT(A2),"lbm","g")

 

[Katieg918]

in B2 =(A2-INT(A2))*16
in C2 =B2*28.35

fill down as needed

thanks this is perfect! now i'm really being picky, is there any way to not have those quantities less than 1 show up in the pounds, and only show up where needed as ounces or grams?

 

[Fluff]

You could format the column to show whole numbers only, but that will round numbers up, so 14.875 would show as 15.

 

[Katieg918]

You could format the column to show whole numbers only, but that will round numbers up, so 14.875 would show as 15.

right I can' have the numbers rounding

 

[jmacleary]

Hi Katie.

In that case introduce a helper column for lb and use the formula =ROUNDDOWN(C3,0) - where C3 is the total lb.

 

[Fluff]

How about

Book1
	A	B	C	D
1
2	11.375	11	6.00	170.10
3	5.6875	5	11.00	311.84
4	1.75	1	12.00	340.19
5	0.1388	0	2.22	62.96
6	14.875	14	14.00	396.89
7	11.375	11	6.00	170.10
HPV

Cell Formulas
Range	Formula
B2:B7	B2	=INT(A2)
C2:C7	C2	=DOLLARFR(A2-B2,16)*100
D2:D7	D2	=CONVERT(A2-B2,"lbm","g")

and then hide col A, although I'm not sure where you are getting the figures for Grams from

 

[Katieg918]

That is great.
Is there a way I can only have grams appear if the ounces are not a whole number?