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 (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

  • 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

    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

     

     

     

     

     

     

     

    • dynah rose's avatar
      dynah rose
      Copper Contributor

      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)))

       

       

      • Willy Lau's avatar
        Willy Lau
        Steel Contributor
        =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

    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