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

PeterBartholomew1

Silver Contributor

Mar 30, 2022

clh_1496

Some further ideas, firstly what I would use based upon the latest version of Excel

= LET(
    nonBlanks, FILTER(spend, spend<>""),
    lastFour,  TAKE(nonBlanks,-4),
    AVERAGE(lastFour)
  )

and then, using obsolete versions of Excel,

= AVERAGE(
    INDEX(spend, LARGE(sn*(spend<>""),4)) 
    : INDEX(spend, MAX(sn))
  )

where the serial number 'sn' is a helper range.

OK, I guess 'obsolete' was a bit harsh, but Excel has changed, and is still changing, so much that the best approaches offered by each are not even recognisably similar.

clh_1496
Brass Contributor
Mar 31, 2022
Thank you!, Unfortunately my work uses said obsolete excel which means it doesn't recognise FILTER - I agree thought that life would be a lot easier with the latest version of Excel!
Starrysky1988
Iron Contributor
Mar 30, 2022
That's the power of Excel.
Many functions can give the same result.
=Indirect("A1:A"&3),
=A1:Index(A1:A3,3) and
=A1:Offset(A1,3-1,) are the same.