Forum Discussion
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 completed items, or the spend of the last completed item.
In the end, I have had to create 'Total Spend of Completed Items' column so that it only shows those items that are at 100% and makes those less than this blank. I then used the following formula: =AVERAGE(OFFSET(T67,COUNT(T68:T85,),0,-4))
to look into the 'Total Spend of Completed Items', and calculate the average of the last 4 items ignoring blanks. However, because the cell 3 from bottom within this column is also blank, it means it includes it in the average so where the average should read 989, it reads 885 as the blank is included in the 4 item average. I had tried this formula, but couldn't make it work =AVERAGE(N(OFFSET(T67,LARGE(IF(T68:T85<>"",ROW(T68:T85)-ROW(T68)),ROW(1:4)),0)))
I had also tried to change Average to AverageIF but this wouldn't work along side the OFFSET
Any help would be appreciated.
Kind regards
Charlotte
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))
13 Replies
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.
- 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!
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.
- This formula will work the same 🙂
That's the reason that I like using Excel.
=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)) ".
- Thank you for all your help! It works perfectly
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))- 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 🙂
If you are office365 user, you may write the formula as below.
=AVERAGE(INDEX(FILTER(T68:T85,T68:T85<>""),COUNT(FILTER(T68:T85,T68:T85<>""))+1-SEQUENCE(4)))
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.
