Average data from range if Year = Current year -1

[nmgmarques]

Hi all.

Imagine range A1:A1000 with varying dates from 2020 to 2022 (short form 16-08-2021).
Range B1:B1000 has number value for the corresponding date.

In cell C1, show average of all values from current year (today()) -1. So in this case, effectively, return the average of all values from 2021 only.

I have tried an AverageIF and AverageIFS to no avail. Suspect it has something to do with the year.
=AVERAGEIF(A2:A1000;YEAR(TODAY())-1;B2:B1000)

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[nmgmarques]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thanks. I updated it. I'm using Office 365. Excel is currently version 2022 build 14931.20604

 

[Fluff]

Thanks for that, how about

=AVERAGE(FILTER(B2:B1000,(A2:A1000>=DATE(YEAR(TODAY()),1,1))*(A2:A1000<TODAY())))

 

[nmgmarques]

Thanks for that, how about

=AVERAGE(FILTER(B2:B1000,(A2:A1000>=DATE(YEAR(TODAY()),1,1))*(A2:A1000<TODAY())))

The formula seems to work, but I am getting discrepancies. The file is available here: Dashboard.xlsx
Your formulas were used in row 2 of worksheet Averages.

If I select all the 2021 data from worksheet Inputs (say we select all Purchase Orders QTY in column B), the resulting average at the bottom right corner in Excel Task Bar is different from the result given by the formula. What am I missing?

Edit: unsure if the previous link is working, so here is an alternate: Dashboard.xlsx

 

[Fluff]

Misunderstood what you wanted, try

=AVERAGE(FILTER(Inputs!B3:B1000,YEAR(Inputs!$A3:$A1000)=YEAR(TODAY())-1))

And please do not use whole column references unless you want your workbook to be unusable.

 

[nmgmarques]

Misunderstood what you wanted, try

=AVERAGE(FILTER(Inputs!B3:B1000,YEAR(Inputs!$A3:$A1000)=YEAR(TODAY())-1))

And please do not use whole column references unless you want your workbook to be unusable.

That's it! That did it. Thanks a ton. Marking as solution.
Just out of curiosity, you mentioned instability if I use whole column references. How can I sub these references for one that says "start at row2 and go down till the last value"?

 

[Fluff]

you mentioned instability

Not instability, but unusable. Before I changed the formulae your workbook was very slow to do anything because of the whole column references.

You can go below the last used range without a problem, but limit it to something sensible.