I have a range of cells that is populated with weight amounts and an adjacent column with its pricing for shipping, for example:
<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!
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!