CODEWITHSUNDEEP
excel
Vess
Vess

Reputation: 117

Excel: Find the minimal value in a column

An Excel table consists of two columns (e.g., A1:B5):

0 10
1 20
3 30
2 20
1 59

I need to get the minimal value in column B for which the corresponding value in column A is greater than zero. In the above example it should be 20.

I tried using various combinations of INDEX(), MIN(), IF(), ROW(), array formulas, etc. - but I just can't figure out how to do it. :-( Any help would be appreciated.

Upvotes: 0

Views: 9132

Answers (5)

guitarthrower
guitarthrower

Reputation: 5844

Grsm almost had it

if you enter the following formula in C1 as an array (Ctrl+Shift+End)

=MIN(IF(A1:A5>0,B1:B5))

That should do the trick.

Upvotes: 3

Fionnuala
Fionnuala

Reputation: 91376

This is not identical, but very similar: Excel VBA - Find minimum of list of values?

Upvotes: 0

reva
reva

Reputation: 1477

Check the "Minimum And Maximum Values In A Range" example in http://www.cpearson.com/Excel/excelF.htm (you can download the same as well from the same section)

HTH

Upvotes: 0

Shiraz Bhaiji
Shiraz Bhaiji

Reputation: 65491

You need to do it in 2 stages

  • First use the MIN function to find the minimum
  • Then take that answer and use the LOOKUP function to select the row and column that you need.

Upvotes: 0

Gersom
Gersom

Reputation: 671

I think you have to make an extra column..

A     B     C     D
0     10    false 20
1     20    20
3     30    30
2     40    40
1     50    50

column C : =IF(A1>0;B1)

cell D1: =MIN(C1:C5)

Upvotes: 1

Related Questions