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))
Formula is quite long.
=CHOOSE(SUM(--(INDIRECT("T"&MAX((T:T<>"")*ROW(T:T))-3&":T"&MAX((T:T<>"")*ROW(T:T)))<>"")),AVERAGE(INDIRECT("T"&MAX((T:T<>"")*ROW(T:T))-6&":T"&MAX((T:T<>"")*ROW(T:T)))),AVERAGE(INDIRECT("T"&MAX((T:T<>"")*ROW(T:T))-5&":T"&MAX((T:T<>"")*ROW(T:T)))),AVERAGE(INDIRECT("T"&MAX((T:T<>"")*ROW(T:T))-4&":T"&MAX((T:T<>"")*ROW(T:T)))),AVERAGE(INDIRECT("T"&MAX((T:T<>"")*ROW(T:T))-3&":T"&MAX((T:T<>"")*ROW(T:T)))))