IF Function help!

Hello, I am trying to add the IF function to a number of cells horizontally for a set of data vertically using autofill To understand this better, ill use this function as an example: =IF(ISBLANK(B2...

excel

Formulas and Functions

Wyn Hopkins
Apr 29, 2019
JunSheng

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
INDEX reference.xlsx14 KB

PeterBartholomew1

Silver Contributor

Apr 29, 2019

Wyn Hopkins

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!