Weight decimal as ounces rather than fractions

[LGFN1]

I have a range of cells that is populated with weight amounts and an adjacent column with its pricing for shipping, for example:

0.1	$0.75
0.3	$1
0.4	$1.15
0.6	$1.5
0.12	$2
0.14	$2.15
1	$2.5
1.2	$2.6

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

On the left side you see the weight, entered as following:

0.3 means 3 ounces
0.4 means 4 ounces,
0.12 means 12 ounces
1 means 1 pound
1.2 means 1 pound, 2 ounces

And so on.

I have made this table a reference for a VLOOKUP, so when I enter any amount elsewhere, an adjacent cell will automatically populate with the correct price for its weight.

There's one problem: by default, Excel sees the numbers after the decimal as fractions rather than ounces, so when I enter, in our example, 0.12 for the weight, Excel will give me in return $0.75, rather than the correct amount, which is $2. How do I change this behaviour? What formula will do this for me?

Thank you!

 

[mikerickson]

It seems to me that the pound.oz notation would confuse 2 lb 1 oz with 2 lb 10 oz since 2.1 = 2.10

Unless the data is entered as text, that notation is flawed.

 

[LGFN1]

Wow, genius! Thanks, Stephen!

One question, in such a case, how will you suggest I enter for example, 3.5 ounces? For me .035 is the only method that works, do you suggest anything better?

 

[StephenCrump]

I think we're flogging a dead horse here ...

If Amazon use 0.1 to mean 1oz and 0.15 to mean 15oz, what is 1.5oz?

0.015 makes better "sense", but only if we use .01 for 1oz.

 

[LGFN1]

With Stephen's formula, .1 gives me 0.0625, and .035 gives me 0.1875. Sounds quite good, doesn't it?

 

[LGFN1]

I've noticed a caveat with this formula, though - both .035 (3.5 ounces) and .032 (3.2 ounces) give me the same result of 0.1875, the problem being that the pricing switches to a higher pricing at 3.5 ounces, here even if I enter .032, it gives me the same price as 3.5 ounces!

 

[mikerickson]

If 2.01 is 2 lb, 1 oz and 3.127 is 3 lb 12.7 oz

=16*INT(A1)+100*MOD(A1,1) is the number of ounces.

 

[shg]

I've noticed a caveat with this formula, though - both .035 (3.5 ounces) and .032 (3.2 ounces) give me the same result of 0.1875,

	A​	B​	C​	D​
1​	p.zz​	pounds​	ounces​
2​	0.032​	0.2​	3.2​	B2: =DOLLARDE(A2,16)
3​	0.035​	0.21875​	3.5​	C2: =16*DOLLARDE(A2,16)
4​	2.01​	2.0625​	33​
5​	3.127​	3.79375​	60.7​

 

[LGFN1]

I'd prefer 2.1 to be 2 pounds one once, not 2.01. To get around it perhaps I should add some symbol, or even "oz" after the number when I want it to be ounces rather than pounds. Suggestions?

 

[mikerickson]

Suggestion: two columns for data entry, pounds and ounces.

For downstream calculation, these can be combined into a total pounds helper column.

For output, they can be combined in the format most appropriate for to the situation.

(I assume that the data is coming in as lb/oz rather than decimal lb.)

 

[LGFN1]

There should be a way (although quite a complex formula, I assume) to have Excel check if there's "oz" after the amount, and would calculate it as ounces rather than the default pounds. Any assistance?