CODEWITHSUNDEEP
excelnumbersreturntraversal
Philip McQuitty
Philip McQuitty

Reputation: 1077

Return smallest two numbers with negatives in Excel

If I have an array of numbers in Excel like below:

-5    1    4    -2

I need to return -5 and -2 (each in a separate cell).

The =SMALL(array, 1) does not work because the numbers in my array are less than 1. Is there a workaround so I can traverse trough the array and return the two smallest numbers?

Upvotes: 0

Views: 176

Answers (2)

Subodh Tiwari sktneer
Subodh Tiwari sktneer

Reputation: 9976

Assuming your numbers are in the range A2:A5 and

If you want the formula to return the output in rows, place this formula in any cell and copy it down.

=SMALL($A$2:$A$5,ROWS(A$1:A1))

If you want the formula to return the output across the columns, place this formula in any cell and copy it across or right.

=SMALL($A$2:$A$5,COLUMNS($A1:A1))

Change the ranges as required.

Upvotes: 1

Jeremy
Jeremy

Reputation: 1337

To get the smallest =MIN(-5,1,4,-2), to get the second smallest =SMALL({-5,1,4,-2},2)

Upvotes: 1

Related Questions