• 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.
 

DIGBY

New Member
As per my reply of the 23/04 the solution is perfect with my laptop. However I have an issue with another computer that is using the same Excel worksheet but has a different version of Excel. When the worksheet is installed on my laptop, no problem but immediately data is added when installed on the alternative the LET formula is corrupted thus:

= _xlfn.LET(
_xlpm.arr, FILTER($BG$18:$BG$237,$BG$18:$BG$237 >0),
_xlpm.n, COUNT(_xlpm.arr),
_xlpm.k, SEQUENCE(1,20,_xlpm.n,-1),
_xlpm.d, INDEX(_xlpm.arr, _xlpm.k),
_xlpm.s, SMALL(_xlpm.d,{1,2,3,4,5,6,7,8}),
AVERAGE(_xlpm.s)
)

The Excel version on my laptop is Microsoft® Excel® for Microsoft 365 MSO (16.0.13929.20360) 64-bit

On the other computer it is Microsoft 365 MSO (16.0.12527.21912) 32bit

I understand that the error is because there is a compatibility issue but is there a way to solve the issue by modification of the formula or via an alternative formula?

Thanks.
 

Peter Bartholomew

Well-Known Member
It looks like an issue that should sort itself the next time the other computer updates. I assume it is a company machine set to update on a semi-annual basis. The process for removing a LET formula is to take the result formula and progressively eliminate the local names, replacing them by the formulas they refer to, until you are left with a single nested formula. It is not pretty but the resulting formula should work.
 

DIGBY

New Member
Peter,

Again thanks for help with LET. I have a new issue that was not envisaged at the time of my original question.

The attached test sheet displays the problem that I need help to solve. I think that the LET formula in R5 requires modification to add a rounding factor so that calculated value is set to one decimal place? [Changing the value in any of W9 to W28 changes the value in R5]. The LET formula works OK as originally designed. If I'm correct, then I don't know how to accomplish that modification?

The value in Y5 is linked to an XLOOKUP when the IF(AND…. element of the formula in P5 is active & as the attached shows the value in Y5 as 0.0 when it should be 0.1. This is due to, I think, the rounding in R5 (20.4899...minus 17.3 = 3.186... whereas 20.5 minus 17.3 = 3.2). I have tried the solution of changing the match function of XLOOKUP but that did not work, all it achieved was that other values caused an error.

Thanks, appreciated.
 

Attachments

Peter Bartholomew

Well-Known Member
I do not think any rounding is needed. The 0 returned by the XLOOKUP appears to be the result of a successful approximate lookup, not an error trap. I am not very good at reading formulas with direct cell references so I have used labels to generate names where possible. I haven't found any obvious errors beyond the redundant use of SUM in expressions such as
=SUM($U$5+3)
for which I substituted
=constant+3
 

Attachments

DIGBY

New Member
Peter, thank you for your insight, appreciated, I'm now able to conclude the update to the sheet.
 
Top