Trying to get a 30 day average

[64 Impala]

Hello

I am trying to get excel to give a moving 30 day average of my fuel cost per kilometer based on the fuel I have purchased in that period.

In other words, if I fill up once or twice a week as in the attached spreadsheet, excel will look back 30 days and tell me what the average of Column F is in that period.

Thank you

[quekbc]

Try this formula in cell G2 (and copied down)

Please Login or Register  to view this content.

I've also attached a file with the formula in action, and a separate section to do "checks" with manual adjustments to formulas to see if the calculation logic is in line with what you have in mind.

[FDibbins]

quekbc, nice formula.

However, I think you have something wrong there, the values seem to increase dramatically the further down you go...starting with 0.12001 and ending with 0.65484

Try this instead...
=SUMPRODUCT(--($A$2:$A$1000>A2-30),--($A$2:$A$1000<=A2),($B$2:$B$1000),($D$2:$D$1000))/SUMIFS(C:C,A:A,">="&A2-30,A:A,"<="&A2)

aahh OK, I see from your file that the formula in post 2 was just a "starting" formula. You had an adjusted 1 below that....
=SUMPRODUCT(--(A$2:A2<=A2)*(A$2:A2>=A2-30),B$2:B2,D$2:D2)/SUMPRODUCT(--(A$2:A2<=A2)*(A$2:A2>=A2-30),C$2:C2)

You could actually have used that in G2 as well

Good 1

[quekbc]

Ah, my bad. I pasted the old formula. The attached file in the earlier comment has the new formula in place (G3 onwards) with the formula - which is almost exactly what you posted.

Please Login or Register  to view this content.

OP, one thing to note though, which I noticed through Ford's formula, is in relation to the > and < logicals, whether it should be strictly larger than or larger than or equal to (and vice versa). Does not make an impact in the current example, but may cause differences in output. In any case, if the cost per km do not fluctuate crazily, I don't think the output will differ that much.

[FDibbins]

This is actually a perfect example to show why averaging averages is mathematically wrong.

The way we have both run the calc, we came up with pretty much identical answers, but I have seen some members try and average column F (the cost/Km), using this...
=AVERAGE($F$2:F13)

Using that, gives variations from -2% to +12%....

	A	B	C	D	E	F	G	H	I	J
1	Date	Fuel Added	Km Driven	Cost per Litre	Km per Litre	Cost per Km	30 Day Ave Cost/Km	quekbc	FD	Var
2	15-Jun-15	61.3	500.2	0.980	8.159869	0.1201	0.12010	0.12010	0.12010	100%
3	20-Jun-15	71	656	0.976	9.239437	0.105634	0.11287	0.11189	0.11189	101%
4	23-Jun-15	45.6	450	0.983	9.868421	0.099611	0.10845	0.10845	0.10845	100%
5	29-Jun-15	63.4	583.7	0.998	9.206625	0.1084	0.10844	0.10844	0.10844	100%
6	2-Jul-15	53.2	700.2	1.020	13.16165	0.077498	0.10225	0.10094	0.10094	101%
7	7-Jul-15	12.6	200	1.060	15.87302	0.06678	0.09634	0.09873	0.09873	98%
8	10-Jul-15	37.4	588	0.992	15.72193	0.063097	0.09159	0.09303	0.09303	98%
9	15-Jul-15	66.7	893	1.014	13.38831	0.075738	0.08961	0.08966	0.07651	117%
10	19-Jul-15	73.1	1000.2	0.980	13.68263	0.071624	0.08761	0.08310	0.08310	105%
11	20-Jul-15	33	602	1.010	18.24242	0.055365	0.08438	0.08015	0.06794	124%
12	22-Jul-15	56	812	0.983	14.5	0.067793	0.08288	0.07556	0.07556	110%
13	26-Jul-15	54.312	800.2	1.010	14.73339	0.068552	0.08168	0.07290	0.07290	112%

G2=AVERAGE($F$2:F2)
H2=SUMPRODUCT(--(A$2:A2<=A2)*(A$2:A2>=A2-30),B$2:B2,D$2:D2)/SUMPRODUCT(--(A$2:A2<=A2)*(A$2:A2>=A2-30),C$2:C2)
I2=SUMPRODUCT(--($A$2:$A$1000>A2-30),--($A$2:$A$1000<=A2),($B$2:$B$1000),($D$2:$D$1000))/SUMIFS(C:C,A:A,">="&A2-30,A:A,"<="&A2)
J2=G2/I2
all copied down

[quekbc]

Ford, I'd give you another rep if I could but EF is preventing me from doing so. Says I should spread the love a bit more. An imaginary salute will have to do.

[FDibbins]

Thanks quekbc, its the thought that counts

[FlameRetired]

That's interesting. I am getting the same results up to row 8 with this:

=SUMPRODUCT(--IF(($A$2:A2+30)>=A2,$B$2:B2)*$D$2:D2)/SUMPRODUCT(--IF(($A$2:A2+30)>=A2,$C$2:C2))

Then results differ after that. What am I missing?

Row\Col	A	B	C	D	E	F	G	H	I
1	Date	Fuel Added	Km Driven	Cost per Litre	Km per Litre	Cost per Km	quekbc	FD	FR
2	15-Jun-15	61.3	500.2	0.980	8.159869	0.1201	0.12010	0.12010	0.12010
3	20-Jun-15	71	656	0.976	9.239437	0.105634	0.11189	0.11189	0.11189
4	23-Jun-15	45.6	450	0.983	9.868421	0.099611	0.10845	0.10845	0.10845
5	29-Jun-15	63.4	583.7	0.998	9.206625	0.1084	0.10844	0.10844	0.10844
6	2-Jul-15	53.2	700.2	1.020	13.16165	0.077498	0.10094	0.10094	0.10094
7	7-Jul-15	12.6	200	1.060	15.87302	0.06678	0.09873	0.09873	0.09873
8	10-Jul-15	37.4	588	0.992	15.72193	0.063097	0.09303	0.09303	0.09303
9	15-Jul-15	66.7	893	1.014	13.38831	0.075738	0.08966	0.07651	0.08966
10	19-Jul-15	73.1	1000.2	0.980	13.68263	0.071624	0.08310	0.08310	0.08642
11	20-Jul-15	33	602	1.010	18.24242	0.055365	0.08015	0.06794	0.08339
12	22-Jul-15	56	812	0.983	14.5	0.067793	0.07556	0.07556	0.08158
13	26-Jul-15	54.312	800.2	1.010	14.73339	0.068552	0.07290	0.07290	0.08024

[quekbc]

Hi Dave,

I did not go through your formula in detail, but I notice that your answers are producing the result the same as taking all data into account.

Please Login or Register  to view this content.

EDIT: It seems that you'll need to type it in as an array formula (CTRL SHIFT ENTER). The result is the same as mine after this.

[FlameRetired]

@ quekbc

Don't we also need to qualify C$2:C2 by the same relative date range?

Edit: Aha! I see they are the same (as taking all data into account), but I cannot wrap my mind around why the date qualifiers in my formula don't filter out the figures earlier than current row date -30. F9ing the IF functions reveal 0s or FALSE in all the right places. I would expect my formula to not give the same as if they were not.

Another edit: Yes. I'd never expect to have to CSE a SUMPRODUCT formula such as this. Clearly they are the same results --- the ones I would expect. Thanks for looking at this.

[quekbc]

@FlameRetired

Yes, we do. I was just noting in my post above (#9) that your formula in #8 is producing the same results as what I mentioned in #9 =SUMPRODUCT(B$2:B2,D$2:D2) / SUM(C$2:C2). Which, as you've rightly pointed out, is incorrect.

In the same post (#9), I've also added (via an edit to the original post) that your formula will work once you enter it in as an array formula (i.e. CTRL SHIFT ENTER)

[FlameRetired]

@FlameRetired

In the same post (#9), I've also added (via an edit to the original post) that your formula will work once you enter it in as an array formula (i.e. CTRL SHIFT ENTER)

Yes I see that, now. Am surprised CSE was necessary. It never occurred to me. Rookie error.