Excel Formula 4-tiers Nested IF (Electricity Bills)

[dynah rose]

I have 4-tiers electricity tariff of nested IF for excel formula. I know how to calculate it by using manual, but to make formula in excel, i am quite not sure how to do it. The situation is as below (electricity bill):

A. 0001 - 0600 kWh - $0.01
B. 0601 - 2000 kWh - $0.08

C. 2001 - 4000 kWh - $0.10

D. 4001 and above - $0.12

Situation: Calculate cost electricity for 3145 kWh. Manually i calculated like this and i got the correct answer.

C - B

3145 - 2000 = 1145

1145 x $0.10 = $114.5

B - A

2000 - 600 = 1400

1400 x $0.08 = $112

A = 600 x $0.01 = $6

Totol up everything: $114.5 + $112 + $6 = $232.5

[#2]

A

=MIN(<kWh>,600)*0.01

B

=MAX(0,MIN(1400,<kWh>-600))*0.08

C

=MAX(0, MIN(2000, <kWh>-2000))*0.1

D

=MAX(0, <kWh> - 4000)*0.12

[#3]

Hai Willy, I tried the formulas above and combined it into 1 cell and 1 formula. But it seems something wrong. can u lead me this.

=IF(MIN(G7,600)*0.01, IF(MAX(0,MIN(1400,G7-600))*0.08,IF(MAX(0,MIN(2000, G7-2000))*0.1, IF(MAX(0, G7-4000))*0.12)))

[#4 · Accepted solution]

=MIN(G7,600)*0.01 +MAX(0,MIN(1400,G7-600))*0.08+MAX(0,MIN(2000, G7-2000))*0.1+MAX(0, G7-4000)*0.12

[#5]

Hi, dynah.  I just learnt new thing from another post, If Function Question.

A + B + C + D

=<kWh> * 0.01 + MAX(0,(<kWh>-600)*0.07) + MAX(0, (<kWh>-2000)*0.02) +
 MAX(0, (<kWh> - 4000)*0.02)