Forum Discussion

Brass Contributor

Mar 30, 2022

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...

excel

Formulas and Functions

Starrysky1988
Mar 30, 2022
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))

OliverScheurich

Gold Contributor

Mar 30, 2022

clh_1496

=AVERAGE(N(OFFSET(T67,LARGE(IF(T68:T85<>"",ROW(T68:T85)-ROW(T68)),ROW(1:4))+1,0)))

You can try this formula which seems to work in my sheet. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021. I only made a small change: " ROW(1:4))+1 " instead of " ROW(1:4)) ".

average.pdf125 KB

Starrysky1988

Iron Contributor

Mar 30, 2022

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))

clh_1496
Brass Contributor
Mar 31, 2022
Thank you for all your help! I wouldn't have been able to work this out without your help and it does exactly what I want it to do 🙂