G.J. Mullaly
Level 1 (4 points)
Mac App Store

Q: Building a 'maximumif' function

Numbers comes with an "Averageif" function but so far as I can see, no equivalent 'Maximumif', 'Minimumif' or 'Medianif' functions. I have tried to build formulas to return these values with no success (I'm new to this exercise). Any suggestions as to how to accomplish this would be much appreciated.

MacBook Pro, macOS Sierra (10.12)

Posted on Oct 15, 2016 9:40 PM

by Barry,Apple recommended
Barry Barry
Level 7 (32,714 points)
iWork
A:

Hi G.J.,

 

Numbers doesn't support MAXIF or MINIF, but returning max and min values with conditions is possible, as can be seen in these tables displaying MAX and MIN values within each month:

Screen Shot 2016-10-15 at 11.02.08 PM.png

Details can be found here: Re: Finding min/max number within a selected date range - Please HELP!

 

If this doesn't fit the bill, please provide more detail regarding the "IF" and values parts of what you are attempting.

A screen shot of the table, showing the data would also be useful.

 

Regards,

Barry

Posted on Oct 15, 2016 11:05 PM

Close
G.J. Mullaly

Q: Building a 'maximumif' function

  • All replies
  • Helpful answers

  • by Barry,Apple recommended

    Barry Barry Oct 15, 2016 11:05 PM in response to G.J. Mullaly
    Level 7 (32,714 points)
    iWork
    Oct 15, 2016 11:05 PM in response to G.J. Mullaly

    Hi G.J.,

     

    Numbers doesn't support MAXIF or MINIF, but returning max and min values with conditions is possible, as can be seen in these tables displaying MAX and MIN values within each month:

    Screen Shot 2016-10-15 at 11.02.08 PM.png

    Details can be found here: Re: Finding min/max number within a selected date range - Please HELP!

     

    If this doesn't fit the bill, please provide more detail regarding the "IF" and values parts of what you are attempting.

    A screen shot of the table, showing the data would also be useful.

     

    Regards,

    Barry

  • by G.J. Mullaly,

    G.J. Mullaly G.J. Mullaly Oct 16, 2016 6:14 PM in response to Barry
    Level 1 (4 points)
    Mac App Store
    Oct 16, 2016 6:14 PM in response to Barry

    Thanks for such a quick response Barry. I have not yet had a chance to apply your suggestions but I will report on the outcome when I do. In the meantime, do you know of any way to find a median in the way that the 'averageif' function does? Thanks again for all your help!

  • by Barry,

    Barry Barry Oct 16, 2016 9:35 PM in response to G.J. Mullaly
    Level 7 (32,714 points)
    iWork
    Oct 16, 2016 9:35 PM in response to G.J. Mullaly

    Hi G.J.

     

    Like MAXIF and MINIF, Numbers does not include or support a MEDIANIF function.  But the result can be obtained in the same manner. Substitute MEDIAN for MIN or MAX in the formulas included in the linked post.

    Screen Shot 2016-10-16 at 9.29.47 PM.png

    E2: IF(COUNTIF(Table 1::$B,$B2)<1,"",MEDIAN(OFFSET(Table 1::$A$1,MATCH($B2,Table 1::$B,0)−1,2,COUNTIF(Table 1::$B,$B2))))

     

    Fill down to the end of column E.

     

    Regards,

    Barry