Forum Discussion
Solved
Formula that ignores blanks, and creates average based on the last 4 populated cells
Hi, I have this table below where I was trying to find a formula that looks in a column, if the total earned is less than 100%, it ignores it and then either finds the average of the last 4 complete...
It works great and the formula can still be shorten as below.
=AVERAGE(OFFSET(T68,LARGE(IF(T68:T85<>"",ROW(T68:T85)-ROW(T68)),ROW(1:4)),0))
If you are office365 user, you may write the formula as below.
=AVERAGE(INDEX(FILTER(T68:T85,T68:T85<>""),COUNT(FILTER(T68:T85,T68:T85<>""))+1-SEQUENCE(4)))