CODEWITHSUNDEEP
excelexcel-formulavba
Matt Mcdon
Matt Mcdon

Reputation: 305

Minimal value from set if expression

Is it possible to get min value from set, but only if that value is bigger than other value?

Example:

A1 = 2500
A2 = 4800
B6 = 1200

D1 = 2000

I want to get min from A1, A2, B6 but only if that value is bigger than D1, so here result should be 2500. I have tried with min(if()) but to use it I need to have values in continous range.

I think it is possible with VBA, but I would like to do that with formulas.

Upvotes: 1

Views: 71

Answers (2)

ttaaoossuu
ttaaoossuu

Reputation: 7884

If your range were contiguous (which actually makes sense here IMHO), that would look like this:

enter image description here

Upvotes: 0

user4039065
user4039065

Reputation:

There are plenty of examples of pseudo MINIF formulas but your non-contiguous range of cells will demand that at least two ranges be looked at individually with then their results compared. MIN can take many parameters.

=MIN(INDEX((A1:A2)+(A1:A2<D1)*1E+99,,),B6+(B6<D1)*1E+99)

Any value in the disjointed range that is less than the value in D1 has 1E+99 added to it and that won't be the MIN of anything.

         enter image description here

Upvotes: 5

Related Questions