Forum Discussion
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 )
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 yes, will do. I'll also have a play around with them to see how they do
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
Hey there, apologies for the late reply, that sounds good I was just wondering about it.
Thanks for you help
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
- 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? - Ahh I see, that makes sense now, thanks for your help!