Solved

How do I rank by category in iWork Numbers

Posted on 2009-07-14
8
1,445 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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
 
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 Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

856 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