Forum Discussion
Excel Formula 4-tiers Nested IF (Electricity Bills)
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
- =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
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
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)))
- =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
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)
