Find min or max value in between two numbers

[swemek]

Hi all!

I have a column A1:A21 with both negative and positive values.
In it I want to find the minimum or maximum value based on values in B1min and B2max. And the results/formula in B3

So if I enter -7,86 in B1 and 2,82 in B2, excel should find -6,92 if min, or 1,84 if max.
I assume the formula is the same for min and max value.


Any help is appreciated!

//M
Example A1:A21

 

[Fluff]

How about

=MIN(FILTER(A1:A21,(A1:A21>B1)*(A1:A21<B2)))

 

[Joe4]

Formula for MIN value in that range:

=MINIFS(A1:A21,A1:A21,">"&B1)

Formula for MAX value in that range:

=MAXIFS(A1:A21,A1:A21,"<"&B2)

 

[swemek]

Works great! (both suggestions)

Thanks so much for your help!

 

[Fluff]

Glad we could help & thanks for the feedback.