• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Let Formula

DIGBY

New Member
The following formula was a result of help via this forum in February, [I have no experience of the LET process], & it works perfectly.
Code:
= LET(
     arr, FILTER($BG$15:$BG$234,$BG$15:$BG$234 >0),
     n, COUNT(arr),
     k, SEQUENCE(1,20,n,-1),
     d, INDEX(arr, k),
     s, SMALL(d,{1,2,3,4,5,6,7,8}),
     AVERAGE(s)
   )
I need to modify the above so that it provides the average of the last 20 entries irrespective of the value, e.g. not the average of the 8 smallest from the last 20.
 

Peter Bartholomew

Well-Known Member
All you require is to average 'd' rather than 's'
Code:
= LET(
arr, FILTER($BG$15:$BG$234,$BG$15:$BG$234 >0),
n, COUNT(arr),
k, SEQUENCE(1,20,n,-1),
d, INDEX(arr, k),
AVERAGE(d)
)
Do you still need to remove negative numbers and blanks before selecting the last 20 values for averaging. If you need help with understanding the formula by all means feel free to ask. It is not sacred and may be adjusted to meet your wishes. For example, the final 20 values 'd' are listed horizontally, starting with the final value. If you wish to switch them to a vertical list, starting with the 201st, then only a trivial change is needed.
k, SEQUENCE(20,1,n-19),
 

DIGBY

New Member
Peter, thank you, the solution is perfect & I am able to adjust the number of values averaged by amending the number [20] in 'k'. The solution works with the unpopulated cells having a '-' & also if a cell is missed although that is not envisaged. I'm grateful.
 

Peter Bartholomew

Well-Known Member
That is good. It is just that I did not want you to treat the formula as if it were written in tablets of stone brought down from the mountain! The parts are simply meant as suggestions that you might incorporate into your work as you see fit.
 
Top