Solved

How do I rank by category in iWork Numbers

Posted on 2009-07-14
8
1,438 Views
Last Modified: 2012-05-07
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.

Please help.
0
Comment
Question by:AGoodKeenMan
8 Comments
 
LVL 38

Expert Comment

by:jeverist
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

by:jeverist
ID: 24852083
AGoodKeenMan,

Here's the file...

Jim
Q-24568076.xls
0
 
LVL 5

Author Comment

by:AGoodKeenMan
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

by:jeverist
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 5

Author Comment

by:AGoodKeenMan
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

by:
barry houdini earned 500 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

by:IncisiveOne
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:
a  forgot about excel
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

by:AGoodKeenMan
ID: 31603179
Works perfectly thanks Barry.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Steve Jobs once said that Blu-ray is "a bag of hurt". As Macs users well known, things haven’t settled down (at least not from Apple’s perspective). Several years after that comment, Mac OS X still doesn’t support Blu-ray playback, nor has any Mac s…
Users will learn how to set proper sequence settings, scale images, paste attributes, add transitions, fades, and music. Open up Final Cut Pro 7 and Create a new Project: Set the Sequence Settings. a) Click File > Easy Setup > Format > Apple ProRe…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now