Average for given period

[Muthukrishnan V]

Excel 2007


Column A contains dates in dd-mm-yyyy format.
Continuous dates like 07-08-2019, 08-08-2019, 09-08-2019 and so
on.


Column B contains numbers (round numbers) in numericals.



I have a helper column dd-mm-yyyy in E5 for Start Date and
E6 for End Date



My requirement: To get average of numbers in Column B for the
given period E5 to E6.


Request formula, Thanking you,

 

[Joe4]

One way is to use a SUMIFS formula to sum all the values that fall into that date range, and then divide by a COUNTIFS formula that counts all the values that fall into that date range, i.e.

=SUMIFS(B:B,A:A,">=" & E5,A:A,"<="&E6)/COUNTIFS(A:A,">=" & E5,A:A,"<="&E6)

 

[JV0710]

You could also use Averageifs

=AVERAGEIFS(B:B,A:A,">="&E5,A:A,"<="&E6)

 

[Joe4]

You could also use Averageifs

=AVERAGEIFS(B:B,A:A,">="&E5,A:A,"<="&E6)

I keep forgetting about that one - and it is a little simpler!

 

[Muthukrishnan V]

You could also use Averageifs

=AVERAGEIFS(B:B,A:A,">="&E5,A:A,"<="&E6)

I get #Div/0!

 

[Joe4]

I get #Div/0!

That usually means that no records meet your criteria, so it is trying to divide by 0, which is not possible.
If you like, you can "hide" that error message by wrapping it in an IFERROR function, i.e.

[COLOR=#333333]=IFERROR(AVERAGEIFS(B:B,A:A,">="&E5,A:A,"<="&E6),"")[/COLOR]

would return a blank while

[COLOR=#333333]=IFERROR(AVERAGEIFS(B:B,A:A,">="&E5,A:A,"<="&E6),0)[/COLOR]

would return 0.

 

[Muthukrishnan V]

Thank you Sirs,

 

[Joe4]

You are welcome.