Excel - Min function over 4 separate cells, but ignoring negatives

[griff]

good afternoon all

I have been sent a sheet which contains lots of Min formulae which look for
lowest of 4 separate cell values, eg =MIN(M23,J23,G23,D23)

The 'challenge' I have now is to get them to ignore any negative numbers.

Can anyone help? I have tried looking around, but so far the solutions I
have seen using MIN and SMALL all seem to need the data to be in contiguous
cells...

Thanks in advance for any helpful advice

cheers

Griff

 

[Sam Wilson]

=min(max(m23,0),max(j23,0),max(g23,0),max(d23,0))

would work but it's horrible.

 

[griff]

thanks for the reply Sam, but this didn't work (got a zero)
in this case G23 is -0.35 if it's any help

Regards

Griff

 

[David Biddulph]

But that would return an answer of 0, rather than ignoring the negative
number.

 

[griff]

[EDIT]

I just realised that for some of the rows in the sheet the cells used in the
formula are sometimes blank and need to be ignored too!

While MIN automatically ignores the blanks, using the formula Sam kindly
built returns a zero for these too

 

[John C]

It is still kind of an ugly formula, but if you use some arbitrarily high
number (I used 99999 in my formula, but feel free to make the number bigger
if needed) then the following formula should work:

=MIN(IF(M23>0,M23,99999),IF(J23>0,J23,99999),IF(G23>0,G23,99999),IF(D23>0,D23,99999))

--
John C

[EDIT]

I just realised that for some of the rows in the sheet the cells used in the
formula are sometimes blank and need to be ignored too!

While MIN automatically ignores the blanks, using the formula Sam kindly
built returns a zero for these too

good afternoon all

I have been sent a sheet which contains lots of Min formulae which look for
lowest of 4 separate cell values, eg =MIN(M23,J23,G23,D23)

The 'challenge' I have now is to get them to ignore any negative numbers.

Can anyone help? I have tried looking around, but so far the solutions I
have seen using MIN and SMALL all seem to need the data to be in contiguous
cells...

Thanks in advance for any helpful advice

cheers

Griff

 

[Harlan Grove]

I have been sent a sheet which contains lots of Min formulae which
look for lowest of 4 separate cell values, eg =MIN(M23,J23,G23,D23)

The 'challenge' I have now is to get them to ignore any negative
numbers.

....

=MIN(IF(M23>0,M23),IF(J23>0,J23),IF(G23>0,G23),IF(D23>0,D23))

or, since these are every 3rd column beginning with column D, you
could use the array formula

=MIN(IF((MOD(COLUMN(D23:M23),3)=1)*(D23:M23>0),D23:M23))

 

[pshepard]

Hi griff,

Another spin on John C's answer:

=MIN(IF(M23>0,M23,MAX(M23,J23,G23,D23)),IF(J23>0,J23,MAX(M23,J23,G23,D23)),IF(G23>0,G23,MAX(M23,J23,G23,D23)),IF(D23>0,D23,MAX(M23,J23,G23,D23)))

This will result in a zero or negative number if all values are either zero
or negative, in which case you would want to add another condition for that
case.

Hope this helps.
Peggy

 

[griff]

Thanks John - this worked fine and I don't mind if you think it's ugly!

Thanks to all the others who kindly offered their help too

cheers

Griff

It is still kind of an ugly formula, but if you use some arbitrarily high
number (I used 99999 in my formula, but feel free to make the number bigger
if needed) then the following formula should work:

=MIN(IF(M23>0,M23,99999),IF(J23>0,J23,99999),IF(G23>0,G23,99999),IF(D23>0,D23,99999))

--
John C

[EDIT]

I just realised that for some of the rows in the sheet the cells used in the
formula are sometimes blank and need to be ignored too!

While MIN automatically ignores the blanks, using the formula Sam kindly
built returns a zero for these too

good afternoon all

I have been sent a sheet which contains lots of Min formulae which look for
lowest of 4 separate cell values, eg =MIN(M23,J23,G23,D23)

The 'challenge' I have now is to get them to ignore any negative numbers.

Can anyone help? I have tried looking around, but so far the solutions I
have seen using MIN and SMALL all seem to need the data to be in contiguous
cells...

Thanks in advance for any helpful advice

cheers

Griff