Index/Match formula to find average.

**whitehead6682** · 11-17-2011, 02:08 AM

Hi All

I am after your expert advice please.

I want to find out the average number of litres consumed by each driver over a period.

Does anybody know how I can do this?

Please see attached sample.

Thanks for any help.

**TMShucks** · 11-17-2011, 02:14 AM

=SUMIF/COUNTIF OR =AVERAGEIF, maybe?

Or: =SUMPRODUCT

Or: =SUMIFS/COUNTIFS or =AVERAGEIFS

Depending on Excel version and the number of conditions.

Regards

**whitehead6682** · 11-17-2011, 02:22 AM

Thanks TM, I have very basic Excel knowledge, do you know how I would use this in my spreadsheet, I am guessing with a Match formula?

**Paul** · 11-17-2011, 02:36 AM

In cell D1 type "Start Date". In D3 type "End Date". Fill in D2 and D4 with valid dates.

Convert your text "dates" in column A to actual dates. To do so, in E9 use this formula (assumes dd/mm/yy):

=DATEVALUE(LEFT(A9,2)&"/"&MID(A9,4,2)&"/"&RIGHT(A9,2))

Fill that down to E39 (or your last used row). Copy E9:E39. Right-click on A9 and choose PasteSpecial > Values. Delete E9:E39.

Now in B2, to get Chris' average, use:

=AVERAGEIFS($D$9:$D$39,$A$9:$A$39,">="&$D$2,$A$9:$A$39,"<="&$D$4,$B$9:$B$39,A2)

Fill that down as many rows as you have people listed.

Good luck!

**whitehead6682** · 11-17-2011, 05:09 AM

Hi All

Thanks for all your replies.

However I am still have issues with this.

I have updated my sample to show a bit more detail.

Basically, I need a formula to establish MPG average by driver, I say basically, but I am really stuck here.

Any help would be kindly received as always.

**Fotis1991** · 11-17-2011, 06:04 AM

Ηι

One possible solution to the issue you will find the attached file.

It's what you're looking for?

**whitehead6682** · 11-17-2011, 06:14 AM

Thanks Fotis.

That is as close as I have come, but because the count includeds '0' values of MPG the MPG average is incorrect, I need the zero quantinies to be omitted from the count.

Do you have any other idea's?

Thanks again for your help.

**Fotis1991** · 11-17-2011, 06:51 AM

ΗΙ again.

Ok, I will look back.
Of course, I think you know that using the AutoFilter and Subtotal, easily find your challenge!

**Fotis1991** · 11-17-2011, 07:22 AM

Hi

Take a look to the new one.

Is it ok now?

**whitehead6682** · 11-17-2011, 08:27 AM

Hi Fotis

Thanks looks to have done the trick.

Thanks a lot!!!!!

**Fotis1991** · 11-17-2011, 08:32 AM

Hi.

Glad that helped.

If you found the solution to your issue, then please,mark the topic as solved.

**Paul** · 11-17-2011, 02:02 PM

Since the dates dont appear to matter, you can simply use AVERAGEIFS (Excel 2007+) without needing any helper columns.

Assuming your data is laid out as in Fotis' last uploaded file, in C2 use:

=AVERAGEIFS($E$10:$E$67,$B$10:$B$67,$B2,$E$10:$E$67,">0")

Fill that down to C6. No need for columns F, G and H. If you're using Excel 2003 or earlier, other formulas will suffice since AVERAGEIFS does not exist there.

Either this non-array version:

=SUMPRODUCT(--($B$10:$B$67=$B2),--($E$10:$E$67>0),$E$10:$E$67)/SUMPRODUCT(--($B$10:$B$67=$B2),--($E$10:$E$67>0))

Or this array version, confirmed with CTRL+SHIFT+ENTER (not just ENTER):

=AVERAGE(IF(($B$10:$B$67=$B2)*($E$10:$E$67>0),$E$10:$E$67))

Hope that helps!