Solved

How do I rank by category in iWork Numbers

Posted on 2009-07-14
8
1,444 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VBA Help 18 45
VBA question, how many data set exists in the sheet 14 59
Return Column Number based on a specific value 25 26
vba autofilter in row 4 6 11
Hi Folks, This article is intended to throw some light or basically give an idea towards taking the first step in the world of Systems Administration. This would in the real context of the word "Systems Administration" which would mean right from…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

803 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