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.

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.