x
Solved

# How do I rank by category in iWork Numbers

Posted on 2009-07-14
Medium Priority
1,474 Views
I have a list of competitors, they all have an age class and a score. The table is categorized by the  class and sorted by score. I would like to add another column with the competitors rank in each class.
I have searched for an excel formula to rank by category and found this:

A2:A20 = class
C2:C20 = score

Enter this formula in D2 and copy down as needed:

=SUMPRODUCT(--(A\$2:A\$20=A2),--(C2<C\$2:C\$20))+1

I don't understand what is going on here and it doesn't appear to work in numbers.

0
Question by:AGoodKeenMan

LVL 38

Expert Comment

ID: 24852073
Hi AGoodKeenMan,

>  I don't understand what is going on here

The SUMPRODUCT formula is counting every class for the existing row (A\$2:A\$20=A2) that is less that the score for the row (C2<C\$2:C\$20) and adding 1 to give the row a rank by class from highest score (rank 1) to lowest (rank n).

>  it doesn't appear to work in numbers

It seems to work OK with numbers for me but is you have a mix of numbers and text then this version may work a little better:

=SUMPRODUCT(--(A\$2:A\$20=A2),--(--C2<--C\$2:C\$20))+1

See a sample file attached.

Jim
0

LVL 38

Expert Comment

ID: 24852083
AGoodKeenMan,

Here's the file...

Jim
Q-24568076.xls
0

LVL 5

Author Comment

ID: 24853219
There appears to be a problem with the file attached, it crashes Numbers.

I pasted your formula above and got the same result. The error I get is "The Range A3:A21 can't be used as a single value". Does that give you any clues as to what I have wrong?

The class is string "Senior", "Open", "Junior" etc. and the score is a number.

Can you explain what the SUMPRODUCT() function does?
0

LVL 38

Expert Comment

ID: 24853818
AGoodKeenMan,

As mentioned above, the SUMPRODUCT formula is counting all rows with a class equal to the existing row (A\$2:A\$20=A2) that have scores less than the score for the existing row (C2<C\$2:C\$20).  The rest of the formulat then adds 1 to give the row a rank by class from highest score (rank 1) to lowest (rank n).

>  it crashes Numbers

I'm not sure what you mean by that.  Can you post your file?  Here is another sample file that works with text values in column A.

Jim

Q-24568076-2.xls
0

LVL 5

Author Comment

ID: 24854296
Numbers is a spreadsheet program from Apple. I worked around the crashing problem and took a look at your example. It works fine in Excel but there seems to be a problem with that function in Numbers.
Can this be done with another function?
0

LVL 50

Accepted Solution

barry houdini earned 2000 total points
ID: 24854972
Which version of Numbers do you have? I believe that Numbers 09 has a COUNTIFS function similar to Excel 2007 so you could rewrite the SUMPRODUCT formula as a COUNTIFS, i.e.
=COUNTIFS(C\$2:C\$10,">"&C2,A\$2:A\$10,A2)+1
I can't test this in Numbers 09 because I don't have it. That formula works in Excel 2007 and I believe the syntax is very similar.......
Note: SUMPRODUCT exists in both Numbers 08 and Numbers 09 but Numbers doesn't support the coercion of Boolean values involved in the specific SUMPRODUCT formula here.....
regards, barry
0

LVL 6

Expert Comment

ID: 24855207
> I don't understand what is going on here and it doesn't appear to work in numbers.

It would help if you:
b  understood what you want to do
c  understood the Numbers functions available, how to use them
d  understood what you need to have (the columns in the correct format for [b] and [c] )
e  used Numbers functions (without regard to "how excel does it")

Cheers

0

LVL 5

Author Closing Comment

ID: 31603179
Works perfectly thanks Barry.
0

## Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.