Okay, my computer just crashed so I had to adjust some things. Also my
friend
said I need more quizzers on my sheet so I had to adjust some more things.
Here is another in-depth description of what I have so far.
A AA
12 Quizzer #1 110 [=SUM(B12:Z12)]
13 Quizzer #2 100 [=SUM(B13:Z13)]
14 Quizzer #3 90 [=SUM(B14:Z14)]
15 Quizzer #4 80 [=SUM(B15:Z15)]
16 Quizzer #5 70 [=SUM(B16:Z16)]
There is other text in A17:A25. There is the word 'Total' in A25
26 Quizzer #6 60 [=SUM(B26:Z26)]
27 Quizzer #7 50 [=SUM(B27:Z27)]
28 Quizzer #8 40 [=SUM(B28:Z28)]
29 Quizzer #9 30 [=SUM(B29:Z29)]
30 Quizzer #10 20 [=SUM(B30:Z30)]
31 Quizzer #11 10 [=SUM(B31:Z31)]
Text in A33. Otherwise none.
A B C
43 1st high scorer 110 [=MAX(AA12:AA16,AA26:AA31)]
44 2nd high scorer 100 [=LARGE((AA12:AA16,AA26:AA31),2)]
45 3rd high scorer 90 [=LARGE((AA12:AA16,AA26:AA31),3)]
46 4th high scorer 80 [=LARGE((AA12:AA16,AA26:AA31),4)]
47 5th high scorer 70 [=LARGE((AA12:AA16,AA26:AA31),5)]
48 6th high scorer 60 [=LARGE((AA12:AA16,AA26:AA31),6)]
49 7th high scorer 50 [=LARGE((AA12:AA16,AA26:AA31),7)]
50 8th high scorer 40 [=LARGE((AA12:AA16,AA26:AA31),8)]
So right now my problem is to get B43:B50 to work. I need B43 to be the
name
of the high scorer (Quizzer#1) and B44 to be name 2nd high scorer (Quizzer
#2) and so on. Can anybody help me or is this one too tricky?
Sandy Mann said:
Any tied scores will be listed in list order.
Well my excuse is that I was being hassled by my wife to take her out
shopping. Shopping indeed!
No they won't! To get the tied scores in list order you will have to
SUBTRACT the ROW()/100000 not add it
=AA12-ROW()/100000 and copy down to AB28
--
HTH
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
I assume that it may be possible for two Quizzers to have the same score
so
you will have to ensure that no two score are exactly the same. The
easiest way of doing that is to enter in AB12:AB28, (or some other
column
if you are already using that one), =AA12+ROW()/100000 and copy down to
AB28 then hide Column AB
B42 enter the formula:
=INDEX($A$12:$A$28,MATCH(LARGE($AB$12:$AB$28,ROW()-11),$AB$12:$AB$28,0))
Any tied scores will be listed in list order.
The results will list the Quizzers in nlist order untill enries are
made
in AA12:AA28. If you want them to be blank untill all scores are
entered
the enclose the formula in an IF() statement:
=IF(COUNT($AA$12:$AA$28)<>17,"",INDEX($A$12:$A$28,MATCH(LARGE($AB$12:$AB$28,ROW()-11),$AB$12:$AB$28,0)))
--
HTH
Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings
(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
message Thank you so much for your help!
But now I have yet another problem that has similar results.
I need the individual results too. So right now I have.
In A12 "Quizzer #1" and the results (points) of that quizzer in AA12.
In A13 "Quizzer #2" and the results of that quizzer in AA13.
In A14 "Quizzer #3" and the results of that quizzer in AA14
In A24 "Quizzer #4" and the results of that quizzer in AA24
In A25 "Quizzer #5" and the results of that quizzer in AA25
In A26 "Quizzer #6" and the results of that quizzer in AA26
In A 27 "Quizzer #7, and the results of that quizzer in AA27
In A28 "Quizzer #8" and the results of that quizzer in AA28
Results
B42
B43
B44
B45
B46
B47
B48
B49
In C42 I have the following formula
=MAX(AA12:AB14,AA24:AB28)
In C43 I have
=LARGE((AA12:AB14,AA24:AB28),2)
In C44 I have
=LARGE((AA12:AB14,AA24:AB28),3)
And so on and so on. But I need the quizzer's name (in text) with the
highest number of points to be displayed in B42. In other words.
Results
B C
42 Quizzer #4 145 points
43 Quizzer #2 70 points
44 Quizzer #7 55 points
And so on.
In other words, I have the C column figured out but I just need help
with
the B column. Can anybody help me?
