Forum Discussion
Solved
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. ...
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 )
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))
)
Ahh, thankyou very much, that helps