CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
atame
atame

Reputation: 521

Excel formula for min value if greater than negative number and less that positive

I need some help with a formula. I'm trying to work out the minimum value if greater than -365 but less that 365.

{=MIN(IF((E46:J46<365)*(E46:J46>-365),E46:J46))}

This is the formula I have so far, which is ignoring anything less that -365 and anything over 365.

It gives me the correct value if the minimum is less than 0 but anything greater than 0 is getting ignored, and the cell displays a 0.

For example,

E -42101
F 80
G -35
H 367
I "blank"
J "blank"

Then I will get -35 as my minimum value, if I delete this from the cell my minimum values turns to 0, when it should be 80.

Thank you in advance.

Upvotes: 1

Views: 4012

Answers (3)

atame
atame

Reputation: 521

With help from Ron Rosenfeld and Maxime Goguillon who have provided both great answers.

Ron Rosenfeld

{=IFERROR(AGGREGATE(15,6,E46:J46/((E46:J46)*(E46:J46>-365)*(E46:J46<365))*E46:J46,1),"")}

Maxime Goguillon

{=IF(MIN(IF((E46:J46<365)*(E46:J46>-365)*NOT(ISBLANK(E46:J46)),E46:J46))=0,"",MIN(IF((E46:J46<365)*(E46:J46>-365)*NOT(ISBLANK(E46:J46)),E46:J46)))}

Both of these work perfectly.

The help was much appreciated!!!!

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60224

Try:

=AGGREGATE(15,6,IF(NOT(ISNUMBER(myRng)),NA(),IF(myRng>365,NA(),IF(myRng<-365,NA(),myRng))),1)

entered with ctrl + shift + enter

MIN does not ignore errors, but AGGREGATE can.

For a normally entered formula, provoked by @XORLX below:

=AGGREGATE(15,6,1/(ISNUMBER(myRng)*(myRng>-365)*(myRng<365))*myRng,1)

or, more succinctly:

=AGGREGATE(15,6,myRng/(ISNUMBER(myRng)*(myRng>-365)*(myRng<365)),1)

To avoid returning #NUM! if there are no entries meeting the criteria, one solution is to use IFERROR:

=IFERROR(AGGREGATE(15,6,myRng/(ISNUMBER(myRng)*(myRng<365)*(myRng>-365)),1),"")

Upvotes: 3

Maxime Goguillon
Maxime Goguillon

Reputation: 21

Like proposed in the comments, adding a check on whether the cell is blank or not should be sufficient to resolve your issue:

{=MIN(IF((E46:J46<365)*(E46:J46>-365)*(E46:J46<>""),E46:J46))}

(Entered with Ctrl-Shift-Enter)

Upvotes: 2

Related Questions