Improve on "schoolboy" formula

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,785
Office Version
  1. 365
Platform
  1. Windows
Folks
I'm analysing the daily total of EV charging costs.
I'm allowing for a total of 3 charges per day ("Charges 1-3") - to allow for different vehicle movement schedules, and therefore possible charge opportunities.
I want to apply 3 different charging "types", namely "Cheap" (overnight/off peak) = £0.055/kWh), "Max" (day/peak) = £0.1293/kWh) and "E+" (purely from solar PV, which I'm considering as free).
Cols B, D & F hold the 3 charge "types."
Cols C, E & G hold the charge vol delivered to the EV's battery, during each charge session.
In col H, I'm wishing to total the cost of that day's charging activities. I'm only needing to consider "Cheap" and "Max" charges, which makes life a little more simple.

There are more analytical cols after col H, but I've omitted these, as their content isn't relevant to my question.

I do have a formula in col H, which works, but it seems very "Schoolboy" to just use a series of "Ifs".

Any more slick way of achieving this?

I had a look at SUMIFS, but couldn't get it to work.

TIA



Scott electricity calcs.xlsm
ABCDEFGH
1DateCharge 1 (type)charge vol kWhCharge 2 (type)charge vol kWhCharge 3 (type)charge vol kWhDaily cost £
2Thu-11-Mar-21E+10.00
3Fri-12-Mar-21Cheap4.70.26
4Sat-13-Mar-21Cheap7E+6Max30.77
e2008
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,154
Office Version
  1. 2013
Platform
  1. Windows
You could try this in H2 and copy down....

=(SUMIF(B2:G2,"Cheap",C2:H2)*0.055)+(SUMIF(B2:G2,"Max",C2:H2)*0.1293)

Hope that helps.
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,154
Office Version
  1. 2013
Platform
  1. Windows
Apologies! Typo in the above formula.

Should read =(SUMIF(B2:F2,"Cheap",C2:G2)*0.055)+(SUMIF(B2:F2,"Max",C2:G2)*0.1293)
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,599
Office Version
  1. 365
Platform
  1. Windows
Another way to do the same thing
Excel Formula:
=SUM(SUMIF(B2:F2,{"Cheap","Max"},C2:G2)*{0.055,0.1293})
 
Solution

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,785
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Apologies! Typo in the above formula.

Should read =(SUMIF(B2:F2,"Cheap",C2:G2)*0.055)+(SUMIF(B2:F2,"Max",C2:G2)*0.1293)
Thanks Tony

This works.
The only thing I'm not getting my head around, is how Excel "knows" to reference the adjacent cell to the right of the one where the searched value is found.
I realise that the 3rd argument is a range offset by one, to the first argument, but didn't realise that the summed cell would also be offset by the same degree.
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,785
Office Version
  1. 365
Platform
  1. Windows
Another way to do the same thing
Excel Formula:
=SUM(SUMIF(B2:F2,{"Cheap","Max"},C2:G2)*{0.055,0.1293})
Thanks Jason
As this is the first time I've actually used an array formula, I've gone with your solution. I like the fact that it's so curt.

For the avoidance of any doubt (for anyone else looking at these solutions), that provided by @Snakehips yielded just the same result.

I've marked this post as the solution, purely because I don't think I can mark them both (although I will try to, once I've posted this.)
 

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,154
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

Thanks Tony

This works.
The only thing I'm not getting my head around, is how Excel "knows" to reference the adjacent cell to the right of the one where the searched value is found.
I realise that the 3rd argument is a range offset by one, to the first argument, but didn't realise that the summed cell would also be offset by the same degree.
As the 'criteria ' range and the 'sum' range are, of necessity, matching in dimension, the pattern of 'Trues' found in the criteria range will reflect the values to sum, when applied to the sum range.
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,785
Office Version
  1. 365
Platform
  1. Windows
As the 'criteria ' range and the 'sum' range are, of necessity, matching in dimension, the pattern of 'Trues' found in the criteria range will reflect the values to sum, when applied to the sum range.
Thank you Tony.

A very eloquently-put response!
That now makes perfect sense, and will, hopefully, be of help to others referencing this thread.
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,599
Office Version
  1. 365
Platform
  1. Windows
Not criticising in any way, Tony, just a bit of info that you may not be aware of.
As the 'criteria ' range and the 'sum' range are, of necessity, matching in dimension,
With SUMIF it is possible to break that theory using
Excel Formula:
=SUM(SUMIF(B2:F2,{"Cheap","Max"},C2)*{0.055,0.1293})
or
Excel Formula:
=SUM(SUMIF(B2,{"Cheap","Max"},C2:G2)*{0.055,0.1293})
The first of which will return the correct result, the second will not.

This anomaly is only possible (to the best of my knowledge) with SUMIF and AVERAGEIF, when the value and criteria ranges are of different sizes the criteria range is resized to match the values range. If the criteria range is smaller then the formula also becomes volatile, it is possible that a smaller values range will make the formula volatile as well, I have not done any testing to check this.

SUMIFS does not behave the same way and instead will give you a #VALUE! error if the ranges are not equal in size.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,352
Messages
5,635,780
Members
416,880
Latest member
kyleswanson

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top