Excel - Formula reference to cell on the same row

0
votes

I'm trying to build a simple formula: if the cell on the same row as current cell, but column J is either =1 or empty, then the result is 1, else 0.

The part about =1 works, the part about="" does not for some reason.

Here is my formula:

    =IF(OR("J"&ROW()=1,"J"&ROW()=""),1,0)

Can anyone help me find out why "J"&ROW()="" returns false, even if it is clearly true? The "J"&ROW()=1 returns true if the target cell is 1.

Another thing i tested is "J"&ROW()=j50, where 50 is the actual row number, and this also returned false, which does not make any sense to me.

2
excelexcel-formulaformula

2 Answers

2
votes

You need either INDIRECT to turn the string into a cell reference

=IF(OR(INDIRECT("J"&ROW())=1,INDIRECT("J"&ROW())=""),1,0)

or use INDEX (as INDIRECT is volatile)

=IF(OR(INDEX(J:J,ROW())=1,INDEX(J:J,ROW())=""),1,0)
1
votes

In this specific case it makes sense to make use of RC notation. A formula would look like this:

=IF(OR(RC10=1;RC10="");1;0)