Forum Discussion
IF Function help!
If you go with Twifoo 's suggestion I'd recommend typing in the column references (see yellow cells) rather than relying on the COLUMN() formula, because if someone inserts a column A then you'll get the wrong results
Another option is to use the Transpose Function combined with entering it as an array (pressing Ctrl+Shift+Enter) to get the curly brackets. But if you are new to Excel this will be confusing.
I've attached an example file
If you go with Twifoo 's suggestion I'd recommend typing in the column references (see yellow cells) rather than relying on the COLUMN() formula, because if someone inserts a column A then you'll get the wrong results
Another option is to use the Transpose Function combined with entering it as an array (pressing Ctrl+Shift+Enter) to get the curly brackets. But if you are new to Excel this will be confusing.
I've attached an example file
Thank you so much!
I so wish the ridiculous idea of referencing by location had never been invented; it is pure junk!
Once one gets to meaningful problems most models require database tables or functions of time, i.e. lists and arrays. A notation that encourages one to reference individual cell by location rather than business objects in their entirety fails to capture the intent of any solution.
In this case I would argue that
= TRANSPOSE( SIGN(list<>"") )
is far clearer than any filled formula.
p.s. … and, with dynamic arrays, the formula only requires one to use a single cell for the formula!
PeterBartholomew1 Thank you!
