View Single Post
 
Old 08-11-2016, 05:06 AM
xor xor is offline Windows 10 Office 2016
Expert
 
Join Date: Oct 2015
Posts: 1,100
xor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to allxor is a name known to all
Default

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.
Reply With Quote