Please help - I need a formula to average the last 3 cell values in a column that are not blank.
Please see attached for an example.
In the example, the cells referenced are B1 through B14, and the answer should be an average of 13, 17 and 24 (which is 18).
Thanks for any help/
You can use:
=AVERAGE(AGGREGATE(14,6,($B$2:$B$30)/($B$2:$B$30<>""),{1,2,3}))
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
a few options
=IF(COUNT($B$2:$B$14)<4,AVERAGE($B$2:$B$14),AVERAGE(MOD(AGGREGATE(14,6,ROW($B$2:$B$14)*10^7/ISNUMBER(B2:B14)+B2:B14,{1;2;3}),10^7)))
Enter in B16 as Array (paste formula then CONTROL+SHIFT+ENTER:
=AVERAGE(INDEX(B2:B14,LARGE(IF(B2:B14<>"",ROW(B2:B14)-ROW(B2)+1),3)):B14)
Hope this helps
Ochimus
Last edited by Ochimus; 12-10-2020 at 11:44 AM.
If they're not in ascending order, which will almost certainly be the case:
=AVERAGE(INDEX(B:B,AGGREGATE(14,6,ROW($B$2:$B$30)/($B$2:$B$30<>""),{1,2,3})))
The option based on Glenn formula
=AVERAGE(INDEX(B:B,AGGREGATE(14,6,ROW(B2:B30)/(B2:B30<>""),3)):B30)
Oh i see the same above but with Large function.
normal formula
array formulaHTML Code:
HTML Code:
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks