Average Last 5 Numbers in Column, Excluding "#N/A"

[Excel Pooh]

Good morning, I am stumped on this as I can only seem to get one half or the other.

I have a column (F) filled with INDEX/MATCH formulas that return simple numbers. The range is from F3-F20, however, as these numbers are representative of a period of time, they are not always populated with numbers, some still have "#N/A" as we don't know the numerical answer yet.

I am trying to get the average of the last 5 REAL numbers in this selection (F3:F20), but what cells contain the last 5 will be ever-changing as the sheet updates with time. Right now, I have real numbers in F3:F9, and "#N/A" in F10:F20. I would like the average to auto-update to only show the average of the last 5 numbers, even as F10:F20 are populated over time, but until then, they need to be ignored..

Please let me know if I can explain better, I appreciate the help, this has really sent me through the ringer.

 

[BigBeachBananas]

How about this?

=AVERAGE(CHOOSEROWS(FILTER(F3:F20,NOT(ISNA(F3:F20))),SEQUENCE(5,,-5,1)))

 

[kevin9999]

Also:

=AVERAGE(TAKE(FILTER(F3:F20,NOT(ISNA(F3:F20))),-5))

 

[Georgiboy]

Could still be slightly shorter if you have th TOCOL function:

=AVERAGE(TAKE(TOCOL(F3:F20,3),-5))

 

[Excel Pooh]

Thanks everybody, I really appreciate you taking the time to help me. I was stuck in between trying AGGREGATE, not knowing where to fit in the ISNA, and a whole lot of other unsuccessful messy attempts. Have a great Thursday!

 

[kevin9999]

Thanks everybody, I really appreciate you taking the time to help me. I was stuck in between trying AGGREGATE, not knowing where to fit in the ISNA, and a whole lot of other unsuccessful messy attempts. Have a great Thursday!

Glad we could help, and thanks for the feedback