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

Date Table Simplified.xlsx11 KB

excel

Formulas and Functions

Lorenzo

Jan 11, 2024

jsinghbest

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
)

Forum Discussion

Skipping Blank cells in formulas

Resources