Forum Discussion
Solved
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...
- =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
