Forum Discussion
Calculate a sum excluding hidden columns, how?
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
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))
Given N(width) = width, perhaps you mean N(width>0) = SIGN(width) ?
In short no but that works tooWhat 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