Order/Rank
 

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?

Order/Rank
 

Take a look MEDIAN Function in help menu

"Rod" wrote:

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?

Order/Rank
 

Will this work if

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

"Teethless mama" wrote:

Take a look MEDIAN Function in help menu

"Rod" wrote:

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?

Order/Rank
 

On Jul 28, 7:02 am, Rod wrote:
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?

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

Order/Rank
 

Try something like this:

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

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

--
Biff
Microsoft Excel MVP


"Rod" wrote in message
...
Will this work if

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

"Teethless mama" wrote:

Take a look MEDIAN Function in help menu

"Rod" wrote:

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?

Order/Rank
 

Does this work for my last posting?

"Ken Johnson" wrote:

On Jul 28, 7:02 am, Rod wrote:
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?

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

Order/Rank
 

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

Order/Rank
 

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

"Ken Johnson" wrote:

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

Order/Rank
 

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.

--
Biff
Microsoft Excel MVP


"Rod" wrote in message
...
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

"Ken Johnson" wrote:

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

Order/Rank
 

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,R OWS($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

"Rod" wrote:

Will this work if

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

"Teethless mama" wrote:

Take a look MEDIAN Function in help menu

"Rod" wrote:

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?

Order/Rank
 

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: EditCopy
Select the range D47:D51
Then: EditPaste SpecialAddOK

--
Biff
Microsoft Excel MVP


"Rod" wrote in message
...
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,R OWS($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

"Rod" wrote:

Will this work if

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

"Teethless mama" wrote:

Take a look MEDIAN Function in help menu

"Rod" wrote:

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?

Order/Rank
 

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!

"T. Valko" wrote:

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: EditCopy
Select the range D47:D51
Then: EditPaste SpecialAddOK

--
Biff
Microsoft Excel MVP


"Rod" wrote in message
...
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,R OWS($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

"Rod" wrote:

Will this work if

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

"Teethless mama" wrote:

Take a look MEDIAN Function in help menu

"Rod" wrote:

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?

Order/Rank
 

Hmmm....

These 2 formulas are essentially the same:

=SUMIF(G$47:G$51,"=1",E$47:E$51)
=SUMIF(G$47:G$51,ROWS($1:1),E$47:E$51)

I don't see why one works for you and the other doesn't but if you got
something that does work that's all that counts.

screencap:

http://img401.imageshack.us/img401/3738/sumif1st6.jpg


--
Biff
Microsoft Excel MVP


"Rod" wrote in message
...
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!

"T. Valko" wrote:

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: EditCopy
Select the range D47:D51
Then: EditPaste SpecialAddOK

--
Biff
Microsoft Excel MVP


"Rod" wrote in message
...
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,R OWS($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

"Rod" wrote:

Will this work if

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

"Teethless mama" wrote:

Take a look MEDIAN Function in help menu

"Rod" wrote:

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?

Order/Rank
 

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!

"T. Valko" wrote:

Hmmm....

These 2 formulas are essentially the same:

=SUMIF(G$47:G$51,"=1",E$47:E$51)
=SUMIF(G$47:G$51,ROWS($1:1),E$47:E$51)

I don't see why one works for you and the other doesn't but if you got
something that does work that's all that counts.

screencap:

http://img401.imageshack.us/img401/3738/sumif1st6.jpg


--
Biff
Microsoft Excel MVP


"Rod" wrote in message
...
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!

"T. Valko" wrote:

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: EditCopy
Select the range D47:D51
Then: EditPaste SpecialAddOK

--
Biff
Microsoft Excel MVP


"Rod" wrote in message
...
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,R OWS($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

"Rod" wrote:

Will this work if

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

"Teethless mama" wrote:

Take a look MEDIAN Function in help menu

"Rod" wrote:

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?

Order/Rank
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Rod" wrote in message
...
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!

"T. Valko" wrote:

Hmmm....

These 2 formulas are essentially the same:

=SUMIF(G$47:G$51,"=1",E$47:E$51)
=SUMIF(G$47:G$51,ROWS($1:1),E$47:E$51)

I don't see why one works for you and the other doesn't but if you got
something that does work that's all that counts.

screencap:

http://img401.imageshack.us/img401/3738/sumif1st6.jpg


--
Biff
Microsoft Excel MVP


"Rod" wrote in message
...
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!

"T. Valko" wrote:

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: EditCopy
Select the range D47:D51
Then: EditPaste SpecialAddOK

--
Biff
Microsoft Excel MVP


"Rod" wrote in message
...
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,R OWS($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

"Rod" wrote:

Will this work if

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

"Teethless mama" wrote:

Take a look MEDIAN Function in help menu

"Rod" wrote:

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?