Transport problem with Excel

[MIchel Khennafi]

0 500 1,001 2,001 5,001 10,001 20,001 30,001 40,001
499 1,000 2,000 5,000 10,000 20,000 30,000 40,000 999,999
$ 124.80 $ 57.29 $ 46.30 $ 37.81 $ 32.06 $
26.07 $ 21.84 $ 16.33 $ 12.33 $ 11.13



In the table below, I have weight ranges (from 0 to 499, from 500 to 1,000
....).

I would like to enter a weight in a cell A1 and:

a) have a formula in a cell A2 that determines the amount I have to pay. For
instance entering 256 would give me 57.29 $

b) Deficit weight calculation: I would like to have a formula in the cell A3
that compares the price in the actual weight range and the price if I was to
use the next class; for instance if I enter 424 in A1, it really corresponds
to the first weight class and I would get $57.29 but if I enter 425 it is
more advantageous for the customer to be charged the price of the next class

Has anyone solved this type of problem?

Can someone help me get the formula to get the results...

I tried to play with formulas like (--(name=$G$6),--(from>H6),--(to<=I6) and
I am lost now...

Thanks so much

 

[Richard Buttrey]

Can you explain how you get 57.29 for a)

The first band appears to be 0-499, and the first amount 124.80
Hence how does a weight of 256 (within the first band) give 57.29?

Rgds

0 500 1,001 2,001 5,001 10,001 20,001 30,001 40,001
499 1,000 2,000 5,000 10,000 20,000 30,000 40,000 999,999
$ 124.80 $ 57.29 $ 46.30 $ 37.81 $ 32.06 $
26.07 $ 21.84 $ 16.33 $ 12.33 $ 11.13



In the table below, I have weight ranges (from 0 to 499, from 500 to 1,000
...).

I would like to enter a weight in a cell A1 and:

a) have a formula in a cell A2 that determines the amount I have to pay. For
instance entering 256 would give me 57.29 $

b) Deficit weight calculation: I would like to have a formula in the cell A3
that compares the price in the actual weight range and the price if I was to
use the next class; for instance if I enter 424 in A1, it really corresponds
to the first weight class and I would get $57.29 but if I enter 425 it is
more advantageous for the customer to be charged the price of the next class

Has anyone solved this type of problem?

Can someone help me get the formula to get the results...

I tried to play with formulas like (--(name=$G$6),--(from>H6),--(to<=I6) and
I am lost now...

Thanks so much

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________