Calculate cost of gas usage with different rates

Hi, I am trying to calculate the cost of gas depending on usage. There are 4 different rates for each band. The more you use the cheaper it gets per Mega Joule. The rate are as follows.

First 4000 MJ = 0.023 cent / MJ
Next 5000 MJ = 0.018 cent / MJ
Next 10000 MJ = 0.012 cent / MJ
balance = 0.009 cent / MJ

Now lets say the user has consumed 22000 MJ in the month, how can I calculate this? I can calculate if there are two bands by using IF function but I am having difficulty with four different rates.

Thanks in advance

Try using the methods suggested here

If your set up was:
B2 is input
B5 4000
B6 5000
B7 10,000
B8 >10000

Column C on the same row would be the $.


=IF(B2<B5,B2*C5,IF(AND(B2<B6,B2>B5),C6*B2,IF(AND(B2<B7,B2>B6),C7*B2,IF(B2>B8,C8*B2))))

In other words, in your IF statement, you have condition, value if true, value if false. To use multiple IF statements, at the you put Condition, Value if True, Next If statement Condition, Value if True, continue until the last statement and then ,value if false.

Also, I am very amateur at Excel. I am sure there are other, better ways of doing this, but maybe it will help.

I'm assuming that kryptic wants to apply different rates to different parts of the 22,000 total units. The link I posted shows a method to do that, the other suggestions here will apply the same rate to all 22,000....

Originally Posted by daddylonglegs

I'm assuming that kryptic wants to apply different rates to different parts of the 22,000 total units. The link I posted shows a method to do that, the other suggestions here will apply the same rate to all 22,000....

Yeah different rates to different parts of the 22000 units. The total amount should be $327.

4000 * 0.023 = $92
5000 * 0.018 = $90
10000 * 0.012 = $120
3000 * 0.009 = $27
-------------------------------
22000 = $327

Unfortunately I can't do SUMPRODUCT as its too hard to understand it. I have read through but still can't figure out.

This is what I am using but I get $1142 for 22000 units. I have 22000 in Cell C3. I have 0, 4000, 5000, 10000 in Cell C6:C9 respectively then I have the rates in Cell C19:C22.

=SUMPRODUCT(--(C3>C6:C9), (C3-C6:C9), (C19:C22))

SUMPRODUCT seems for advanced user. What am I doing wrong? Can someone just post the formula please?

Thanks

Sorry, I didn't see your previous reply....

You are almost there. The amounts in C6:C9 should be the cumulative amounts so C6 should be 0 and C7 4000 but then C8 should be 9000 and C9 19000.

Formula can be

=SUMPRODUCT(--(C3>C6:C9), C3-C6:C9,C19:C22-C18:C21)

C18 cell should be blank or zero. For 22000 in C3 that will give the correct result of 329 (not 327).

An alternative formula to give the same result without SUMPRODUCT (but no better)

=IF(C3="","",C3*C22-MIN(C3,C9)*(C22-C21)-MIN(C3,C8)*(C21-C20)-MIN(C3,C7)*(C20-C19))

see attached