Thread: Calculate Averages

Hi,

I need to create a formula to use each month to calculate the averages base on date ranges. I was able to create a formula to sum the data, base on the date range. However, that's just half of the equation. I need help with the countif formula, to add to my formula. Please see attached spreadsheet.

I want to be able to paste new data each month, in the grey cells, and have the averages calculate in the yellow cells.

Please help,
Dee

In your example.

B77: =SUM(IF(TEXT($A$4:$A$70,"mm-yyyy")=TEXT($B$73,"mm-yyyy"),$B$4:$B$70))/COUNT(IF(TEXT($A$4:$A$70,"mm-yyyy")=TEXT($B$73,"mm-yyyy"),1))

B78: =SUM(IF(TEXT($A$4:$A$70,"mm-yyyy")=TEXT($B$74,"mm-yyyy"),$B$4:$B$70))/COUNT(IF(TEXT($A$4:$A$70,"mm-yyyy")=TEXT($B$74,"mm-yyyy"),1))

... confirm both formulas with Ctrl+Shift+Enter

EDIT:

or

B77: =SUMPRODUCT((TEXT($A$4:$A$70,"mm-yyyy")=TEXT($B$73,"mm-yyyy"))*$B$4:$B$70)/SUMPRODUCT((TEXT($A$4:$A$70,"mm-yyyy")=TEXT($B$73,"mm-yyyy"))*1)

B78: =SUMPRODUCT((TEXT($A$4:$A$70,"mm-yyyy")=TEXT($B$74,"mm-yyyy"))*$B$4:$B$70)/SUMPRODUCT((TEXT($A$4:$A$70,"mm-yyyy")=TEXT($B$74,"mm-yyyy"))*1)

I am assuming you are calculating the average over the number of days in the month.

If you are averaging on the values in column B then:

B77: =SUM(IF(TEXT($A$4:$A$70,"mm-yyyy")=TEXT($B$73,"mm-yyyy"),$B$4:$B$70))/COUNT(IF((TEXT($A$4:$A$70,"mm-yyyy")=TEXT($B$73,"mm-yyyy"))*($B$4:$B$70<>""),1))

B78: =SUM(IF(TEXT($A$4:$A$70,"mm-yyyy")=TEXT($B$74,"mm-yyyy"),$B$4:$B$70))/COUNT(IF((TEXT($A$4:$A$70,"mm-yyyy")=TEXT($B$74,"mm-yyyy"))*($B$4:$B$70<>""),1))

... confirm both formulas with Ctrl+Shift+Enter

The Sumproduct is a clean way to get the Sumif result, without using an array formula
=SUMPRODUCT((MONTH($A$4:$A$70)=MONTH(B73))*($B$4:$B$70))

I am still working on the Countif part

It should still be able to be done without Ctrl &Shift & Enter

Do you mean something like:

B77: =SUMPRODUCT((TEXT($A$4:$A$70,"mm-yyyy")=TEXT($B$73,"mm-yyyy"))*$B$4:$B$70)/SUMPRODUCT((TEXT($A$4:$A$70,"mm-yyyy")=TEXT($B$73,"mm-yyyy"))*($B$4:$B$70<>""))

... if averaging on the values in column B and not on the number of days in the month.

Yes! Here's mine using months instead of Text

=SUMPRODUCT((MONTH($A$4:$A$70)=MONTH(B73))*($B$4:$B$70))/SUMPRODUCT((MONTH($A$4:$A$70)=MONTH(B73))*($B$4:$B$70<>""))

Did you get
10,500.19

for May?

I did indeed and using MONTH is neater.

EDIT:

... as previously posted (but using MONTH instead):

Just in case averaging over total days in the month:

B77: =SUMPRODUCT((MONTH($A$4:$A$70)=MONTH(B73))*($B$4:$B$70))/SUMPRODUCT((MONTH($A$4:$A$70)=MONTH(B73))*1)