Min, Max problem

[Guest]

hi
I would like to find the maximum (or the minimum) of a range of cell but without several numbers witch can be in the range or not...For exemple, my range is:A1
A B C
1 1 0 10
2 0.4 -5 7

And I want the maximum of this range but without number 10 and 11.... Can it be done just with one formula or do I have to do a new range
thanks for help

 

[Paul]

hi,
I would like to find the maximum (or the minimum) of a range of cell but

without several numbers witch can be in the range or not...For exemple, my
range is:A12

A B C D
1 1 0 10 9
2 0.4 -5 7 6

And I want the maximum of this range but without number 10 and 11.... Can

it be done just with one formula or do I have to do a new range ?

thanks for help

Try this array formula:
=MAX((A12<>10)*(A12<>11)*A12)
An array formula has to be entered using TRL+SHIFT+ENTER rather than just
ENTER.

 

[Guest]

Thanks a lot

 

[Guest]

hi again
In fact, this systems works for the maximum but it does not for the minimum. If i put one of the number witch are in the range, the minimum gives me 0. Else it is working. Do you know why it is doing this and how can I have a good number
thanks for help

 

[Paul]

hi again,
In fact, this systems works for the maximum but it does not for the

minimum. If i put one of the number witch are in the range, the minimum
gives me 0. Else it is working. Do you know why it is doing this and how can
I have a good number ?

thanks for help

When you have 10 or 11 in the range, one or other of the conditions
evaluates as FALSE, which when multiplied by the value itself gives 0. This
is what you need for MAX (at least for positive values - if you could have
all negative ones, see below), but makes the formula fail for MIN. So we
need a slightly modified formula:
=MIN(IF((A12<>10)*(A12<>11),A12))

The equivalent MAX formula is better than the one I gave earlier, as it
allows for all negative values:
=MAX(IF((A12<>10)*(A12<>11),A12))

 

[Guest]

Yes, Paul !!!!
I have even understand how it works...
That's great. thanks