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)
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:
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
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!
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.
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
Building a 'maximumif' function
