Forum Discussion
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(B22), 0,1), and the cell im typing this on is A1.
So, if B22 is empty, itll display a "0". If i have data from B22 to B30, and i want the cells that display 0 or 1 from A1 to H1, I would fill in the function one by one. I tried autofilling cell A1 to H1 after typing the function in cell A1 but it shows =IF(ISBLANK(C22), 0,1) in cell B1 instead of =IF(ISBLANK(B23), 0,1), and so on.
Sorry if my explanation is bad, i am still trying to get used to excel. Thank You!
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
6 Replies
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!
- You may enter this formula in A1, copied across to H1:
=(INDEX($B22:$B30,COLUMN())<>"")*1
