0

I have a significant amount of rows of data showing earnings per week for over 104 weeks with various blank cells.

I am looking for a formula that would average only the latest 52 cells that have earnings in them.

Yisroel Tech
  • 9,687
  • 3
  • 23
  • 37
user1799718
  • 1
  • Hi and welcome to Superuser . It would be best if you can add a sample of your data (especially the ranges your data is in) so the answers can be more tailored to your need – Yisroel Tech May 16 '23 at 18:46

1 Answers1

0

You can use the following formula to get the average of the last 52 non-blank cells in a range (the data is in Column A): =IFERROR(AVERAGE(IF(ROW(A:A)>=LARGE(IF(A:A<>"",ROW(A:A)),MIN(COUNT(A:A),52)),IF(A:A<>"",A:A))),"")

Note: This is an array formula. If you have a current version of Microsoft Office (Microsoft 365), then you can input the formula in the top-left-cell of the output range, then press ENTER (like with any regular formula) to confirm the formula. But If you have older/other versions of Excel, you need to press Ctrl+Shift+Enter to confirm the formula (Excel will insert curly brackets at the beginning and end of the formula for you. Manually inserting those will not make the formula an array formula.)

Yisroel Tech
  • 9,687
  • 3
  • 23
  • 37