| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to rank
I am using excel 2003. I need to know how to rank the following?
results 10 -2 -5 25 31 ranking i would like 1 2 3 1 1 I want all results 0 to get a ranking of 1. and the ones that are <0 i want stack ranked with the first result to get a ranking of 2. Can someone help? -- John |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to rank
How should these numbers be ranked:
10...0...-2...-2...-3 -1...-1...-5...-5...-4 -- Biff Microsoft Excel MVP "jt114" wrote in message ... I am using excel 2003. I need to know how to rank the following? results 10 -2 -5 25 31 ranking i would like 1 2 3 1 1 I want all results 0 to get a ranking of 1. and the ones that are <0 i want stack ranked with the first result to get a ranking of 2. Can someone help? -- John |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to rank
how they should be ranked
10 0 -2 -2 -3 -1 -1 -5 -5 -4 1 1 3 3 4 2 2 6 6 5 -- John "T. Valko" wrote: How should these numbers be ranked: 10...0...-2...-2...-3 -1...-1...-5...-5...-4 -- Biff Microsoft Excel MVP "jt114" wrote in message ... I am using excel 2003. I need to know how to rank the following? results 10 -2 -5 25 31 ranking i would like 1 2 3 1 1 I want all results 0 to get a ranking of 1. and the ones that are <0 i want stack ranked with the first result to get a ranking of 2. Can someone help? -- John |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to rank
I'm not real sure about this but it works based on your sample numbers and
the results you say you want: Numbers in the range A1:J1 Entered in A2 and copied across to J2: =IF(A10,1,SUMPRODUCT(--($A1:$J1<=0),--(A1<$A1:$J1),1/COUNTIF($A1:$J1,$A1:$J1))+1) -- Biff Microsoft Excel MVP "jt114" wrote in message ... how they should be ranked 10 0 -2 -2 -3 -1 -1 -5 -5 -4 1 1 3 3 4 2 2 6 6 5 -- John "T. Valko" wrote: How should these numbers be ranked: 10...0...-2...-2...-3 -1...-1...-5...-5...-4 -- Biff Microsoft Excel MVP "jt114" wrote in message ... I am using excel 2003. I need to know how to rank the following? results 10 -2 -5 25 31 ranking i would like 1 2 3 1 1 I want all results 0 to get a ranking of 1. and the ones that are <0 i want stack ranked with the first result to get a ranking of 2. Can someone help? -- John |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to rank
worked on all but one. here are the numbers i am using
42.77 -13.55 33.24 65.61 27.83 -13.83 10.72 -29.75 -3.33 fomula 1 2 1 1 1 3 1 4 1 it is giving a rank of 1 to the -3.33 which should be a rank of 2 -- John "T. Valko" wrote: I'm not real sure about this but it works based on your sample numbers and the results you say you want: Numbers in the range A1:J1 Entered in A2 and copied across to J2: =IF(A10,1,SUMPRODUCT(--($A1:$J1<=0),--(A1<$A1:$J1),1/COUNTIF($A1:$J1,$A1:$J1))+1) -- Biff Microsoft Excel MVP "jt114" wrote in message ... how they should be ranked 10 0 -2 -2 -3 -1 -1 -5 -5 -4 1 1 3 3 4 2 2 6 6 5 -- John "T. Valko" wrote: How should these numbers be ranked: 10...0...-2...-2...-3 -1...-1...-5...-5...-4 -- Biff Microsoft Excel MVP "jt114" wrote in message ... I am using excel 2003. I need to know how to rank the following? results 10 -2 -5 25 31 ranking i would like 1 2 3 1 1 I want all results 0 to get a ranking of 1. and the ones that are <0 i want stack ranked with the first result to get a ranking of 2. Can someone help? -- John |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to rank
worked on all but one.
If -3.33 should be ranked 2 then all the other negative numbers rank needs to increase by 1, right? How is 0 ranked? Should it be ranked with the positive numbers or with the negative numbers? -- Biff Microsoft Excel MVP "jt114" wrote in message ... worked on all but one. here are the numbers i am using 42.77 -13.55 33.24 65.61 27.83 -13.83 10.72 -29.75 -3.33 fomula 1 2 1 1 1 3 1 4 1 it is giving a rank of 1 to the -3.33 which should be a rank of 2 -- John "T. Valko" wrote: I'm not real sure about this but it works based on your sample numbers and the results you say you want: Numbers in the range A1:J1 Entered in A2 and copied across to J2: =IF(A10,1,SUMPRODUCT(--($A1:$J1<=0),--(A1<$A1:$J1),1/COUNTIF($A1:$J1,$A1:$J1))+1) -- Biff Microsoft Excel MVP "jt114" wrote in message ... how they should be ranked 10 0 -2 -2 -3 -1 -1 -5 -5 -4 1 1 3 3 4 2 2 6 6 5 -- John "T. Valko" wrote: How should these numbers be ranked: 10...0...-2...-2...-3 -1...-1...-5...-5...-4 -- Biff Microsoft Excel MVP "jt114" wrote in message ... I am using excel 2003. I need to know how to rank the following? results 10 -2 -5 25 31 ranking i would like 1 2 3 1 1 I want all results 0 to get a ranking of 1. and the ones that are <0 i want stack ranked with the first result to get a ranking of 2. Can someone help? -- John |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to rank
0 is ranked as 1. then starting with lowest negative number ranked as 2 and
so on until you rank the hightest negative number. -- John "T. Valko" wrote: worked on all but one. If -3.33 should be ranked 2 then all the other negative numbers rank needs to increase by 1, right? How is 0 ranked? Should it be ranked with the positive numbers or with the negative numbers? -- Biff Microsoft Excel MVP "jt114" wrote in message ... worked on all but one. here are the numbers i am using 42.77 -13.55 33.24 65.61 27.83 -13.83 10.72 -29.75 -3.33 fomula 1 2 1 1 1 3 1 4 1 it is giving a rank of 1 to the -3.33 which should be a rank of 2 -- John "T. Valko" wrote: I'm not real sure about this but it works based on your sample numbers and the results you say you want: Numbers in the range A1:J1 Entered in A2 and copied across to J2: =IF(A10,1,SUMPRODUCT(--($A1:$J1<=0),--(A1<$A1:$J1),1/COUNTIF($A1:$J1,$A1:$J1))+1) -- Biff Microsoft Excel MVP "jt114" wrote in message ... how they should be ranked 10 0 -2 -2 -3 -1 -1 -5 -5 -4 1 1 3 3 4 2 2 6 6 5 -- John "T. Valko" wrote: How should these numbers be ranked: 10...0...-2...-2...-3 -1...-1...-5...-5...-4 -- Biff Microsoft Excel MVP "jt114" wrote in message ... I am using excel 2003. I need to know how to rank the following? results 10 -2 -5 25 31 ranking i would like 1 2 3 1 1 I want all results 0 to get a ranking of 1. and the ones that are <0 i want stack ranked with the first result to get a ranking of 2. Can someone help? -- John |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to rank
Ok, assuming no empty cells in the range:
=IF(A1=0,1,SUMPRODUCT(--($A1:$I1<0),--(A1<$A1:$I1),1/COUNTIF($A1:$I1,$A1:$I1))+1+(COUNTIF($A1:$I1,"=0" )0)) -- Biff Microsoft Excel MVP "jt114" wrote in message ... 0 is ranked as 1. then starting with lowest negative number ranked as 2 and so on until you rank the hightest negative number. -- John "T. Valko" wrote: worked on all but one. If -3.33 should be ranked 2 then all the other negative numbers rank needs to increase by 1, right? How is 0 ranked? Should it be ranked with the positive numbers or with the negative numbers? -- Biff Microsoft Excel MVP "jt114" wrote in message ... worked on all but one. here are the numbers i am using 42.77 -13.55 33.24 65.61 27.83 -13.83 10.72 -29.75 -3.33 fomula 1 2 1 1 1 3 1 4 1 it is giving a rank of 1 to the -3.33 which should be a rank of 2 -- John "T. Valko" wrote: I'm not real sure about this but it works based on your sample numbers and the results you say you want: Numbers in the range A1:J1 Entered in A2 and copied across to J2: =IF(A10,1,SUMPRODUCT(--($A1:$J1<=0),--(A1<$A1:$J1),1/COUNTIF($A1:$J1,$A1:$J1))+1) -- Biff Microsoft Excel MVP "jt114" wrote in message ... how they should be ranked 10 0 -2 -2 -3 -1 -1 -5 -5 -4 1 1 3 3 4 2 2 6 6 5 -- John "T. Valko" wrote: How should these numbers be ranked: 10...0...-2...-2...-3 -1...-1...-5...-5...-4 -- Biff Microsoft Excel MVP "jt114" wrote in message ... I am using excel 2003. I need to know how to rank the following? results 10 -2 -5 25 31 ranking i would like 1 2 3 1 1 I want all results 0 to get a ranking of 1. and the ones that are <0 i want stack ranked with the first result to get a ranking of 2. Can someone help? -- John |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to rank
perfect. thanks for all your help
-- John "T. Valko" wrote: Ok, assuming no empty cells in the range: =IF(A1=0,1,SUMPRODUCT(--($A1:$I1<0),--(A1<$A1:$I1),1/COUNTIF($A1:$I1,$A1:$I1))+1+(COUNTIF($A1:$I1,"=0" )0)) -- Biff Microsoft Excel MVP "jt114" wrote in message ... 0 is ranked as 1. then starting with lowest negative number ranked as 2 and so on until you rank the hightest negative number. -- John "T. Valko" wrote: worked on all but one. If -3.33 should be ranked 2 then all the other negative numbers rank needs to increase by 1, right? How is 0 ranked? Should it be ranked with the positive numbers or with the negative numbers? -- Biff Microsoft Excel MVP "jt114" wrote in message ... worked on all but one. here are the numbers i am using 42.77 -13.55 33.24 65.61 27.83 -13.83 10.72 -29.75 -3.33 fomula 1 2 1 1 1 3 1 4 1 it is giving a rank of 1 to the -3.33 which should be a rank of 2 -- John "T. Valko" wrote: I'm not real sure about this but it works based on your sample numbers and the results you say you want: Numbers in the range A1:J1 Entered in A2 and copied across to J2: =IF(A10,1,SUMPRODUCT(--($A1:$J1<=0),--(A1<$A1:$J1),1/COUNTIF($A1:$J1,$A1:$J1))+1) -- Biff Microsoft Excel MVP "jt114" wrote in message ... how they should be ranked 10 0 -2 -2 -3 -1 -1 -5 -5 -4 1 1 3 3 4 2 2 6 6 5 -- John "T. Valko" wrote: How should these numbers be ranked: 10...0...-2...-2...-3 -1...-1...-5...-5...-4 -- Biff Microsoft Excel MVP "jt114" wrote in message ... I am using excel 2003. I need to know how to rank the following? results 10 -2 -5 25 31 ranking i would like 1 2 3 1 1 I want all results 0 to get a ranking of 1. and the ones that are <0 i want stack ranked with the first result to get a ranking of 2. Can someone help? -- John |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to rank
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "jt114" wrote in message ... perfect. thanks for all your help -- John "T. Valko" wrote: Ok, assuming no empty cells in the range: =IF(A1=0,1,SUMPRODUCT(--($A1:$I1<0),--(A1<$A1:$I1),1/COUNTIF($A1:$I1,$A1:$I1))+1+(COUNTIF($A1:$I1,"=0" )0)) -- Biff Microsoft Excel MVP "jt114" wrote in message ... 0 is ranked as 1. then starting with lowest negative number ranked as 2 and so on until you rank the hightest negative number. -- John "T. Valko" wrote: worked on all but one. If -3.33 should be ranked 2 then all the other negative numbers rank needs to increase by 1, right? How is 0 ranked? Should it be ranked with the positive numbers or with the negative numbers? -- Biff Microsoft Excel MVP "jt114" wrote in message ... worked on all but one. here are the numbers i am using 42.77 -13.55 33.24 65.61 27.83 -13.83 10.72 -29.75 -3.33 fomula 1 2 1 1 1 3 1 4 1 it is giving a rank of 1 to the -3.33 which should be a rank of 2 -- John "T. Valko" wrote: I'm not real sure about this but it works based on your sample numbers and the results you say you want: Numbers in the range A1:J1 Entered in A2 and copied across to J2: =IF(A10,1,SUMPRODUCT(--($A1:$J1<=0),--(A1<$A1:$J1),1/COUNTIF($A1:$J1,$A1:$J1))+1) -- Biff Microsoft Excel MVP "jt114" wrote in message ... how they should be ranked 10 0 -2 -2 -3 -1 -1 -5 -5 -4 1 1 3 3 4 2 2 6 6 5 -- John "T. Valko" wrote: How should these numbers be ranked: 10...0...-2...-2...-3 -1...-1...-5...-5...-4 -- Biff Microsoft Excel MVP "jt114" wrote in message ... I am using excel 2003. I need to know how to rank the following? results 10 -2 -5 25 31 ranking i would like 1 2 3 1 1 I want all results 0 to get a ranking of 1. and the ones that are <0 i want stack ranked with the first result to get a ranking of 2. Can someone help? -- John |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| rank the numbers / range of data using 'RANK' and 'ABS' | Excel Worksheet Functions | |||
| RANK, duplicate ranking but no gaps in rank | Excel Worksheet Functions | |||
| Rank where lowest value is highest rank | Excel Worksheet Functions | |||
| Does Correl/Rank combo work eg CORREL(cols E & H) where E&H=RANK(. | Excel Worksheet Functions | |||
| Rank items, select one start date, have remaining dates follow based on rank | Excel Discussion (Misc queries) | |||
Linear Mode
