Solved: Multiple "IF" Statement

Discussion in 'Business Applications' started by floydcojacket, Jan 14, 2013.

Not open for further replies.

Joined:
Jul 4, 2008
Messages:
278
MS Excel 2007.
My electric bill has a graduated cost per KWH usage.
Total KWH used =1213.
1st 650 KWH =.052 Cents Per KWH
Next 350KWH=.045 Cents Per KWH
Over 1000 KWH=.042 Cents Per KWH

Is there a way to enter a less than and a more than factor in an "If" statement?

The 1st and last part is easy, but the middle "Next 350 Per KWH" gives me a problem.
I have a formula for making it work but it is very lengthy.
Is there a simple formula for computing this amount?

2. etafModerator

Joined:
Oct 2, 2003
Messages:
65,252
First Name:
Wayne
yes , you could use

IF ( Kwh_CELL > 1000 , ((kwh_CELL - 1000)*.042)+(350*0.045)+(650*0.52) , IF(Kwh_CELL > 650, ((kwh_CELL - 650)*.045)+(650*0.052) , ((kwh_CELL * 0.052))

ie
=IF(A1>1000,((A1-1000)*0.042)+(350*0.045)+(650*0.52),IF(A1>650,((A1-650)*0.045)+(650*0.052),((A1*0.052))))

attached spreadsheet - use A2 to enter the Kwh - and the result is in D2

Note the > means that it needs to be greater than 650 for example
so 650 will be @ 0.052
and 651 will be 1 at 0.045

Attached Files:

• KWH_ETAF.xlsx
File size:
10.2 KB
Views:
26

Joined:
Jul 4, 2008
Messages:
278
Thank you so much.
Have a great day

As Seen On