Finding MAX number (excluding a particular number)

I did a search for help on this, but didn't find anything.
Using MAX to find the largest number in a column: =MAX(D3:D7)

If one of those numbers = 125, then I would like to exclude it from the
calculation. I've tried using DMAX, and IFs, but I can't get it to
work.

Use Array formula:

=MAX((D3:D7<>125)*(D3:D7))

Ctrl+Shift+Enter


David

On 24 Apr 2005 04:06:53 -0700, "Johnny" <flem1064@gmail.com> wrote:

>I did a search for help on this, but didn't find anything.
>Using MAX to find the largest number in a column: =MAX(D3:D7)
>
>If one of those numbers = 125, then I would like to exclude it from the
>calculation. I've tried using DMAX, and IFs, but I can't get it to
>work.

Use this **array** formula:

=MAX(IF(D3:D7<>125,D3:D7))

To enter an **array** formula, after typing it in, hold down <ctrl><shift>
while hitting <enter>. Excel will place braces {...} around the formula.


--ron