SOLVED
Home

IF Function help!

%3CLINGO-SUB%20id%3D%22lingo-sub-498224%22%20slang%3D%22en-US%22%3EIF%20Function%20help!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-498224%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%20I%20am%20trying%20to%20add%20the%20IF%20function%20to%20a%20number%20of%20cells%20horizontally%20for%20a%20set%20of%20data%20vertically%20using%20autofill%3C%2FP%3E%3CP%3ETo%20understand%20this%20better%2C%20ill%20use%20this%20function%20as%20an%20example%3A%20%3DIF(ISBLANK(B22)%2C%200%2C1)%2C%20and%20the%20cell%20im%20typing%20this%20on%20is%20A1.%3C%2FP%3E%3CP%3ESo%2C%20if%20B22%20is%20empty%2C%20itll%20display%20a%20%220%22.%20If%20i%20have%20data%20from%20B22%20to%20B30%2C%20and%20i%20want%20the%20cells%20that%20display%200%20or%201%20from%20A1%20to%20H1%2C%20I%20would%20fill%20in%20the%20function%20one%20by%20one.%20I%20tried%20autofilling%20cell%20A1%20to%20H1%20after%20typing%20the%20function%20in%20cell%20A1%20but%20it%20shows%26nbsp%3B%3DIF(ISBLANK(C22)%2C%200%2C1)%20in%20cell%20B1%20instead%20of%20%3DIF(ISBLANK(B23)%2C%200%2C1)%2C%20and%20so%20on.%26nbsp%3B%3C%2FP%3E%3CP%3ESorry%20if%20my%20explanation%20is%20bad%2C%20i%20am%20still%20trying%20to%20get%20used%20to%20excel.%20Thank%20You!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-498224%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-498534%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%20help!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-498534%22%20slang%3D%22en-US%22%3EYou%20may%20enter%20this%20formula%20in%20A1%2C%20copied%20across%20to%20H1%3A%3CBR%20%2F%3E%3D(INDEX(%24B22%3A%24B30%2CCOLUMN())%26lt%3B%26gt%3B%22%22)*1%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-499063%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%20help!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-499063%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F329508%22%20target%3D%22_blank%22%3E%40JunSheng%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20go%20with%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B's%20suggestion%20I'd%20recommend%20typing%20in%20the%20column%20references%20(see%20yellow%20cells)%20rather%20than%20relying%20on%20the%20COLUMN()%20formula%2C%20because%20if%20someone%20inserts%20a%20column%20A%20then%20you'll%20get%20the%20wrong%20results%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F110928i0B48E203CDF0259E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnother%20option%20is%20to%20use%20the%20Transpose%20Function%20combined%20with%20entering%20it%20as%20an%20array%20(pressing%20Ctrl%2BShift%2BEnter)%20to%20get%20the%20curly%20brackets.%26nbsp%3B%20But%20if%20you%20are%20new%20to%20Excel%20this%20will%20be%20confusing.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI've%20attached%20an%20example%20file%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-499182%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%20help!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-499182%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20so%20wish%20the%20ridiculous%20idea%3C%2FSPAN%3E%20of%20referencing%20by%20location%20had%20never%20been%20invented%3B%20it%20is%20pure%20junk!%3C%2FP%3E%3CP%3EOnce%20one%20gets%20to%20meaningful%20problems%20most%20models%20require%20database%20tables%20or%20functions%20of%20time%2C%20i.e.%20lists%20and%20arrays.%26nbsp%3B%20A%20notation%20that%20encourages%20one%20to%20reference%20individual%20cell%20by%20location%20rather%20than%20business%20objects%20in%20their%20entirety%20fails%20to%20capture%20the%20intent%20of%20any%20solution.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20this%20case%20I%20would%20argue%20that%3C%2FP%3E%3CP%3E%3CSPAN%3E%3D%20TRANSPOSE(%20SIGN(list%26lt%3B%26gt%3B%22%22)%20)%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%3Eis%20far%20clearer%20than%20any%20filled%20formula.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3Ep.s.%20%E2%80%A6%20and%2C%20with%20dynamic%20arrays%2C%20the%20formula%20only%20requires%20one%20to%20use%20a%20single%20cell%20for%20the%20formula!%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-500245%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%20help!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-500245%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F9832%22%20target%3D%22_blank%22%3E%40Wyn%20Hopkins%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-500249%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%20help!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-500249%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F214174%22%20target%3D%22_blank%22%3E%40Peter%20Bartholomew%3C%2FA%3E%26nbsp%3BThank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-500250%22%20slang%3D%22en-US%22%3ERe%3A%20IF%20Function%20help!%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-500250%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3BThank%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
JunSheng
Occasional Contributor

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!

6 Replies
You may enter this formula in A1, copied across to H1:
=(INDEX($B22:$B30,COLUMN())<>"")*1
Solution

@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

 

image.png

 

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

@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!

 

@Wyn Hopkins 

Thank you so much!

@Peter Bartholomew Thank you!

@Twifoo Thank you!

 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies