{=MAX((A$3:A$13=A18)*(B$3:B$13<>1)*(C$3:C$13))}
This is a discussion on Excluding data from "MAX(IF..." array formula within the Excel Questions forums, part of the Question Forums category; 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 ...
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?
Thanks in advance.
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
=
A B C D 1 INPUT*FILE * * * 2 LEVEL TYPE LEAD*TIME * 3 1 1 30 * 4 2 4 15 * 5 2 4 25 * 6 2 1 30 * 7 3 4 60 * 8 3 4 15 * 9 3 9 26 * 10 4 4 30 * 11 4 4 30 * 12 2 4 95 * 13 2 4 15 *
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.
{=MAX((A$3:A$13=A18)*(B$3:B$13<>1)*(C$3:C$13))}
Thanks! Works perfectly. I wasn't even close, never thought to remove the "if" statement.
Regards,
Joe
It can also be set up with IF included, although it is not needed in most cases.Originally Posted by Josue Barocio
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)}
Thank you Aladin. This latest version bullet-proofs the formula.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.
Regards,
Joe
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
Like this thread? Share it with others