IF with three conditions

[Ecel Dumbo]

HI , I need help in building a formula to calculate Electrcity charges

Example- Electrcity usage is 31,180 kilo watts

Pricing would be-
1,1kilowatt to 2500 kilo watts would be charge $0.23 per kilo watt
2,Remaining kilo watts would be charges $0.32 per kilo watt
3, If no usage, price is 0

Answer would be = (2500*0.23)+((31180-2500)*0.32)=$9752

Thanks

 

[Khalid NGO]

Hi,

See if this works:
=IF(A2>=2500,(2500*0.23)+(A2-2500)*0.32,A2*0.23)

I have assumed A2 = 31,180 KW

Regards,

 

[David Evans]

Khalid has given you a perfectly good response - as with most things in Excel there are lots of different ways of completing this calculation. If you have a very complex rate schedule - i.e. lots of rate bands, I think a UDF is the best solution, as it produces a simple "one-cell" solution
I've attached a file with how I solve your type of problem and few examples of other means - this is a very similar problem to the prior one you posted, although this is more straightforward. I've create a UDF for your other problem but I need to check it as it's not returning the same as your example ....
I've had a requirement where I needed 50 different rate structures on financial instruments and the UDF was worth it - in this case, I think its overkill, but they are fun to create

 

[Ecel Dumbo]

Thank you very much Khalid. It worked. Hi David, thank you very much for the file. I'll go through it soon to see how I can use it to solve my tiered cases