Excluding data from "MAX(IF..." array formula

Thanks:  0
Likes:  0

# Thread: Excluding data from "MAX(IF..." array formula

1. ## Excluding data from "MAX(IF..." array formula

I adapted the following array formula from this post from Juan Pablo Gonzáles: http://www.mrexcel.com/board2/viewto...ighlight=maxif

{=MAX(IF(A\$3:A\$13=A18,C\$3:C\$13))}

How can I modify the formula to exclude all items TYPE "1" in the following example?

Joe

******** ******************** ************************************************************************>
 Microsoft Excel - Book4 ___Running: xl2000 : OS = Windows Windows 2000
 (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
 A1 =

A
B
C
D
1
INPUT*FILE***
2
3
1130*
4
2415*
5
2425*
6
2130*
7
3460*
8
3415*
9
3926*
10
4430*
11
4430*
12
2495*
13
2415*
 Sheet1 *

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

2. ## Re: Excluding data from "MAX(IF..." array formula

{=MAX((A\$3:A\$13=A18)*(B\$3:B\$13<>1)*(C\$3:C\$13))}

3. ## Re: Excluding data from "MAX(IF..." array formula

Thanks! Works perfectly. I wasn't even close, never thought to remove the "if" statement.

4. ## Re: Excluding data from "MAX(IF..." array formula

Originally Posted by Josue Barocio
Thanks! Works perfectly. I wasn't even close, never thought to remove the "if" statement.
It can also be set up with IF included, although it is not needed in most cases.

BTW, if you have empty cells or formula-blanks in B-range, it is better to use...

{=MAX((A3:A13=F3)*(B3:B13<>1)*(B3:B13<>"")*C3:C13)}

5. It can also be set up with IF included, although it is not needed in most cases.

BTW, if you have empty cells or formula-blanks in B-range, it is better to use...

{=MAX((A3:A13=F3)*(B3:B13<>1)*(B3:B13<>"")*C3:C13)}

I tested the formula by inserting subformulas with other LEVEL values to be excluded, e.g., (B3:B13<>x), using the "*" symbol to string them together as as a sort of a logical AND. Is there a limit to the number of subformulas that can be inserted?

Since the subformulas are not actually nested, it would seem there should be no limit, as for the IF worksheet function. Thanks again.

6. ## Re: Excluding data from "MAX(IF..." array formula

Hi,

I know this is an old post, but I'm taking chances.

I'm using this formula to obtain the greatest number in an array. I am excluding a number in B6.

=MAX((B3:F3)*(B3:F3<>B6))

The result is correctly displayed in the formula box. The result shown in the spreadsheet is different than the result in the formula box.

Instead of showing the max amount it shows the min.

Is there a problem with my excel or my formula?
Thanks

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•