Oct 25 2021 10:44 AM - edited Oct 25 2021 10:45 AM
Oct 25 2021 12:33 PM
So much--so VERY much--depends on the exact situation.
Like
Personally, I'd want to think about the implications of each of those questions first. Any other solution would potentially be vulnerable to future changes that make the resulting totals unreliable. A good design tries to reduce the risk of unintended side effects. Hidden columns (which admittedly can be useful) are potential time bombs depending on who uses the resulting spreadsheet, how much they're allowed to do, etc.
Oct 25 2021 02:13 PM
I am also not a fan of hiding cells as a way of conveying information. However
visible
= SIGN(INDEX(CELL("width",(@data)),1))
= SUM(IF(visible,data))
might work for you.
Of course, I might use
= SUM(IF(MAP(data, Visibleλ), data))
where Visibleλ is given by
=LAMBDA(c,SIGN(INDEX(CELL("width",c),1)))
but that is just showing off how different modern Excel can be.
Jul 19 2022 11:31 PM - edited Jul 19 2022 11:38 PM
Hi,
I prefer writing it this way
N(INDEX(CELL("width",(data)),2))) and avoid the sign function
I never have used Lambda so Ill need an actual example to understand but ill try
Is there no other function or conditional formatting which can be used to achieve a value when visible and 0 ( or diff value or text) when hidden
Love to hear from you
Jul 20 2022 12:58 AM
If you don't have too many columns and you don't plan to add any new ones, you can always do a simple SUM(range1,range2,range3,...,rangeN).
Jul 20 2022 01:28 AM
Given N(width) = width, perhaps you mean N(width>0) = SIGN(width) ?
For a non-Lambda option maybe try:
=SUM(IF(CELL("width",OFFSET(data,,SEQUENCE(,COLUMNS(data),0))),data))
Jul 20 2022 05:28 AM - edited Jul 20 2022 05:30 AM
Given N(width) = width, perhaps you mean N(width>0) = SIGN(width) ?
In short no but that works too
What I actually was trying to do is
when I try only CELL("width",data) in excel 21 then I get SPILL errors and the formula doesn't work because it evaluates to an array say {8,FALSE}
Therefore the index function becomes necessary
in Excel 21 INDEX(CELL("width",(data)),2)) gives me TRUE or FALSE as applicable depending on the width of the column
If the column is hidden then it evaluates to FALSE
N Function then converts it to 1 or 0
Instead as @Peter Bartholomew suggests we can use
INDEX(CELL("width",(@data)),1) instead which would give the width of the column which is converted to 1 or 0 using the SIGN function
Thanks for the non lambda suggestion!
Jul 20 2022 06:52 AM
According to MS Help on CELL("width") :
"Returns an array with 2 items.
The 1st item in the array is the column width of the cell, rounded off to an integer. Each unit of column width is equal to the width of one character in the default font size.
The 2nd item in the array is a Boolean value, the value is TRUE if the column width is the default or FALSE if the width has been explicitly set by the user.
Note: This value is not supported in Excel for the web, Excel Mobile, and Excel Starter."
This suggests the 2nd item is not a reliable check for column visibility though may sometimes coincide. The 1st item isn't totally reliable either as very small column widths can get rounded to zero. For windows versions the only non-vba way to get the exact width seems to be:
ColumnWidth=LAMBDA(range,GET.CELL(16,range))
Jul 20 2022 01:17 PM
@lori_m Thanks for picking this up; I have been tied up with other things today. A good thing as well, because I misinterpreted the question. I assumed an alternative was required for 'CELL'; the idea of trying to remove LAMBDA, at a time when everything I do is aimed at exploiting Lambda, failed to register correctly!
Jul 20 2022 02:15 PM
Apologies for jumping in, and i don't think I fully addressed the question either! My response was mainly prompted by the proposed N() formulation which appeared to have some shortcomings.
As for MAP versus native array processing... that's entirely personal preference, I guess I tend to be influenced by NumPy style in this respect.
Jul 24 2022 04:40 AM