Average for month

[Muthukrishnan V]

Office 365

Average for Month

Cell G9 contains Jun-2020 in MMM-YYYY
Column A contains dates in DD-MM-YYYY
Column B contains amount in whole number

To find out average for the month, I use the formula in H9:
=Averageifs(B:B,A:A,">="&G9,A:A,"<="&G9,0))
The result is #DIV/! since Jun-2020 onwards are future months.

Kindly advise me formula to show the average as blank for future months

Thanking you

 

[Z51]

Why do you have a 0 as an argument at the end of the AVERAGEIFS function? Also, is G9 the date June 1, 2020 formatted as MMM-YYYY, or is it text?

I'd think you'd be able to use:

=AVERAGEIFS(B:B,A:A,">="&G9,A:A,"<="&DATE(YEAR(G9),MONTH(G9)+1,0))

 

[FormR]

Hi, try like this instead.

=AVERAGEIFS(B:B,A:A,">="&G9,A:A,"<"&EDATE(G9,1))

 

[Muthukrishnan V]

G9 is mmm-yyyy formatted
The formula in post 3 gives the same result #Div/0!

 

[FormR]

Hi, what exactly is in G9? Is it a date with formatting to only display the MMM-YYYY - in which case what exact date is it? Or is it text?

 

[Muthukrishnan V]

G9 is : Format Cells - Category: Custom - Type: mmm-yyyy.
G9 contains: Jun-2020

 

[FormR]

G9 is : Format Cells - Category: Custom - Type: mmm-yyyy.
G9 contains: Jun-2020

What does =DAY(G9) return when placed in an empty cell?

 

[Muthukrishnan V]

=DAY(G9) in an empty cell shows Jan-1900.
The empty cell is : Format Cells - Category: custom - Type: mmm-yyyy
By way of additional information: Input in cell G9 is 01-06-2020. G9 shows: Jun-2020
Thank you Sir

 

[FormR]

By way of additional information: Input in cell G9 is 01-06-2020.

That is the piece of information I was looking for. The suggested formula is sound.

You need to check that your dates in column A are actual dates and not text that looks like dates and that you actually have some dates in June 2020.