Forum Discussion
jsinghbest
Jan 10, 2024Copper Contributor
Skipping Blank cells in formulas
I have a table of data and I'm trying to take the average of the bottom few non-blank values across a few columns (so, including zeros but not blanks). The values will be capped if they're too high.
I'm using indexing to retrieve the values and if statements to cap the values.
It needs to be at least as interactive as the simplified version I've attached. Any ideas on how to produce this? Thanks.
Actually, how do you get the table that excludes blanks? I see Q5 just says '=LET(', but what does this mean and how does this know to reference Table 1?
If you increase the size of the formula bar you'll see that in Q5 the formula is (I updated it - feel free to re-download the sample I earlier shared):
=LET( n, F5, FilterCol, LAMBDA(col, LET(values, CHOOSECOLS(Table1,col), FILTER(values,ISNUMBER(values),FALSE))), StackCol, LAMBDA(init,col, HSTACK(init, TAKE(FilterCol(col),-n))), Array, IFNA(DROP(REDUCE("",SEQUENCE(COLUMNS(Table1)), StackCol),,1),FALSE), Array )
where you can see the 2 references to Table1
If you want to make it more obvious or for easier maintenance you can use:
=LET( myTable, Table1, n, F5, FilterCol, LAMBDA(col, LET(values, CHOOSECOLS(myTable,col), FILTER(values,ISNUMBER(values),FALSE))), StackCol, LAMBDA(init,col, HSTACK(init, TAKE(FilterCol(col),-n))), Array, IFNA(DROP(REDUCE("",SEQUENCE(COLUMNS(myTable)), StackCol),,1),FALSE), Array )
- PeterBartholomew1Silver Contributor
Another 365 Lambda function.
After all, working within the same coding environment doesn't mean the solutions are identical.
"Worksheet formula" = BYCOL(Table1, AvLastNCappedλ) "AvLastNCappedλ(col)" = LET( values, FILTER(col, NOT(ISBLANK(col))), capped, IF(values > cap, cap, values), AVERAGE(TAKE(capped, -n)) )
- jsinghbestCopper ContributorAhh, thankyou very much, that helps
- LorenzoSilver Contributor
Hi jsinghbest
(Thanks for posting a sample) Haven't looked deeply at your issue yet
What version of Excel do you run?
- jsinghbestCopper Contributor
Thanks for your reply.
I believe it is Excel for Microsoft 365, the full title of what (I assume) is the version name is below:
Microsoft® Excel® for Microsoft 365 MSO (Version 2310 Build 16.0.16924.20054) 64-bit
- LorenzoSilver Contributor
Hi jsinghbest
With 365, could you check that the values highlighted in green in 'Sheet2' are the expected ones against the values I used in Table1 (something isn't clear to me...)?