Excel: Obtain the row of the smaller value

Question

I have the following columns:
A - Original values, B - Absolute values, C - sorted absolute values

(I obtain the ordered values (C) using the SMALL formula for the given range - see the link bellow)

I need to know for each ordered absolute value, if the original value was or not negative:

So in the picture the red columns I filled-in manually...
Is there a way to automatize that via a formula ?

Here is the link to the sandbox in Excel Online: http://1drv.ms/1Vu2MZ4
If the first link does not work, the same thing and formulas on Google Sheets

Answer 1

In your case and for your locale =MATCH(C2; $B$2:$B$6; 0) and = INDEX($A$2:$A$6; MATCH(C2; $B$2:$B$6; 0)) < 0 will do but beware of cases where you have the same value both negative and positive.

Answer 2

A partial answer, which might be enough if column E in your screenshot is what you want and column D was a helper column intended to make column E easy to compute.

The problem with ABS() is that it loses information. Instead of using that, use a function which doesn't lose information but also doesn't change the sort order and which makes it possible to recover the absolute value after sorting. One way is to leave positive numbers alone but send negative numbers to their absolute value + 0.5. For example,

In B2 enter

=IF(A2 >= 0,A2, 0.5+ABS(A2)) 

In C2 enter

=SMALL($B$2:$B$6,-1+ROW())

In D2 enter

=INT(C2)

To recover the absolute values. Finally, in E2 enter

=IF(C2=D2,FALSE,TRUE)

(copying all of the formulas down).