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

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

would work but it's horrible.


"griff" wrote:

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

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



"Sam Wilson" wrote:

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

would work but it's horrible.


"griff" wrote:

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

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

"Sam Wilson" wrote in message
...
=min(max(m23,0),max(j23,0),max(g23,0),max(d23,0))

would work but it's horrible.


"griff" wrote:

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

[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

"griff" wrote:

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

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(M230,M23,99999),IF(J230,J23,99999),IF(G2 30,G23,99999),IF(D230,D23,99999))

--
John C


"griff" wrote:

[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

"griff" wrote:

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

griff wrote...
....
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(M230,M23),IF(J230,J23),IF(G230,G23),IF( D230,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:M230),D23 :M23))

Hi griff,

Another spin on John C's answer:

=MIN(IF(M230,M23,MAX(M23,J23,G23,D23)),IF(J230,J 23,MAX(M23,J23,G23,D23)),IF(G230,G23,MAX(M23,J23, G23,D23)),IF(D230,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" wrote:

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

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

"John C" wrote:

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(M230,M23,99999),IF(J230,J23,99999),IF(G2 30,G23,99999),IF(D230,D23,99999))

--
John C


"griff" wrote:

[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

"griff" wrote:

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