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:

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]0.1[/TD]
[TD="width: 64, align: right"]$0.75[/TD]
[/TR]
[TR]
[TD="align: right"]0.3[/TD]
[TD="align: right"]$1[/TD]
[/TR]
[TR]
[TD="align: right"]0.4[/TD]
[TD="align: right"]$1.15[/TD]
[/TR]
[TR]
[TD="align: right"]0.6[/TD]
[TD="align: right"]$1.5[/TD]
[/TR]
[TR]
[TD="align: right"]0.12[/TD]
[TD="align: right"]$2[/TD]
[/TR]
[TR]
[TD="align: right"]0.14[/TD]
[TD="align: right"]$2.15[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]$2.5[/TD]
[/TR]
[TR]
[TD="align: right"]1.2[/TD]
[TD="align: right"]$2.6[/TD]
[/TR]
</tbody>[/TABLE]

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!

 

[joeu2004]

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.
[....]
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?

You neglect to tell us how you are calling VLOOKUP. I presume the 4th parameter is TRUE or omitted (defaults to TRUE). And I presume your intent is: for 5 ounces, return $1.15, the amount associated with 4 ounces.

I'm not a fan of your method of recording pounds and ounces. I'm sure it will lead to problems elsewhere.

But to make it work with VLOOKUP(...,TRUE), you should use 0.03, 0.04 and 1.02 to represent less than 10 ounces.

 

[LGFN1]

Thanks for responding,

Yes, I omitted the last parameter, and yes, 5 ounces should return $1.15, in effect.

Perhaps I'm mistaken (or rather, the rep I asked), but when Amazon writes 7.4 pounds, they imply 7 pounds and 4 ounces, and I understand this is the industry standard (I'm fairly new to this), so I don't see any better way to go with this. Hey, if you can guide me otherwise I'd appreciate it.

Thanks for your suggestion, at a glance it looks legit and workable, I've gone ahead and changed it accordingly, but I'd like to hear what others have to say regarding this issue, and the standard way to deal with this.

 

[shg]

If 0.12 means 0 pounds 12 ounces, then 0 pounds 3 ounces must be 0.03, not 0.3

 

[joeu2004]

Perhaps I'm mistaken (or rather, the rep I asked), but when Amazon writes 7.4 pounds, they imply 7 pounds and 4 ounces, and I understand this is the industry standard

I would expect 7.4 pounds to mean 7 pounds plus 4/10 pounds. That is what any person educated the US would think at first glance. Ordinarily, I would complain vociferously if they misuse such a common decimal notation. But since 4/16 is smaller, frankly we have no legal recourse.

(Unless you are the recipient, not the payer, of the shipping charges.)

Nevertheless, the obvious ambiguity makes me suspicious of what you were told.

 

[StephenCrump]

I share the concerns expressed above about ambiguity and potential problems ...

but if you do want to convert between this notation and standard, you might find the DOLLARDE and DOLLARFR functions useful, e.g.

A1: 1.75 (1.75 lbs = one and three quarter pounds)
A2: = DOLLARFR(A1,16) = 1.12 (1 lb 12 oz in alternative notation)

OR

Weight of two parcels of 1 lb 12 oz
=2*DOLLARDE(1.12,16) = 3.5 (i.e. 3 and a half pounds).

 

[LGFN1]

Thanks all for your replies and your concern regarding the legitimacy of this method, I've gone ahead and inquired Amazon again regarding this issue, I will wait it out to see how they respond.

 

[LGFN1]

This is what I got in reply from Amazon:
Please note that 4.7 pounds means 4 pounds and 7 ounces and not 4 pounds + 7/10 pounds.

 

[LGFN1]

Thanks Stephen,

I see your method and it is working per se, but I don't get how to implement this in my above situation with the VLOOKUP. Please advise.

Thank you.

 

[StephenCrump]

So in Amazon parlance, 1.12 is bigger than 1.6. That's going to create problems with any VLOOKUP.

joeu2004 was right. Let's convert to standard decimal notation.

Perhaps:

A1: '1.2 (1 lb 2 oz in Amazon-speak)

A2: =INT(A1)+IFERROR(VALUE(MID(A1,1+FIND(".",A1),2)),0)/16

= 1.125 pounds

Note that I have entered A1 as text. If you use numbers rather than text, Excel won't differentiate between 1.1 and 1.10, which in Amazon-speak are not the same.