Order/Rank

R

Rod

I have 5 categories with years associated with the categories:

Fund Education 15
Pay Off Consumer Debt 10
Provide Survivor Income Replacement 10
Pay Off Mortgage 20
Pay Final Expenses 20

I would like to order/rank these from greatest to least:

Fund Education 15 2
Pay Off Consumer Debt 10 3
Provide Survivor Income Replacement 10 3
Pay Off Mortgage 20 1
Pay Final Expenses 20 1

I will need to later add those years of the same order/rank. I tried sumif
using max and min but could not figure out how to do the middle values; is
there a better way to do this?
 
K

Ken Johnson

I have 5 categories with years associated with the categories:

Fund Education 15
Pay Off Consumer Debt 10
Provide Survivor Income Replacement 10
Pay Off Mortgage 20
Pay Final Expenses 20

I would like to order/rank these from greatest to least:

Fund Education 15 2
Pay Off Consumer Debt 10 3
Provide Survivor Income Replacement 10 3
Pay Off Mortgage 20 1
Pay Final Expenses 20 1

I will need to later add those years of the same order/rank. I tried sumif
using max and min but could not figure out how to do the middle values; is
there a better way to do this?
Click to expand...

Excel has a RANK function.
It skips rank position(s) when two or more values are equal, so
instead of 2,3,3,1,1 you will get 3,4,4,1,1 because the two twenties
are tied on rank 1, so rank 2 is skipped.

Assuming your years are in B2:B6, then =RANK(B2,B$2:B$6) in column C
will give the ranks.

=SUMIF($C$2:$C$6,1,$B$2:$B$6) results in 40 (20 + 20)
=SUMIF($C$2:$C$6,3,$B$2:$B$6) results in 15
=SUMIF($C$2:$C$6,4,$B$2:$B$6) results in 20 (10 + 10)

Ken Johnson
 
T

T. Valko

Try something like this:

=SUMPRODUCT(--(C2:C6>MIN(C2:C6)),--(C2:C6<MAX(C2:C6)),B2:B6)

Where C2:C6 are the ranks, B2:B6 are the values to sum.
 
R

Rod

Does this work for my last posting?

Ken Johnson said:
Excel has a RANK function.
It skips rank position(s) when two or more values are equal, so
instead of 2,3,3,1,1 you will get 3,4,4,1,1 because the two twenties
are tied on rank 1, so rank 2 is skipped.

Assuming your years are in B2:B6, then =RANK(B2,B$2:B$6) in column C
will give the ranks.

=SUMIF($C$2:$C$6,1,$B$2:$B$6) results in 40 (20 + 20)
=SUMIF($C$2:$C$6,3,$B$2:$B$6) results in 15
=SUMIF($C$2:$C$6,4,$B$2:$B$6) results in 20 (10 + 10)

Ken Johnson
Click to expand...
 
K

Ken Johnson

Excel ranks 15 years 3rd out of 20,20,15,10,10.

It's the only rank 3 so sumif results in 15.

I thought that was what you were trying to do when you said...

"I will need to later add those years of the same order/rank."

Ken Johnson
 
R

Rod

The years could be any of the following values: 0, 10, 15, 20,2 5, 30, 35. I
have 5 categories which I need to condense down to 1, 2, 3 or 4. Normally one
of the categories (education, debt, etc.) is combined with another, e.g debt
& income in the previouse example. I would like to simple get the rank #1,
#2, #3, #4. If there are only three, such as my example, two sets of them
have the same years (10's and 20's) so their dollar amounts are added (which
I can figure out once I have then ranked in sequential order - no gap such as
1's, 3, 4's. In a nutshell, I am looking for the result to be:
Fund Education 2
Pay Off Consumer Debt 3
Provide Survivor Income Replacement 3
Pay Off Mortgage 1
Pay Final Expenses 1
I can then add the amounts associated with 1's,
add the ammounts associated with 3's
have the total amounts in order for 1, 2 and 3.

Thanks
 
T

T. Valko

Ok, now I see what you want. Try these:

With your values to rank in B2:B6...

For the ranks, enter this formula in C2 and copy down to C6:

=IF(B2="","",SUMPRODUCT(--(B2<B$2:B$6),1/COUNTIF(B$2:B$6,B$2:B$6&""))+1)

Then for the sums:

=IF(ROWS($1:1)<=MAX(C$2:C$6),SUMIF(C$2:C$6,ROWS($1:1),B$2:B$6),"")

Copy down until you get blanks.
 
R

Rod

I am adapting this to the location of the data in my sheet. Everything looks
good until I get to the ROWS function. For me, "Fund Education"... begins in
B47. The second formulat looks like:
=IF(ROWS($47:47)<=MAX(E$47:E$51),SUMIF(E$47:E$51,ROWS($1:1),D$47:D$51),"").
I have a flag for other use in column D, so starting in B47 I have:
Fund Education Rider 15 $110,000 $25.82
Pay Off Consumer Debt Rider 10 $- $-
Provide Survivor Income Replacement Rider 10 $30,000 $5.05
Pay Off Mortgage Base 20 $100,000 $33.27
Pay Final Expenses Base 20 $10,000 $3.33
The ranking is working perfectly, but the summing is coming up 0.

Thanks
 
T

T. Valko

The ranking is working perfectly, but the summing is coming up 0.

Then the values in D47:D51 are TEXT and are not numeric numbers.

Try this:

Select an empty cell anywhere on your sheet that has the DEFAULT format of
GENERAL
Copy that empty cell: Edit>Copy
Select the range D47:D51
Then: Edit>Paste Special>Add>OK
 
R

Rod

The change did not work. I played with it and did:
=SUMIF(G$47:G$51,"=1",E$47:E$51) and it works.

Thanks for your great help!
 
R

Rod

I have never used the ROWS function before. I'll have to look at it for
future ref.. In any case, your thought of using SUMIF is what got me
thinking.

Thanks!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to Rank Name? 4
fund return v income 0
Order the names by ranking 8
Rank a Rank? 1
Ranking (Correct Order) 4
Calculating occupational sick pay entitlement 0
How may I lookup and return a value between two numbers 2
Ranking after a shoot-off 3

Top