Threshold Weight

[swastik]

Hi, I am looking for a formula to calculate freight charge based on slabs (Min & Max Threshold Weight).

Rate Chart
Threshold Weight (KG)
Minimum Maximum Rate (Rs)
0 50 5
51 250 10
251 500 12
501 1000 15
1001 5000 25
5001 7500 40
7501 10000 60
10001 100000 100

Total Weight Price
623 ?? <= Formula required

50 250
250 2,500
250 3,000
73 1,095
623 6,845 <= Expected Result

A sample worksheet is attached. Would very much appreciate help. Regards. Swastik
--------
Mod Edit: Please ask questions in a new thread, not in the "Introduce yourself" thread.

 

[Luke M]

Since your rates are not all linear, I don't think your expected result is correct. My your example, 623 would be a rate of 15, and 15 * 623 = 9,345
Formula to do that, in G5, would be:
=LOOKUP(F5,B6:D13)*F5

Or, if you were really talking about the individual items below (F8:F15), formula in G8 is:
=LOOKUP(F8,$B$6:$D$13)*F8