Thread: Simple problem solving - data range comparison

Hi there,

Hope you guys can help me, I've bent my brain over this simple problem for a couple of hours and for some reason I cannot see the light.

I have data for 2 weeks which tells me the Miles Per Gallon fuel consumption of a car each day. I also know the Volume in Gallons consumed on those days - this then allows me to calculate the distance covered.

How do I work out how more or less fuel economic the second week was compared to the first?

My initial thought is that I simply take the total distance during the first week, divided by the total gallons consumed during that time, and then subtract the same values for the second week. However, is there a "smooth way" I can do this without having to calculate the distance column - IE just use the MPG and/or Gallons inputs?

If you guys could let me know if I'm right in my idea, if there's any shortcut way to remove the need for the Distance column and confirm my result that'd be great. I get 1.77 less MPG in week 2 compare to week 1 - IE a drop of of 1.77 MPG



Thanks guys.

At first blush, I'd say of course you can. Let be the fuel efficiency, in MPG, for day Let be the volume of gas consumed, in gallons, for day Then the average fuel efficiency for week one, in MPG, can be computed by a "weighted average" as follows:



Note that is the distance traveled for day So, basically, what I'm doing is not computing any of the intermediate distances, but plugging, in turn, their formulas into the final formula. See how that works?

Spot on, thanks very much for your help.

The formula you've arrived at is pretty much what I had scrawled on my pad as an idea, so its a relief to know I was in the right ballpark. I dont know how you are with Microsoft Excel - Dont suppose you know what the equivalent of that equation would be in excel?

About the best I can think is to do something like (e1V1+e2V2+e3V3+e4V4+e5V5+e6V6+e7V7)/(V1+V2+V3+V4+V5+V6+V7) to figure out week one MPG? Is there a quick root there that you're familliar with?

Last thing to ask of you, what is the reason one cannot cancel out the upper Vi and lower Vi (since that is effectively multiply by V, then divide. I know they dont cancel but it was a while ago I did this in lessons, and I could do with putting my mind at rest as to why they dont cancel.

Thanks again.
Mark

For the numerator, look up the SUMPRODUCT function in Excel. The denominator you can get with the SUM function.

You can't cancel things, because the Vi are not factors: they're added into the denominator.