How do I rank by category in iWork Numbers

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.

LVL 5
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
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
Commented:
AGoodKeenMan,

Here's the file...

Jim
Q-24568076.xls
0
Author Commented:
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
Commented:
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
Author Commented:
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
Commented:
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

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Commented:
> 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
Author Commented:
Works perfectly thanks Barry.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Apple Software

From novice to tech pro — start learning today.