SOLVED

# Skipping Blank cells in formulas

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

16 Replies

# Re: Skipping Blank cells in formulas

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

What version of Excel do you run?

# Re: Skipping Blank cells in formulas

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

# Re: Skipping Blank cells in formulas

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

# Re: Skipping Blank cells in formulas

Hey there,

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

# Re: Skipping Blank cells in formulas

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?

# Re: Skipping Blank cells in formulas

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

Thanks again

# Re: Skipping Blank cells in formulas

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

# Re: Skipping Blank cells in formulas

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

# Re: Skipping Blank cells in formulas

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

# Re: Skipping Blank cells in formulas

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?

# Re: Skipping Blank cells in formulas

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

# Re: Skipping Blank cells in formulas

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

# Re: Skipping Blank cells in formulas

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

Thanks for you help

# Re: Skipping Blank cells in formulas

Ahh, thankyou very much, that helps

# Re: Skipping Blank cells in formulas

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

# Re: Skipping Blank cells in formulas

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

# Re: Skipping Blank cells in formulas

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