Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ranking different groups in one column
Hi
I have three columns, column A contains a 'region' name, column B, has the store name and column C has a 'score'. I want to rank the stores (B) within the region they're in (A) based on the score (C) and return the result into 1 column (D), is there a way to use the RANK formula so that I get each store's rank within their region returned into the one column (D)? Thanks in advance :) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ranking different groups in one column
Sort the three columns by region and score. The store with the highest score
will appear first in the set of data for any region. Then just use VLOOKUP() to get the store name. -- Gary''s Student - gsnu200776 "Marie Bayes" wrote: Hi I have three columns, column A contains a 'region' name, column B, has the store name and column C has a 'score'. I want to rank the stores (B) within the region they're in (A) based on the score (C) and return the result into 1 column (D), is there a way to use the RANK formula so that I get each store's rank within their region returned into the one column (D)? Thanks in advance :) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ranking different groups in one column
Thanks for your response GS, however, this won't solve my problem. I cannot
sort the data in the spreadsheet as I'm setting up a crystal report for a.n.other user from this datasheet and just 'pulling' the rank no into the report (so just the name of the top rank won't do), so there can be no sorting as the end user won't see the original data (the data is subject to change weekly and is just 'dumped' into the spreadsheet). I need to just pull in the ranked no. Therefore, back to my original question if that's ok, do you (or anyone) know of a way to use the ranking formula to do this? "Gary''s Student" wrote: Sort the three columns by region and score. The store with the highest score will appear first in the set of data for any region. Then just use VLOOKUP() to get the store name. -- Gary''s Student - gsnu200776 "Marie Bayes" wrote: Hi I have three columns, column A contains a 'region' name, column B, has the store name and column C has a 'score'. I want to rank the stores (B) within the region they're in (A) based on the score (C) and return the result into 1 column (D), is there a way to use the RANK formula so that I get each store's rank within their region returned into the one column (D)? Thanks in advance :) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ranking different groups in one column
If I understand your question, the actual name of the store is incidental.
You're looking for the rank within the regions in Column A. Say your datalist is in A1 to C20. Enter this formula in D1, and copy down: =SUMPRODUCT(($A$1:$A$20=A1)*(C1<$C$1:$C$20))+1 -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Marie Bayes" wrote in message ... Thanks for your response GS, however, this won't solve my problem. I cannot sort the data in the spreadsheet as I'm setting up a crystal report for a.n.other user from this datasheet and just 'pulling' the rank no into the report (so just the name of the top rank won't do), so there can be no sorting as the end user won't see the original data (the data is subject to change weekly and is just 'dumped' into the spreadsheet). I need to just pull in the ranked no. Therefore, back to my original question if that's ok, do you (or anyone) know of a way to use the ranking formula to do this? "Gary''s Student" wrote: Sort the three columns by region and score. The store with the highest score will appear first in the set of data for any region. Then just use VLOOKUP() to get the store name. -- Gary''s Student - gsnu200776 "Marie Bayes" wrote: Hi I have three columns, column A contains a 'region' name, column B, has the store name and column C has a 'score'. I want to rank the stores (B) within the region they're in (A) based on the score (C) and return the result into 1 column (D), is there a way to use the RANK formula so that I get each store's rank within their region returned into the one column (D)? Thanks in advance :) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ranking different groups in one column
I have a solution for you, but its not very clean. Here is some sample data:
reg3 store25 423 reg2 store15 477 reg2 store18 106 reg1 store2 456 reg2 store19 462 reg1 store1 104 reg3 store26 474 reg3 store28 27 reg1 store10 361 reg1 store7 36 reg1 store8 88 reg2 store11 22 reg2 store16 35 reg2 store20 25 reg3 store22 378 reg2 store14 390 reg3 store30 8 reg3 store29 17 reg1 store3 402 reg2 store17 15 reg1 store6 59 reg1 store5 245 reg3 store23 21 reg1 store9 341 reg3 store27 439 reg2 store12 125 reg2 store13 118 reg1 store4 12 reg3 store21 33 reg3 store24 4 As you see, three regions, a bunch of store names and scores. The first step is to find the highest score for a given region. In C1 enter the array formula: =MAX(IF(A1:A30="reg1",C1:C30)) This must be entered with a CNTRL-SHFT-ENTER rather than just the ENTER key. C1 displays 456 At this point we can try MATCH & INDEX, but the 456 may appear several times, so we must find the 456 for reg1 only. In E1 we enter: =SUMPRODUCT(--(A1:A30="reg1"),--(C1:C30=D1),(ROW(1:30))) This displays 4. So we want the fourth row. Finally in F1 we enter: =INDEX(B:B,E1) which displays: store2 You can use this until some gives you a more compact solution. -- Gary''s Student - gsnu200776 "Marie Bayes" wrote: Thanks for your response GS, however, this won't solve my problem. I cannot sort the data in the spreadsheet as I'm setting up a crystal report for a.n.other user from this datasheet and just 'pulling' the rank no into the report (so just the name of the top rank won't do), so there can be no sorting as the end user won't see the original data (the data is subject to change weekly and is just 'dumped' into the spreadsheet). I need to just pull in the ranked no. Therefore, back to my original question if that's ok, do you (or anyone) know of a way to use the ranking formula to do this? "Gary''s Student" wrote: Sort the three columns by region and score. The store with the highest score will appear first in the set of data for any region. Then just use VLOOKUP() to get the store name. -- Gary''s Student - gsnu200776 "Marie Bayes" wrote: Hi I have three columns, column A contains a 'region' name, column B, has the store name and column C has a 'score'. I want to rank the stores (B) within the region they're in (A) based on the score (C) and return the result into 1 column (D), is there a way to use the RANK formula so that I get each store's rank within their region returned into the one column (D)? Thanks in advance :) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ranking different groups in one column
Hi Ragdyer
I liked this response as it was less complicated than Gary's student's response, however, it returns the ranking of 1 in every cell, maybe it needs a small adjustment...? "Ragdyer" wrote: If I understand your question, the actual name of the store is incidental. You're looking for the rank within the regions in Column A. Say your datalist is in A1 to C20. Enter this formula in D1, and copy down: =SUMPRODUCT(($A$1:$A$20=A1)*(C1<$C$1:$C$20))+1 -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Marie Bayes" wrote in message ... Thanks for your response GS, however, this won't solve my problem. I cannot sort the data in the spreadsheet as I'm setting up a crystal report for a.n.other user from this datasheet and just 'pulling' the rank no into the report (so just the name of the top rank won't do), so there can be no sorting as the end user won't see the original data (the data is subject to change weekly and is just 'dumped' into the spreadsheet). I need to just pull in the ranked no. Therefore, back to my original question if that's ok, do you (or anyone) know of a way to use the ranking formula to do this? "Gary''s Student" wrote: Sort the three columns by region and score. The store with the highest score will appear first in the set of data for any region. Then just use VLOOKUP() to get the store name. -- Gary''s Student - gsnu200776 "Marie Bayes" wrote: Hi I have three columns, column A contains a 'region' name, column B, has the store name and column C has a 'score'. I want to rank the stores (B) within the region they're in (A) based on the score (C) and return the result into 1 column (D), is there a way to use the RANK formula so that I get each store's rank within their region returned into the one column (D)? Thanks in advance :) |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ranking different groups in one column
Thanks for your response, it is complicated, it looks like it'll work, but
I'm just waiting for, as you put it, a more compact solution (if one exists), if that's not forthcoming then I will take you up on this one!! Thanks for all the effort with this one though. "Gary''s Student" wrote: I have a solution for you, but its not very clean. Here is some sample data: reg3 store25 423 reg2 store15 477 reg2 store18 106 reg1 store2 456 reg2 store19 462 reg1 store1 104 reg3 store26 474 reg3 store28 27 reg1 store10 361 reg1 store7 36 reg1 store8 88 reg2 store11 22 reg2 store16 35 reg2 store20 25 reg3 store22 378 reg2 store14 390 reg3 store30 8 reg3 store29 17 reg1 store3 402 reg2 store17 15 reg1 store6 59 reg1 store5 245 reg3 store23 21 reg1 store9 341 reg3 store27 439 reg2 store12 125 reg2 store13 118 reg1 store4 12 reg3 store21 33 reg3 store24 4 As you see, three regions, a bunch of store names and scores. The first step is to find the highest score for a given region. In C1 enter the array formula: =MAX(IF(A1:A30="reg1",C1:C30)) This must be entered with a CNTRL-SHFT-ENTER rather than just the ENTER key. C1 displays 456 At this point we can try MATCH & INDEX, but the 456 may appear several times, so we must find the 456 for reg1 only. In E1 we enter: =SUMPRODUCT(--(A1:A30="reg1"),--(C1:C30=D1),(ROW(1:30))) This displays 4. So we want the fourth row. Finally in F1 we enter: =INDEX(B:B,E1) which displays: store2 You can use this until some gives you a more compact solution. -- Gary''s Student - gsnu200776 "Marie Bayes" wrote: Thanks for your response GS, however, this won't solve my problem. I cannot sort the data in the spreadsheet as I'm setting up a crystal report for a.n.other user from this datasheet and just 'pulling' the rank no into the report (so just the name of the top rank won't do), so there can be no sorting as the end user won't see the original data (the data is subject to change weekly and is just 'dumped' into the spreadsheet). I need to just pull in the ranked no. Therefore, back to my original question if that's ok, do you (or anyone) know of a way to use the ranking formula to do this? "Gary''s Student" wrote: Sort the three columns by region and score. The store with the highest score will appear first in the set of data for any region. Then just use VLOOKUP() to get the store name. -- Gary''s Student - gsnu200776 "Marie Bayes" wrote: Hi I have three columns, column A contains a 'region' name, column B, has the store name and column C has a 'score'. I want to rank the stores (B) within the region they're in (A) based on the score (C) and return the result into 1 column (D), is there a way to use the RANK formula so that I get each store's rank within their region returned into the one column (D)? Thanks in advance :) |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ranking different groups in one column
Check to make sure your actual ranges match the ranges in the formula you're
using. This works perfectly on the 30 row example that Gary posted: =SUMPRODUCT(($A$1:$A$30=A1)*(C1<$C$1:$C$30))+1 -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Marie Bayes" wrote in message ... Hi Ragdyer I liked this response as it was less complicated than Gary's student's response, however, it returns the ranking of 1 in every cell, maybe it needs a small adjustment...? "Ragdyer" wrote: If I understand your question, the actual name of the store is incidental. You're looking for the rank within the regions in Column A. Say your datalist is in A1 to C20. Enter this formula in D1, and copy down: =SUMPRODUCT(($A$1:$A$20=A1)*(C1<$C$1:$C$20))+1 -- HTH, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Marie Bayes" wrote in message ... Thanks for your response GS, however, this won't solve my problem. I cannot sort the data in the spreadsheet as I'm setting up a crystal report for a.n.other user from this datasheet and just 'pulling' the rank no into the report (so just the name of the top rank won't do), so there can be no sorting as the end user won't see the original data (the data is subject to change weekly and is just 'dumped' into the spreadsheet). I need to just pull in the ranked no. Therefore, back to my original question if that's ok, do you (or anyone) know of a way to use the ranking formula to do this? "Gary''s Student" wrote: Sort the three columns by region and score. The store with the highest score will appear first in the set of data for any region. Then just use VLOOKUP() to get the store name. -- Gary''s Student - gsnu200776 "Marie Bayes" wrote: Hi I have three columns, column A contains a 'region' name, column B, has the store name and column C has a 'score'. I want to rank the stores (B) within the region they're in (A) based on the score (C) and return the result into 1 column (D), is there a way to use the RANK formula so that I get each store's rank within their region returned into the one column (D)? Thanks in advance :) |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ranking different groups in one column
You're right, I had typed part of it incorrectly (apologies), however, if you
look at the sample results below you'll see that it's returning the first store, Colchester, which has 0% as ranked first along with the two 'true' rank 1 positions. Do you know why this would be (I've checked that the cell for Central & East is identical to those below (by copying from the one below it)? Central & East Colchester 0.0% 1 Central & East Coventry 1.6% 13 Central & East Birmingham 26.2% 12 Central & East Ipswich 26.2% 10 Central & East Norwich 34.5% 10 Central & East Peterborough 45.2% 9 Central & East Hemel Hempstead 59.5% 8 Central & East Stevenage 63.1% 7 Central & East Cambridge 71.4% 6 Central & East Braintree 94.0% 5 Central & East Milton Keynes 97.6% 4 Central & East Harlow 97.6% 1 Central & East Northampton 97.6% 1 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ranking different groups in one column
Upon examining your posted results, it's evident that your percentage column
is *not* displaying the actual value in the cell, but probably the "formatted" display value. Are there formulas in Column C, returning the percentage values you posted? Your last 3 entries display the exact same percents, but, as you can see, the rankings are not equal. This means the *actual, true* value in the cell, the value that XL uses for calculating, is being masked by formatting. If you try the formula on Gary's example, and just substitute a zero value for any value in the datalist, you'll see that a *true* zero will rank last. I would venture to say that your skewed results are being caused by the manner in which you're populating the percentage column. Perhaps you should try wrapping your percent formulas with the Round() function instead of just formatting the cells. BTW, a <space in a percent column cell will return a rank of 1. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Marie Bayes" wrote in message ... You're right, I had typed part of it incorrectly (apologies), however, if you look at the sample results below you'll see that it's returning the first store, Colchester, which has 0% as ranked first along with the two 'true' rank 1 positions. Do you know why this would be (I've checked that the cell for Central & East is identical to those below (by copying from the one below it)? Central & East Colchester 0.0% 1 Central & East Coventry 1.6% 13 Central & East Birmingham 26.2% 12 Central & East Ipswich 26.2% 10 Central & East Norwich 34.5% 10 Central & East Peterborough 45.2% 9 Central & East Hemel Hempstead 59.5% 8 Central & East Stevenage 63.1% 7 Central & East Cambridge 71.4% 6 Central & East Braintree 94.0% 5 Central & East Milton Keynes 97.6% 4 Central & East Harlow 97.6% 1 Central & East Northampton 97.6% 1 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ranking different groups in one column
Thanks for getting back and another apology, another mistake in the formula,
I'd had it checking row 1 when on row 2 (I forgot about my header row) etc etc, I've fixed this and now the zero value is showing in the right place, thanks once again and sorry to have made such a stupid mistake! |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ranking different groups in one column
Glad you got it all working correctly.
And thank you for feeding back. -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Marie Bayes" wrote in message ... Thanks for getting back and another apology, another mistake in the formula, I'd had it checking row 1 when on row 2 (I forgot about my header row) etc etc, I've fixed this and now the zero value is showing in the right place, thanks once again and sorry to have made such a stupid mistake! |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ranking different groups in one column
Great tip Tam.
Anyway of breaking the ties? "Ragdyer" wrote: If I understand your question, the actual name of the store is incidental. You're looking for the rank within the regions in Column A. Say your datalist is in A1 to C20. Enter this formula in D1, and copy down: =SUMPRODUCT(($A$1:$A$20=A1)*(C1<$C$1:$C$20))+1 -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Marie Bayes" wrote in message ... Thanks for your response GS, however, this won't solve my problem. I cannot sort the data in the spreadsheet as I'm setting up a crystal report for a.n.other user from this datasheet and just 'pulling' the rank no into the report (so just the name of the top rank won't do), so there can be no sorting as the end user won't see the original data (the data is subject to change weekly and is just 'dumped' into the spreadsheet). I need to just pull in the ranked no. Therefore, back to my original question if that's ok, do you (or anyone) know of a way to use the ranking formula to do this? "Gary''s Student" wrote: Sort the three columns by region and score. The store with the highest score will appear first in the set of data for any region. Then just use VLOOKUP() to get the store name. -- Gary''s Student - gsnu200776 "Marie Bayes" wrote: Hi I have three columns, column A contains a 'region' name, column B, has the store name and column C has a 'score'. I want to rank the stores (B) within the region they're in (A) based on the score (C) and return the result into 1 column (D), is there a way to use the RANK formula so that I get each store's rank within their region returned into the one column (D)? Thanks in advance :) |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Ranking different groups in one column
Team,
Thank you for the ranking different groups within one colmun formula: ={SUMPRODUCT(($A$1:$A$6000=$A7)*($B1<$B$1:$B$6000) )+1} array formula <control-shift-enter To break deadlocks I set up an adjacent column C & use this formula that I found elsewhere on this forum: =B1-ROW()/10^10 which gives every value in column B a unique value then the ranking column becomes {=SUMPRODUCT(($A$1:$A$6000=$A7)*($C1<$C$1:$C$6000) )+1} the problem I'm now having is that it only works up to ~4,000 rows and I need to go to 10,000 and the Rank formula then returns #N/A It seems to be very temperamental. Any tips would be welcome. ta Ditch "Marie Bayes" wrote: Thanks for your response, it is complicated, it looks like it'll work, but I'm just waiting for, as you put it, a more compact solution (if one exists), if that's not forthcoming then I will take you up on this one!! Thanks for all the effort with this one though. "Gary''s Student" wrote: I have a solution for you, but its not very clean. Here is some sample data: reg3 store25 423 reg2 store15 477 reg2 store18 106 reg1 store2 456 reg2 store19 462 reg1 store1 104 reg3 store26 474 reg3 store28 27 reg1 store10 361 reg1 store7 36 reg1 store8 88 reg2 store11 22 reg2 store16 35 reg2 store20 25 reg3 store22 378 reg2 store14 390 reg3 store30 8 reg3 store29 17 reg1 store3 402 reg2 store17 15 reg1 store6 59 reg1 store5 245 reg3 store23 21 reg1 store9 341 reg3 store27 439 reg2 store12 125 reg2 store13 118 reg1 store4 12 reg3 store21 33 reg3 store24 4 As you see, three regions, a bunch of store names and scores. The first step is to find the highest score for a given region. In C1 enter the array formula: =MAX(IF(A1:A30="reg1",C1:C30)) This must be entered with a CNTRL-SHFT-ENTER rather than just the ENTER key. C1 displays 456 At this point we can try MATCH & INDEX, but the 456 may appear several times, so we must find the 456 for reg1 only. In E1 we enter: =SUMPRODUCT(--(A1:A30="reg1"),--(C1:C30=D1),(ROW(1:30))) This displays 4. So we want the fourth row. Finally in F1 we enter: =INDEX(B:B,E1) which displays: store2 You can use this until some gives you a more compact solution. -- Gary''s Student - gsnu200776 "Marie Bayes" wrote: Thanks for your response GS, however, this won't solve my problem. I cannot sort the data in the spreadsheet as I'm setting up a crystal report for a.n.other user from this datasheet and just 'pulling' the rank no into the report (so just the name of the top rank won't do), so there can be no sorting as the end user won't see the original data (the data is subject to change weekly and is just 'dumped' into the spreadsheet). I need to just pull in the ranked no. Therefore, back to my original question if that's ok, do you (or anyone) know of a way to use the ranking formula to do this? "Gary''s Student" wrote: Sort the three columns by region and score. The store with the highest score will appear first in the set of data for any region. Then just use VLOOKUP() to get the store name. -- Gary''s Student - gsnu200776 "Marie Bayes" wrote: Hi I have three columns, column A contains a 'region' name, column B, has the store name and column C has a 'score'. I want to rank the stores (B) within the region they're in (A) based on the score (C) and return the result into 1 column (D), is there a way to use the RANK formula so that I get each store's rank within their region returned into the one column (D)? Thanks in advance :) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validating Values Within Sets of Groups in a Column | Excel Discussion (Misc queries) | |||
Dynamic stacked column chart with ranking | Charts and Charting in Excel | |||
Ranking System For Large Groups | Excel Discussion (Misc queries) | |||
Ranking a List which must be within GROUPS | Excel Discussion (Misc queries) | |||
redistribute a column by a groups of rows | Excel Worksheet Functions |