Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Skipping Blank cells in formulas

Copper Contributor

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. 

16 Replies

Hi @jsinghbest 

 

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

What version of Excel do you run?

@L z. 

 

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

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

Hey there,

After taking out a few misplaced '0's, yes that looks right! Please may you explain how you solved the problem?
Thanks

Hi @jsinghbest 

Please may you explain how you solved the problem?

It's not that I don't want but this sounds unrealistic if you're not yet familiar with LAMBDA and its helper functions

 

In a nutshell:

- in Q5 we generate an array of 3 columns (= #columns of Table1) where each column consists of the last N (in F5) values of the corresponding column in Table1, excluding blank/empty cells

The above array to stored somewhere in the workbook as it's going to be used for the various Averages (this avoids calculating the same array n times)

 

- in M7 we just do an average of the above array

- in M8:O8 we iterate the columns of the above array and do an Avg of each column values

- in M9 we do the same Avg as in M7 after capping the values according to L (in G5)

 

Makes sense? Helps?

Right ok, that makes sense, I'll try and learn more about these functions and apply them more.

Thanks again
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?
best response confirmed by jsinghbest (Copper Contributor)
Solution

@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
)

 

Ahh I see, that makes sense now, thanks for your help!
Sorry, another question!

How would I go about modifying the formula to use, for example, data for the 7 days before the most recent 7?

Or even 'yesterday', 'last week', 'last month' sort of functions?

@jsinghbest 

 

So far we've worked with numbers only, now you want to intro. dates/periods ==> The formula might be significantly different (+ I don't have too much time these days). So let me suggest the following approach:

  • Mark the current solution (link at the bottom of each reply you get here) - Helps people who Search
  • Create a new thread/discussion with a representative sample of your data (with Dates) and as for this one provide a few expected results

Hope this all makes sense. Thanks

@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))
  )

@L z. 

 

Hey there, apologies for the late reply, that sounds good I was just wondering about it. 

 

Thanks for you help

Ahh, thankyou very much, that helps

@jsinghbest 

 

No worries at all and glad you have something that suits your need

Whatever option you picked it would help people who Search if you click on Mark as response at the bottom of the corresponding post - Thanks

Ahh yes, will do. I'll also have a play around with them to see how they do
1 best response

Accepted Solutions
best response confirmed by jsinghbest (Copper Contributor)
Solution

@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
)

 

View solution in original post