1

in Excel 2003, I use the following equations to calculate the cost of the electricity consumption. I have to use different equations for different consumption slice situations.

The next figure shows the data used for consumption slices and the cost for each slice:

enter image description here

I put fixed values in cells and refer to them. For example: 0.5 in cell A1 0.82 in cell B1 1 in cell C1

in October the consumption was 190 kwh,

I used the equation =((190*$A$1))+6

in December the consumption was 228 kwh,

I used the equation =((200*$A$1)+(28*$B$1))+11

in August the consumption was 370 kwh,

I used the equation =((200*$A$1)+(150*$B$1)+(20*$C$1))+15

How to use one equation in all the different consumption slice situations, instead of using different equations in different situations.

1

To be able to use formula efficiently you need first to calculate "cost correction" which gives you the difference between calculating with constant price (e.g. 370*1) vs based on price categories (e.g. 200*0.5 + 150*0.82 + 20*1), this will be constant for each category.

The formula for it is:
=-(Sheet1!$A3-1)*(Sheet1!$B3-B2)+D2
(place it from second category and fill it down, for first one it's 0)

Then you can use VLOOKUP to calculate based on cost, added fee & correction:
=Sheet1!$G2*VLOOKUP(Sheet1!$G2,Sheet1!$A$2:$D$5,2,TRUE)+VLOOKUP(Sheet1!$G2,Sheet1!$A$2:$D$5,3,TRUE)+VLOOKUP(Sheet1!$G2,Sheet1!$A$2:$D$5,4,TRUE)

enter image description here

enter image description here

  • Thank you for your answer. I just could not figure out the formulae in D4 and D5 resulting in -127 and -387 respectively – Hany Sep 5 at 14:05
  • Would you please show me the formulae in D4 and D5, because I have other consumption slice data to add; and I could not figure out how to calculate cost correction for them. – Hany Sep 6 at 5:39
  • I tried the following formulae; are they correct? for D4: =-($A$4-1)*($B$4-$B$3)-($A$3-1)*($B$3-$B$2)+$D$2 and for D5: =-($A$5-1)*($B$5-$B$4)-($A$4-1)*($B$4-$B$3)-($A$3-1)*($B$3-$B$2)+$A$2 – Hany Sep 6 at 6:31
  • What is the value of adding +$D$2, because its value is 0? – Hany Sep 6 at 6:51
  • +D2 is there to enable filling down the formula, you just need to fill it down for all rows. Please see updated picture – Máté Juhász Sep 6 at 7:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.