Sorting Postive and Negative numbers in order

[pritesh118]

Hi All,

I am currently struggling with trying to lookup a set of numbers, which are variances both positive and negative in order of absolute amount, but retaining the negative sign when shown into the top 5 amounts. I have pasted the numbers below.

My current formula I have is =LARGE(B2:B19,1), then followed by 2, 3, 4, and 5 in the subsequent cells. However this only brings back the top 5 largest positive values onle and ignore the high negative value of (150).

When i try to add an ABS formula in, using an array, it retrieves the 150 but shows it as a posivtive number and not negative, which is how i need to show it.

Any help would be much appreciated.

Have attached my spreadsheet with my formulas.

Top Variance Lookup.xlsx

Thanks.

42
(17)
82
271
19
187
12
183
(35)
(32)
(9)
3
0
3
(2)
12
127
(150)

[NeedForExcel]

You want to sort the data irrespective of + or - sign?

[zbor]

Try this in H3:

Please Login or Register  to view this content.

comfirmed with ctrl+shift+enter

[NeedForExcel]

Here!

Please find the file attached.

Deep

[pritesh118]

Super that works perfectly for me zbor.
Thanks

[Simon Livings]

Hello

You could try the following formula instead:

Please Login or Register  to view this content.

Thus the ABS test is performed up front, but then the actual value is brought through using the SMALL function instead of the LARGE function.

Kind regards,
Simon

[Simon Livings]

Apologies - thought I had a nice solution, but of course mine will only ever compare the first, second, third (etc) smallest / largest items against each other. The Array formula mentioned by ZBOR is more complete and appropriate for you.

[NeedForExcel]

Agreed!

The Formula given by ZBOR is perfect..