How to calculate multi tier tariff?

Hi,

I was looking for a solution for calculating multi tier tariff as shown below:

For example below, 0-50 units cost 3.50, above 50 up to 100 will cost 3.90, etc. Finally if consumption exceeds 200, total units will be charged rate of 6.10

Kw Amount
0-50 3.50
51-100 3.90
101-150 4.80
151-200 5.20
>200 6.10

Regards,
Jishar

Welcome to the forum.

Use VLOOKUP with an approximate match.

There are instructions at the top of the page explaining how to attach your sample workbook.

With 170 in F4 (value)
Try this:

=IF(F4<51,F4*E8,IF(F4<101,175+(F4-50)*E9,IF(F4<151,370+(F4-100)*E10,IF(F4<201,610+(F4-150)*E11,870+(F4-200)*E12))))

Originally Posted by jisharka

Hi,

Thanks for the prompt reply. Everything perfect except if >200, if the consumption is >200, all the above tariff MUST not applicable. It MUST calculate the whole units a rate of 6.10.
For example - if the consumption is 201 the rate must be 1226.1

Can you fix it?

Again thanks for your efforts,

Regards,
Jishar

Try to replace:
870+(F4-200)*E12
with
F4*E12