Average If using Dates

[mawilson12]

I have a formula =AVERAGEIFS(J:J,H:H,">=3/1/2018") - where H is a date and J is number, so I am taking an average of everything that is greater than a date.

What I WOULD LIKE TO DO is substitute the date with cell location, like in another part of the spreadsheet, so that I can list every month from 2004 to 2017 and get a running average.

Something like "=AVERAGEIFS(J:J,H:H,">='K2")
and
"=AVERAGEIFS(J:J,H:H,">='K3") and so on.

Is this possible?

 

[joeu2004]

Use:

=AVERAGEIFS($J$1:$J$10000, $H$1:$H$10000, ">=" & K2)

Generally, it is better to use a restricted range like J1:J10000 instead of J:J. J:J refers to a column of 1M+ cells. It is unclear if Excel is clever enough to ignore cells below the last-used row.

----

PS.... ">=" & DATE(2018,3,1) is better than ">=3/1/2018" because 3/1/2018 can be interpreted as Jan 3 in some regions, instead of Mar 1. In fact, you might have intended DATE(2018,1,3).

 

[mawilson12]

I think I figured it out.


I concatenated the cell

=AVERAGEIFS(J:J,H:H,T5)

Where T5 is =CONCATENATE(S5,P5,R5)

 

[mawilson12]

I like your solution better than mine, thanks.