CODEWITHSUNDEEP
excelexcel-formula
mmo
mmo

Reputation: 4216

Excel: how to average with condition

We have an Excel sheet in which we collect some monthly data. Each line contains a date and some value (in my boiled-down example here the date is always the first of each month and the values are [1..n]).

I am now trying to calculate an average of the values but not from ALL values but only those from the last quarter (i.e. where Date is >TODAY()-90). TODAY() at the time of this writing is Jan. 30th, 2023, so I would expect the result to be 12, i.e. the average of the values for the last 3 months (11, 12, and 13).

       A           B
 1    Date       Value
 2    01.01.2022   1
 3    01.02.2022   2
 4    01.03.2022   3
 5    01.04.2022   4
 6    01.05.2022   5
 7    01.06.2022   6
 8    01.07.2022   7
 9    01.08.2022   8
10    01.09.2022   9
11    01.10.2022  10
12    01.11.2022  11
13    01.12.2022  12
14    01.01.2023  13
15
16  Avg. last 3 months:   
17                #VALUE!      <-- =AVERAGE(IF(A2:A14>(TODAY()-90);B2:B14;0))
18                #DIV/0!      <-- =AVERAGEIF(A2:A14;">(TODAY()-90)";B2:B14)

I tried several approaches using AVERAGE(...) and AVERAGEIF(...) functions as shown above (and also several other variants) but I could not manage to get this working. All I ever get are error messages like #VALUE! or #DIV/0! :-(

Any idea how I can convince AVERAGE(IF) to only consider those values where the date is >today-90 days?

Upvotes: 0

Views: 86

Answers (1)

JvdV
JvdV

Reputation: 75990

As per my comment:

=AVERAGEIF(A2:A14,">="&TODAY()-90,B2:B14)

Where the ampersand calculations operator is just a less-verbose way to concatenate different values. Here the 2nd parameter takes a string so using both parts on the left and right of the operator we create a value such as ">=12345".

Upvotes: 1

Related Questions