Average data from range if Year = Current year -1

nmgmarques

Board Regular
Joined
Mar 1, 2011
Messages
133
Office Version
  1. 365
Platform
  1. Windows
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)
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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’)
 
Upvote 0
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
 
Upvote 0
Thanks for that, how about
Excel Formula:
=AVERAGE(FILTER(B2:B1000,(A2:A1000>=DATE(YEAR(TODAY()),1,1))*(A2:A1000<TODAY())))
 
Upvote 0
Thanks for that, how about
Excel Formula:
=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
 
Last edited:
Upvote 0
Misunderstood what you wanted, try
Excel Formula:
=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.
 
Upvote 0
Solution
Misunderstood what you wanted, try
Excel Formula:
=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"?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top