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 :)
|