Forum Discussion

jsinghbest's avatar
jsinghbest
Copper Contributor
Jan 10, 2024

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. 

  • Lorenzo's avatar
    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
    )

     

  • jsinghbest 

    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))
      )
  • Lorenzo's avatar
    Lorenzo
    Silver Contributor

    Hi jsinghbest 

     

    (Thanks for posting a sample) Haven't looked deeply at your issue yet

    What version of Excel do you run?

    • jsinghbest's avatar
      jsinghbest
      Copper Contributor

      Lorenzo 

       

      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

Resources