Results 1 to 6 of 6

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

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 ...

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    101

    Default 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?

    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
    LEVELTYPELEAD*TIME*
    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. #2
    Board Regular goblin's Avatar
    Join Date
    Apr 2003
    Location
    Reykjavik
    Posts
    469

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

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

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Posts
    101

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

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

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    64,415

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

    Quote 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. #5
    Board Regular
    Join Date
    Mar 2002
    Posts
    101

    Default

    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)}
    Thank you Aladin. This latest version bullet-proofs the formula.

    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

  6. #6
    New Member
    Join Date
    Feb 2013
    Posts
    3

    Default 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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com