CELL("row";arrCodes)+1 returns 4. That is the row number of the upper left cell in range arrCodes =3 (+1).
But the formula says: -CELL("row";arrCodes)+1 which returns -3+1 = -2 (as it should).
Please note that when you highlight CELL("row";arrCodes)+1 and press F9 it is correct that you see -4 but that's not the result of what you have actually highlighted but the result of the (not highlighted minus sign) and the highlighted part which is 4. When you include the minus sign in what you highlight Excel will evaluate -CELL("row"arrCodes) to -3 and finally the +1 which gives the result -2.
Last edited by xor; 08-11-2016 at 10:41 PM.
|