• 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

D

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.
 
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),
 
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.
 
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.
 
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.
 
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.
 
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

  • FORMULA CHECK.xlsx
    157.9 KB · Views: 4
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

  • FORMULA CHECK.xlsx
    160 KB · Views: 6
Peter, thank you for your insight, appreciated, I'm now able to conclude the update to the sheet.
 
I should be grateful for one final adaptation to the LET formula below:

= LET(
arr, FILTER($W$18:$W$236,$W$18:$W$236 >0),
n, COUNT(arr),
k, SEQUENCE(1,10,n,-1),
d, INDEX(arr, k),
AVERAGE(d)
)
First, I need to incorporate an additional filter into the formula to incorporate column AE, cells AE18:AE236. These cells will be entered with a letter, Y or W or R at the same time as the corresponding cell in column W is filled. The average is calculated for all cells in in column W that have, for example, a Y in the corresponding cell in column AE. The formula will be amended to suit.
Col. W Col. AE
90 Y
91 Y
85 R
Second, I need the revised formula to return zero if there are less than 10 entries that meet the criteria. In the above it is 10 but this is adjusted for other calculations.

Last, I grapple with LET & am not an EXCEL expert so I'm grateful for any help on this matter.
 
Code: 
= LET(
  criterion, ($W$18:$W$236>0)*($AE$18:$AE$236="Y"),
  arr, FILTER($W$18:$W$236, criterion),
  n, COUNT(arr),
  k, SEQUENCE(1,10,n,-1),
  d, INDEX(arr, k),
  IF(n>10, AVERAGE(d), 0)
)
What LET does is use an alternating pattern of parameters to assign fragments of the formula (even parameters) to a variable (the preceding odd parameter). This allows subsequent formulas to use the variable rather than creating a deeply nested formula. I separate the pairs of parameters by inserting Alt/Enter (line feed) after the formula fragments, to make the overall formula more readable. The final parameter contains the formula or previously-calculated variable to be returned as the result.

The variable names can be chosen to make the overall formula reasonably concise or longer names may be used to make it clear what each step of the calculation represents. This last idea would be alien to a normal spreadsheet user who prefers to to annotate helper cells (or, worse still, leave the whole lot undocumented).
 
You must log in or register to reply here.
Back
Top