Forum Discussion
Calculate a sum excluding hidden columns, how?
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 PeterBartholomew1 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!
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))
- Thanks Lori !
The drawbacks of using CELL function for finding width weren't obvious but we're nicely highlighted by you 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!
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.
