Find value NOT equal to 0 (Excel 2002/SP2)

Topic

New Reply

I want to determine in which cell in an array (consisting of 1 row) the first cell from the left is NOT equal to 0 (zero).
I have found several formulas to find a value ‘equal to’ but not the opposite of this.

Anyone any ideas?

Reply | Quote

Replies

If your data is on row 2:

=ADDRESS(ROW(A2:I2),MIN(IF((A2:I2″”)*(A2:I2=0),COLUMN(A2:I2),999)))

(array formula, confirm with control-shift enter!!!)

Reply | Quote

=INDEX(A1:G1,MATCH(1,INDEX(ISNUMBER(A1:G1)*(A1:G10),1,0),0))

gives the first non-zero number from A1:G1.

=CELL(“address”,INDEX(A1:G1,MATCH(1,INDEX(ISNUMBER(A1:G1)*(A1:G10),1,0),0)))

gives the address of the first non-zero number from A1:G1.

Aladin

Reply | Quote

Thank you for your quick reply! Works perfect.

Reply | Quote