rank
 

my post from function's division:

Hi,
I have sum data of five columns:

col1 col2 col3 col4 col5 sum_of_5_columns item
5 6 6 20 30 67 1
6 5 6 25 20 62 3*
3 5 4 28 22 62 2
etc..

*less sum of last three columns

I used function LARGE but occured the same result in
sum_of_5_columns. i don't want equal position..

I'd like create (in column item) function with show
position from max to min sum_of_5_columns.
If sums are equal then count sum of last three columns
(3,4,5)
If sums are still equal then count sum of last two columns
(4,5)
If sums are still equal then count value of five column (5)

Remark: My data can't be sort by excel.

Any help in function or VBA function will be appreciated.


I use excel 2k.
Best Regards
Mark

rank
 

Mark,

For your example table of numbers in A2:E4 (with headers in A1:E1):

F2: =SUM(A2:E2)
G2: =SUM(C2:E2)
H2: =SUM(D2:E2)
I2:
=RANK(F2,$F$2:$F$4)+RANK(G2,$G$2:$G$4)/10+RANK(H2,$H$2:$H$4)/100+RANK(E2,$E$
2:$E$4)/1000
J2: =RANK(I2,$I$2:$I$4,TRUE)

Copy these formulas down to match your data.

Note that if you have more than 10 values (but less than 100), you will need
to use 100, 10000, and 1000000 as your divisors in the formula in cell I2:
For up to 1000 values, use 1000, 1000000, and 1000000000 as the divisors,
and so on....

HTH,
Bernie
MS Excel MVP

'"Mark" wrote in message
...
my post from function's division:

Hi,
I have sum data of five columns:

col1 col2 col3 col4 col5 sum_of_5_columns item
5 6 6 20 30 67 1
6 5 6 25 20 62 3*
3 5 4 28 22 62 2
etc..

*less sum of last three columns

I used function LARGE but occured the same result in
sum_of_5_columns. i don't want equal position..

I'd like create (in column item) function with show
position from max to min sum_of_5_columns.
If sums are equal then count sum of last three columns
(3,4,5)
If sums are still equal then count sum of last two columns
(4,5)
If sums are still equal then count value of five column (5)

Remark: My data can't be sort by excel.

Any help in function or VBA function will be appreciated.


I use excel 2k.
Best Regards
Mark

rank
 

Hi,
a modification of Bernie Deitrick code:

G2: =1000*SUM(A2:E2)+100*SUM(C2:E2)+10*SUM(D2:E2)+E2
H2: =RANK(G2,$G$2:$G$4,0)

Adjust $G$2:$G$4 in RANK(G2,$G$2:$G$4) to match your data range, the
copy down.

Jare

--
Jare
-----------------------------------------------------------------------
Jarek's Profile: http://www.excelforum.com/member.php...info&userid=96
View this thread: http://www.excelforum.com/showthread.php?threadid=27691

rank
 

Hi Jarek!
Your assistance is very profesional,
thank you for helping!

Best Regards
Mark


-----Original Message-----

Hi,
a modification of Bernie Deitrick code:

G2: =1000*SUM(A2:E2)+100*SUM(C2:E2)+10*SUM(D2:E2)+E2
H2: =RANK(G2,$G$2:$G$4,0)

Adjust $G$2:$G$4 in RANK(G2,$G$2:$G$4) to match your data
range, then
copy down.

Jarek


--
Jarek
----------------------------------------------------------
--------------
Jarek's Profile: http://www.excelforum.com/member.php?
action=getinfo&userid=965
View this thread:
http://www.excelforum.com/showthread...hreadid=276917

.

rank
 

Thanx Bernie for you assistance!
Best Regards
Mark

-----Original Message-----
Mark,

For your example table of numbers in A2:E4 (with headers
in A1:E1):

F2: =SUM(A2:E2)
G2: =SUM(C2:E2)
H2: =SUM(D2:E2)
I2:
=RANK(F2,$F$2:$F$4)+RANK(G2,$G$2:$G$4)/10+RANK
(H2,$H$2:$H$4)/100+RANK(E2,$E$
2:$E$4)/1000
J2: =RANK(I2,$I$2:$I$4,TRUE)

Copy these formulas down to match your data.

Note that if you have more than 10 values (but less than
100), you will need
to use 100, 10000, and 1000000 as your divisors in the
formula in cell I2:
For up to 1000 values, use 1000, 1000000, and 1000000000
as the divisors,
and so on....

HTH,
Bernie
MS Excel MVP

'"Mark" wrote in message
...
my post from function's division:

Hi,
I have sum data of five columns:

col1 col2 col3 col4 col5 sum_of_5_columns item
5 6 6 20 30 67 1
6 5 6 25 20 62 3*
3 5 4 28 22 62 2
etc..

*less sum of last three columns

I used function LARGE but occured the same result in
sum_of_5_columns. i don't want equal position..

I'd like create (in column item) function with show
position from max to min sum_of_5_columns.
If sums are equal then count sum of last three columns
(3,4,5)
If sums are still equal then count sum of last two
columns
(4,5)
If sums are still equal then count value of five column
(5)

Remark: My data can't be sort by excel.

Any help in function or VBA function will be
appreciated.


I use excel 2k.
Best Regards
Mark



.