Improve on "schoolboy" formula

sykes

Well-known Member
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

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Snakehips

Well-known Member
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
Apologies! Typo in the above formula.

jasonb75

Well-known Member
Another way to do the same thing
Excel Formula:
``=SUM(SUMIF(B2:F2,{"Cheap","Max"},C2:G2)*{0.055,0.1293})``

sykes

Well-known Member

Apologies! Typo in the above formula.

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
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

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
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
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.

Replies
0
Views
181
Replies
9
Views
453
Replies
9
Views
269
Replies
12
Views
7K
Replies
10
Views
8K

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,934
Messages
5,834,468
Members
430,288
Latest member
Shinoy98

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?

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

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