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

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

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

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Posts
    101
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    81,454
    Post Thanks / Like
    Mentioned
    10 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

User Tag List

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