# Thread: Automating a score sheet in Excel (Excel 2003 SP2)

1. ## Automating a score sheet in Excel (Excel 2003 SP2)

Hi Guys! Wanted to ask if someone has an idea how to automate my quiz score sheet. I've attached the score sheet file for reference. My problem is this: The quiz consists of 10 regular rounds of questions R1 to R10. Before each of these rounds, teams are asked if they would like to play a joker which will double whatever score they achieve for that round. The joker may only be played before one of the regular rounds. What I would like to accomplish is to somehow automate the doubling of the score that is manually entered for the team when the joker is played. Currently a separate column is available at the far right of the score sheet in which we manually enter the round in which the joker was played as a reference so that we can then double the score for that team when it is entered for that round. Normally teams wait until just before the round to play the joker, but it is possible that several jokers are played in advance so that we need to manually watch for the point when the score needs doubling. It would be possible to get all the jokers played before the quiz starts so that it is known which round would need the score doubling if this would help with the creation of a solution.
I'd be very grateful for any help you guys could provide in programming a solution to this "problem". Look forward to hearing from you !! Cheers, Davy.

2. ## The Following User Says Thank You to Davyboy For This Useful Post:

harrybl (2013-08-11)

3. ## Re: Automating a score sheet in Excel (Excel 2003 SP2)

Does "Famous Person" belong to any round? There is nothing above it.

4. ## Re: Automating a score sheet in Excel (Excel 2003 SP2)

Hi Hans, The Mystery Person round is an additional round, as id the Table Round towards the right of the sheet in both of which the joker can't be played. The joker can only be played in one of the rounds labeled R1, R2........ R. Cheers, David

5. ## Re: Automating a score sheet in Excel (Excel 2003 SP2)

See the attached workbook. I used SUMPRODUCT to test whether the joker has been played in a specific round. For example in E7:
<code>
=IF(ISBLANK(\$C7),"",SUMPRODUCT(C77,1+(C\$6\$6=\$V7)))
</code>
This formula has been filled down.

6. ## Re: Automating a score sheet in Excel (Excel 2003 SP2)

Hi Hans, Thanks for this very elegant solution. How exactly does it work? Why do I need to type R7 and not 7 into the cell stating when the joker was played?
Was also wondering if it would be possible to find a solution that would actually double the score (on the fly perhaps) entered into the actual cell of the round in which the joker was played so that the team would observe say, 20 if they had scored a perfect 10 (which would be the number actually entered) ? Was also wondering if it would be possible to highlight the actual cell of the round in which the joker was played containing the score for that round so that the team could visually identify the score (even if it is not possible to show it doubled) where they played the joker? (In your example where the score of 10 is shown, cell L7 would be highlighted in some colour for example). Many thanks for your help !

7. ## Re: Automating a score sheet in Excel (Excel 2003 SP2)

Let's take a look at the formula
<code>
=IF(ISBLANK(\$C7),"",SUMPRODUCT(C77,1+(C\$6\$6=\$V7)))
</code>
The expression C\$6\$6=\$V7 returns an array of TRUE/FALSE values, one for each cell in C66. Since TRUE = 1 and FALSE = 0 in Excel, 1+(C\$6\$6=\$V7) results in an array consisting of 1s and 2s - 2 if a column is the one for which the joker has been played. SUMPRODUCT multiplies the scores in C77 with these 1s and 2s and sums the products.

Since you indicated the rounds with R1, R2 etc., I assumed that you'd enter them that way in the Joker column too. However, it's easy to modify the formulas to work with 1, 2, etc.:
<code>
=IF(ISBLANK(\$C7),"",SUMPRODUCT(C77,1+(C\$6\$6="R"&\$V7)))
</code>
and similar for the other formulas.

It would be possible to use the Worksheet_Change event to double the score in the appropriate column, but it would be tricky, and it could easily lead to mistakes and confusion, so I wouldn't recommend it.

You can use conditional formatting to highlight a cell whose score is doubled.
Select C722, F7:G22, K7:L22, N7:O22 and Q7:R22.
Select Format | Conditional Formatting...
Select Formula Is in the dropdown.
Enter a formula in the box next to it that works for the currently active cell (which cell that is, depends on how you selected the ranges). For example, if C7 is the active cell, the formula is
<code>
=AND(NOT(ISBLANK(\$C7));C\$6="R"&\$V7)
</code>
Click Format... to specify the formatting you like.

See the attached modified example.

8. ## Re: Automating a score sheet in Excel (Excel 2003 SP2)

Hi Hans, This is great! Solves the problem really well. I've managed to get the sheet working just the way I'd like it to now. It just remains to put it to the test in an actual quiz! Many thanks for your help in creating such an elegant solution. Cheers, Davy.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•