Forum Discussion

clh_1496's avatar
clh_1496
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's avatar
    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))

Starrysky1988's avatar
Starrysky1988
Iron Contributor
Mar 30, 2022
This formula will work the same 🙂
That's the reason that I like using Excel.

Resources