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)))))
Unfortunately, this formula doesn't work because I have multiple tables in Column T, and the one below I do not have the FILTER function. Thanks for your help though 🙂
- Just copy and paste my formula first in notepad or something. As per your current data, you try to replace T:T with T68:T85 in the formula.
The range can be editted according to your requirement.