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 robtcallahan on August 07, 2020
by KPritchard on August 07, 2020
by Zenstone65 on August 06, 2020
by Guy Hunkin on August 05, 2020
by Sonia Atchison on July 24, 2020