Formulas for Trivia Scoring

[kizzie37]

This might be complex to explain, formulas are required to auto score results from a trivia game. Hoping this is possible but it would be a complex formula

5 captains, each captain Provides a "correct" answer to the trivia question (right side of image "C" ). The "True" answer is noted in the TRUE column with a "T"
Then each Team/player guesses the answer to the trivia question and its logged with an "G" (left side of image)
Additionally each player chooses an answer that might not be "correct" but would be the "favorite" of "funniest" and its marked with an "F" (also left side of image)

Auto scoring as follows:

Example if Sherry guessed the TRUE answer "C" (left side) she gets 10 points, but because Bob had also chosen C she gets another 5 points, in addition its voted as the BEST answer (B) so another 2 points are added for a total of 17 to Sherry.

So a multi formula is needed to bring all these variables together to score based on the letters in the various columns and rows.

Can anyone assist

 

[dreid1011]

There seem to be some inconsistencies between your description and the image. (or maybe just your choice of words)

1: There are 5 players, but 6 answer choices? Is it the correct answer, then 1 "wrong" answer provided by each captain? So always 1 more choice than number of players/teams?
2: Assuming each player can only guess one answer (G) and vote one answer for best (B), why does Sherry have a "G or B" for Grapes?
3: None of the answers have more than 1 vote for best (except grapes if the "G or B" counts?) so how does jello get counted for best?
4: Bob guessed liver, so how does that give Sherry more points for picking jello? Because he voted jello for best?

 

[kizzie37]

There seem to be some inconsistencies between your description and the image. (or maybe just your choice of words)

1: There are 5 players, but 6 answer choices? Is it the correct answer, then 1 "wrong" answer provided by each captain? So always 1 more choice than number of players/teams? - Yes, Captains are guessing what the answer could be, thats part 1
2: Assuming each player can only guess one answer (G) and vote one answer for best (B), why does Sherry have a "G or B" for Grapes? G or B was just for my head (guess or Best) you can ignore that box
3: None of the answers have more than 1 vote for best (except grapes if the "G or B" counts?) so how does jello get counted for best? Sheri gets the 2 point "Best" because Dave chose it as "Best"
4: Bob guessed liver, so how does that give Sherry more points for picking jello? Because he voted jello for best? Becasue Jello was the correct answer

Another example of Scoring : Colin would get 7 points, 5 because Bob chose his answer "liver" and 2 because Sid named it "Best". I hope that makes sense.

It is complicated I've come up with nested "ifs" cut I need other variables

 

[dreid1011]

Okay, I think it's clearer now, but one more question about someone else choosing your answer:

You said:

but because Bob had also chosen C she gets another 5 points

Bob didn't choose C, he voted it best, so that would be 2 points.

Shouldn't it be because Colin chose her answer (B: Grapes)?

 

[kizzie37]

Okay, I think it's clearer now, but one more question about someone else choosing your answer:

You said:

Bob didn't choose C, he voted it best, so that would be 2 points.

Shouldn't it be because Colin chose her answer (B: Grapes)?

HI, yes you are correct. I confused myself. Now you can prob see why I need an automated scoring system. Its going to be a "virtual" event and I have to score in real time. The people names represent teams, all in all 300 people so i wont have time to figure it all out manually.

 

[dreid1011]

Okay, another clarification I need. For the best answer, does that apply to the correct answer or each captains answer?

Sherry guessed the correct answer of C: Jello. Bob voted best for C: Jello. Sherry gets 2 points for ANYONE voting best answer on the correct answer because she guessed it?
Does anyone else get 2 points for best answer if their answers were voted best even though they were not the correct answer?

Can anyone guess and vote for the same answer?

 

[kizzie37]

Okay, another clarification I need. For the best answer, does that apply to the correct answer or each captains answer?

Sherry guessed the correct answer of C: Jello. Bob voted best for C: Jello. Sherry gets 2 points for ANYONE voting best answer on the correct answer because she guessed it?
Does anyone else get 2 points for best answer if their answers were voted best even though they were not the correct answer?

Can anyone guess and vote for the same answer?

Best Answer applies if anyone votes one of the captains answers as "Best". So bob gets 2 points because Colin voted his answer best, Dave gets 2 points because Sherry voted his answer best. Colin gets 2 points because Sid voted his answer best.

However is Colin had B or G for his own answer he would lose 2 points (for a self vote). I hope thats clearer

 

[dreid1011]

Best Answer applies if anyone votes one of the captains answers as "Best". So bob gets 2 points because Colin voted his answer best, Dave gets 2 points because Sherry voted his answer best. Colin gets 2 points because Sid voted his answer best.

However is Colin had B or G for his own answer he would lose 2 points (for a self vote). I hope thats clearer

Okay, and can someone guess and vote the same answer?

 

[dreid1011]

I suppose I should ask this now also, what is the maximum amount of teams you can have?

 

[kizzie37]

Okay, and can someone guess and vote the same answer?

Yes