0

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?

CallumDA
  • 12,025
  • 6
  • 30
  • 52
Philip McQuitty
  • 1,077
  • 9
  • 25
  • 36
  • `=SMALL({-5,1,4,-2},ROW(1:1))` filled down another row doesn't work? –  Apr 07 '17 at 16:00
  • Maybe I'm misunderstanding the question, but `=SMALL()` works just fine for me for your use case as I understand it. Negative values are acceptable inputs to that function. – Marc Apr 07 '17 at 16:18
  • `=SMALL(A1:D1,1)` and `=SMALL(A1:D1,2)` works perfect for me – Gowtham Shiva Apr 07 '17 at 16:20

2 Answers2

1

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

Jeremy
  • 1,337
  • 3
  • 12
  • 26
1

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.

Subodh Tiwari sktneer
  • 9,906
  • 2
  • 18
  • 22