Multiplying a Value Range

[ChrisDailly]

This may be simple, but it's really bothering me!

If I have different charges for different weights of material:
eg. 0-1kg costs £10 per kg; 11-20kg costs £20 per kg; 21-30kg costs
£25 per kg etc...

Then is there a function I can use that, when I enter a weight, it
automatically multiplies the weight by the correct cost
(e.g. If I enter 15kg it will multiply by £20 - if I change it to 22kg
then it is then multiplied by £25)?

I'd be grateful of any advice/suggestions.

Thanks

 

[Mike M]

You can get this done by using a lookup table and the
lookup function. Use one column for kg and one for cost
per kg. The vlookup function, I believe, will select the
closest value in the table, but will not exceed the
desired value (i.e., a table using 0,11,21 for lookup keys
would select the 11 value if 20 was input as the lookup
value).

Mike

 

[Peo Sjoblom]

One way

=SUMPRODUCT(LOOKUP(A1,{0;11;21;31},{10;20;25;30}),A1)

note that if the lookup value is empty it will return an error, to bypass
that

=IF(A1="","",SUMPRODUCT(LOOKUP(A1,{0;11;21;31},{10;20;25;30}),A1))

 

[OZZIE]

you could also use (and may be better served) by using a
nest if statement..