MAXIF function

Question

Level 3

621 points

MAXIF function

I know a MAXIF function doesn't exist, but that's exactly what I need for a spreadsheet of mine. I'm looking for something that would function in the same way as SUMIF, but with MAX instead of SUM.
In the example below, I'd like to automate the Bill's Max cell. Any Ideas?
(Sorry about the lack of a screenshot. Couldn't figure out how to post it in these forums)

Bill 1
Jane 1
Bill 5
Jane 5
Sue 5
Bill 6
Jane 7
Sue 8
Jane 9

Bill’s Max:
Jane’s Max:
Sue’s Max:

15“ MBP, 2.4 GHz Intel Core 2 Duo, 2 GB RAM, Mac OS X (10.4.10), 40 GB ext HD, 500 GB ext HD, 128 MB flash drive/ mouse combo

Posted on Dec 14, 2007 4:28 PM

Reply

Answer 1

Dec 14, 2007 6:13 PM in response to Andrew Tomlin

Hi Andrew,

If you add a column for each person and fill each with IF statements that conditionally copy that person's score, you can easily create the Max statistic for each of those columns (People). Typical score filter expression would be: =IF(A2="Bill",B2,""), where A is the Name column and B is the Score column. Create similar columns for Jane, Sue, etc.

Bill's Max calculation would be =MAX(Bill), where Bill is the name in the header of Bill's filtered score column. The filtered score columns can be hidden.

Jerry

Reply

Answer 2

Andrew Tomlin Author

Level 3

621 points

Dec 15, 2007 10:49 AM in response to Jerrold Green1

Indeed this might have to be the way to get things done. I was hoping for a self-contained formula, but it looks like this is not possible. Thanks!

Message was edited by: Andrew Tomlin

Reply

Answer 3

WWJD

Level 6

15,830 points

Dec 15, 2007 11:19 AM in response to Andrew Tomlin

I tried to think of a way to do this from your original data layout but no success thus far. If your data does not have to be as you show, you could create a table like in Table "Processed." You could sort the columns by name then value as in "Sorted." In Excel, I'd create a Pivot Table from your raw data then process there.

Regards,

Reply

Answer 4

5-vv

Level 3

685 points

Dec 15, 2007 11:53 AM in response to Andrew Tomlin

If you data table is sorted by value (note: not by name as suggested previously), then you can use VLOOKUP to get the max by person.

For instance, if you data lives in a table "Scores" sorted by descending value with columns labeled "name" and "value", the max value for Bill can be found with this formula:

=VLOOKUP("Bill", Scores :: name:value,2,0)

You might make a separate table "People" with a row per name. If that table has a column "name", then the max for each person can be computed by filling down:

=VLOOKUP(name, Scores :: name:value,2,0)

!http://img86.imageshack.us/img86/9131/maxifxc5.png!

Reply

Answer 5

Andrew Tomlin Author

Level 3

621 points

Dec 15, 2007 7:25 PM in response to 5-vv

Alas, the raw data indeed needs to be presented in a seemingly-random order, because it's a register, with transactions listed by date. (I just simplified the example I posted here.) Thanks for your all's help though!

Another workaround that I've thought of is to have an additional column, one for each person. Enter in a bunch of IF formulas (or one duplicated), only displaying the numbers from the full data set if the name matches that of the name of that designated column. For example Bill would have a column that looks like:

1
empty
5
empty
empty
6
empty
empty
empty

Then you would have your "Bill's Max" cell simply take the max of those. It seems more elegant, but for a large number of people, it could be a hassle to enter a new column every time a new person shows up. Thoughts?

On a related topic, when I searched on Google, people have been lamenting the lack of this MAXIF style function of Numbers/Excel since 2002! It would seem like a relatively easy addition after 5 years of requests...

Reply

Answer 6

5-vv

Level 3

685 points

Dec 15, 2007 9:42 PM in response to Andrew Tomlin

OK... what about this:

We still use the idea of VLOOKUP() on a list shorted by value, but we have Numbers calculate the sort using formulas instead of using the Sort/Filter dialog box. This has the added advantage that you are no longer responsible for re-sorting when data changes; this will simple happen automatically as a consequence of auto-recalculation. It will, though, come at the expense of three new columns. You may hide these if you wish, though they do present the data sorted which might be of some use.

!http://img212.imageshack.us/img212/3238/maxif1yx4.png!

The relevant formulas follow. All should be filled down and will auto-fill as the "Scores" table grows.

rank=COUNTIF(value, ">"&value)+COUNTIF(B$2:B2,B2)
rankedName=INDEX(name, $rank,1)
rankedValue=INDEX(value,$rank,1)

In the "People" table, the per-person max VLOOKUP() formula becomes:

max=VLOOKUP(name,Scores :: rankedName:rankedValue,2,0)

Reply

Answer 7

5-vv

Level 3

685 points

Dec 15, 2007 10:00 PM in response to 5-vv

OK... what about this:

We still use the idea of VLOOKUP() on a list shorted by value, but we have Numbers calculate the sort using formulas instead of using the Sort/Filter dialog box. This has the added advantage that you are no longer responsible for re-sorting when data changes; this will simple happen automatically as a consequence of auto-recalculation. It will, though, come at the expense of three new columns. You may hide these if you wish, though they do present the data sorted which might be of some use.

!http://img227.imageshack.us/img227/4999/maxifnx5.png!

The relevant formulas follow. All should be filled down and will auto-fill as the "Scores" table grows.

rank=COUNTIF(value, ">"&value)+COUNTIF(B$2:B2,B2)
rankedName=INDEX(name,MATCH(ROW()-1, $rank, 0)-1,1)
rankedValue=INDEX(value,MATCH(ROW()-1,$rank,0)-1,1)

In the "People" table, the per-person max VLOOKUP() formula becomes:

max=VLOOKUP(name,Scores :: rankedName:rankedValue,2,0)

Reply

Answer 8

Dec 16, 2007 9:19 AM in response to Andrew Tomlin

Andrew,

When what you need is MAXIF, it seems like a logical extension to the function set. But then, what if you decide you'd like MINIF, MEDIF, MODEIF, STDIF, etc. Eventually you have a proliferation of functions to wade through when you are composing your project. The compilers become cumbersome, more opportunities for bugs. Your own work becomes more difficult to troubleshoot. I'd rather not have to squint to figure out the flow of my calculations.

A streamlined function set and building block approach will lead more reliable code and better coding practice - it's my preference, you might come to enjoy it too.

Jerry

Message was edited by: Jerrold Green1

Reply

Answer 9

KOENIG Yvan

Level 8

41,819 points

Dec 16, 2007 9:46 AM in response to 5-vv

Hello

As the OP wrote that some value may be empty, I tested the given responce and got an error in corresponding cell.

So I edited two formulas:

in rankedName

=IF(ISERROR(INDEX(name,MATCH(LIGNE()-1,$rank,0)-1,1)),"",INDEX(name,MATCH(LIGNE( )-1,$rank,0)-1,1))

in rankedValue:

=IF(ISERROR(INDEX(value,MATCH(LIGNE()-1,$rank,0)-1,1)),0,INDEX(value,MATCH(LIGNE ()-1,$rank,0)-1,1))

In fact, we may shorten the test isolating the part which generates the error but I leave it as an exercise 😉

Yvan KOENIG (from FRANCE dimanche 16 décembre 2007 18:45:55)

Reply

Answer 10

5-vv

Level 3

685 points

Dec 16, 2007 2:43 PM in response to Jerrold Green1

Jerrold Green1 wrote:
Andrew,

When what you need is MAXIF, it seems like a logical extension to the function set. But then, what if you decide you'd like MINIF, MEDIF, MODEIF, STDIF, etc. Eventually you have a proliferation of functions to wade through when you are composing your project. The compilers become cumbersome, more opportunities for bugs. Your own work becomes more difficult to troubleshoot. I'd rather not have to squint to figure out the flow of my calculations.

A streamlined function set and building block approach will lead more reliable code and better coding practice - it's my preference, you might come to enjoy it too.

Agreed... I have often thought that some sort of function, FILTER() maybe, could be implemented that returned a multi-area cell range (see the help on the INDEX() function for one of the few places this under utilized feature of cell ranges is mentioned). The result could be passed to SUM(), COUNT(), MAX(), and others to provide the 'IF' functionality. No new formula syntax or semantics would be necessary (unlike the bolted-on array feature in Excel).

=MAX(FILTER(name, "Bob", value))

While they are at it, maybe they could also generalize the logical operations available in this FILTER() command, since the functionality in SUMIF(), COUNTIF(), etc, currently leaves a bit to be desired. I also have thought a building block approach should be applied to this deficiency, but I haven't yet thought of a solution that didn't require modifying the formula syntax and semantics.

Reply