Tiebreaker in a Index formula?
Ok I got the formula that Biff gave to work now. I just changed it to
=RANK(Y3,Y$3:Y$21)+COUNTIF(Y$3:Y3,Y3)-1. It did change the numbers to
percentages, why I don't know but I just changed the format from percentages
to general. Now I am still trying to figure out the tiebreaker formula. I
sure it will work but I can't seem to figure out what the parts in this
formula are referring to =INDEX(A$2:A$10,MATCH(ROWS(A$1:A1),C$2:C$10,0). What
is A$2:A$10 referring to? Stores, Scores or the Ranking? What is A$1:A1
referring to? The row with the Stores, scores or ranking? What does C$2:C$10
referring to? The Ranking?
"YS1107" wrote:
Thanks Luke and Biff, I got the stores to rank with the formula that Luke
gave me =RANK(B2,B$2:B$10). I put the formula in cells AA3:AA21 which now
reads =RANK(Y3,Y$3:Y$21) and so on, and this ranked the stores based on their
score, which the scores are in column Y3:Y21. The formula that Biff gave
me,=RANK(B2,B$2:B$10)+COUNTIF(B$2:B2,B2)-1,
put the store scores into percentages, is that correct? But the tiebreaker
formula I am still struggling with. The stores are listed in Row A3:A21 and
the store scores are listed in Rows Y3:Y21. But when there is a tie thats
when I get the problem. I know this is confusing, it is confusing to me too
and I am looking at the spreadsheet, so I will try to show how it is laid out
he
Cells B28:B32 there is no fomula but I have the numbers 1,2,3,4,5
Cells C28:C32 formula is =INDEX(A$3:A$21,MATCH(D28,Y$3:Y$21,0))
Cells D28:D32 formula is =LARGE($Y$3:$Y$21,1)
Cells AA3:AA21 formula is =RANK(Y3,Y$3:Y$21)
Cells A3:A21 Cells Y3:Y21 Cells AA3:AA21
Store Score Ranking
123 80% 5
234 87% 4
456 98% 2
567 100% 1
678 92% 3
789 79% 6
Cells C28:C32 Cells D28:D32
Store Score
567 100%
456 98%
678 92%
234 80%
I have tried to understand how formulas work and I have tried varying the
formula to match the cell numbers I need, with the formulas given to me and I
still dont get the results I am seeking. I either a circular reference or
the #N/A in the cell. I need a formula that goes into columns C28:C32 that
will fix the tiebreaker issue of listing one store twice. I hope this makes
sense. I really appreciate all the time you guys have spent on this!! I
apologize for my lack of understanding, but I suppose that is why I am here.
"T. Valko" wrote:
=RANK(B2,B$2:B$10)
=INDEX(A$2:A$10,MATCH(ROW(A1),C:C,0)
I think you'll get better results with these formulas:
=RANK(B2,B$2:B$10)+COUNTIF(B$2:B2,B2)-1
=INDEX(A$2:A$10,MATCH(ROWS(A$1:A1),C$2:C$10,0)
--
Biff
Microsoft Excel MVP
"Luke M" wrote in message
...
Next to your stores' scores, use a helper column (I'll assume C) with this
formula:
=RANK(B2,B$2:B$10)
Then, to get the stores in descending order, you can do:
=INDEX(A$2:A$10,MATCH(ROW(A1),C:C,0)
--
Best Regards,
Luke M
"YS1107" wrote in message
...
I need a tiebreaker for a formula. Right now the formula reads:
=INDEX(A$3:A$21,MATCH(D28,Y$3:Y$21,0)) which works real well But when
there
is a tie it shows on one store twice instead of listing the two separate
stores. I have two stores have the exact scores and it list the store
with
the lower store number twice. For example I have store 598 and store 698
both
with a score of 100% but in the ranking of the stores it shows store 598
twice instead of 598 then 698. Is it possible to have a tiebreaker, with
the
index formula, that can list the stores in descending order, 598 first
and
698 second, or do I need another formula in order for the tiebreaker to
work?
Here is what it looks like now:
C28 D28
598 100%
598 100%
I need it to look like this
C28 D28
598 100%
698 100%
If this is not possible I can live without the tiebreaker, I suppose. :)
Thanks
.
|