Forum Discussion

dynah rose's avatar
dynah rose
Copper Contributor
Jan 28, 2018

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...
Formulas & Functions
Tutorial
  • Willy Lau's avatar
    Willy Lau
    Jan 29, 2018
    =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
Willy Lau's avatar
Willy Lau
Steel Contributor
Feb 05, 2018

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)

 

Resources