View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.misc
YS1107 YS1107 is offline
external usenet poster
 
Posts: 16
Default 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





.