Need formula for finding the additive inverse of a given number

[bansche123]

Hi lovely Forum Users,

I need help creating a formula that would look for the additive inverse of a given number.

So, in columns G to K, there are 7 lines of sets of 5 numbers. In the first line (G3 to K3) there is the no 7 (H3) and its additive inverse -7 (I3). I need the formula to identify them and then to translate the positive number to the value 1 and the negative number to the value -1. Those values shall be visible in the second table (columns M to Q). The place of the values in this column should correspond to the place the positive and negative numbers where in the first table (columns G to K). All the other numbers should be translated into the zeros.

I prepared and attached a xls file to illustrate what I am talking about.

[Pete_UK]

Your attachment is not valid - see the yellow banner at the top of the screen.

Pete

EDIT: It is now - you must have changed it.

[Phuocam]

Try this in cell M3:

=IF(COUNTIF($G3:$K3,-G3),SIGN(G3),0)

[Pete_UK]

An alternative in M3:

=IF(G3=0,0,IF(COUNTIF($G3:$K3,-G3),G3/ABS(G3),0))

Copy across and down as required.

Hope this helps.

Pete