04-29-2019 01:53 AM
04-29-2019 01:53 AM
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!
04-29-2019 05:21 AMSolution
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
04-29-2019 05:55 AM
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!
by Ingeborg Hawighorst on May 13, 2020
by cuong on April 22, 2020
by cuong on April 15, 2020
Posted in Microsoft Ignite The Tour 2019 on February 14, 2020