Mar 30 2022 06:34 AM
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
Mar 30 2022 07:19 AM - edited Mar 30 2022 08:07 AM
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)))))
Mar 30 2022 07:30 AM
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)))
Mar 30 2022 07:56 AM
Mar 30 2022 08:02 AM
Mar 30 2022 08:12 AM
=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)) ".
Mar 30 2022 08:30 AM - edited Mar 30 2022 08:32 AM
SolutionIt 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))
Mar 30 2022 08:52 AM
Mar 30 2022 08:57 AM
Mar 30 2022 09:22 AM
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.
Mar 30 2022 11:29 AM - edited Mar 30 2022 11:30 AM
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.
Mar 31 2022 01:28 AM
Mar 31 2022 01:29 AM
Mar 31 2022 01:31 AM
Mar 30 2022 08:30 AM - edited Mar 30 2022 08:32 AM
SolutionIt 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))