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]

Okay, here are a couple scenarios, let me know if the points add up as you expect:

Book1 5-31-2024.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2			Answer Given							Answer Created
3	Colin	Bob	Sid	Dave	Sherry		Question	Colin	Bob	Sid	Dave	Sherry	TRUE
4				G		A	Mud			C
5	G			B		B	Grapes					C
6		B			G	C	Jello						T
7	B					D	Yogurt		C
8			G		B	E	Marshmellows				C
9		G	B			F	Liver	C
10
11							Total	7	2	5	7	17
Sheet1

Cell Formulas
Range	Formula
H11:L11	H11	=LET(ae,$A$4:$E$9,hl,$H$4:$L$9,mg,MAP(ae,LAMBDA(a,IF(ISNUMBER(SEARCH("G",a)),1,0))),BYCOL(VSTACK(mg*MAP($M$4:$M$9,LAMBDA(a,IF(a="T",10,0))),MAP(hl,LAMBDA(b,IF(b="C",1,0)))*BYROW(MAP(ae,LAMBDA(a,IF(ISNUMBER(SEARCH("G",a)),5,0))),LAMBDA(row,SUM(row)))+mg*MAP(hl,LAMBDA(b,IF(b="C",-7,0))),MAP(hl,LAMBDA(b,IF(b="C",1,0)))*BYROW(MAP(ae,LAMBDA(a,IF(ISNUMBER(SEARCH("B",a)),2,0))),LAMBDA(row,SUM(row)))+MAP(ae,LAMBDA(a,IF(ISNUMBER(SEARCH("B",a)),1,0)))*MAP(hl,LAMBDA(b,IF(b="C",-4,0)))),LAMBDA(col,SUM(col))))
Dynamic array formulas.

Book1 5-31-2024.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
13			Answer Given							Answer Created
14	Colin	Bob	Sid	Dave	Sherry		Question	Colin	Bob	Sid	Dave	Sherry	TRUE
15				GB		A	Mud			C
16	G				BG	B	Grapes					C
17		B				C	Jello						T
18	B	G				D	Yogurt		C
19			G			E	Marshmellows				C
20			B			F	Liver	C
21
22							Total	2	0	7	5	1
Sheet1

Cell Formulas
Range	Formula
H22:L22	H22	=LET(ae,$A$15:$E$20,hl,$H$15:$L$20,mg,MAP(ae,LAMBDA(a,IF(ISNUMBER(SEARCH("G",a)),1,0))),BYCOL(VSTACK(mg*MAP($M$15:$M$20,LAMBDA(a,IF(a="T",10,0))),MAP(hl,LAMBDA(b,IF(b="C",1,0)))*BYROW(MAP(ae,LAMBDA(a,IF(ISNUMBER(SEARCH("G",a)),5,0))),LAMBDA(row,SUM(row)))+mg*MAP(hl,LAMBDA(b,IF(b="C",-7,0))),MAP(hl,LAMBDA(b,IF(b="C",1,0)))*BYROW(MAP(ae,LAMBDA(a,IF(ISNUMBER(SEARCH("B",a)),2,0))),LAMBDA(row,SUM(row)))+MAP(ae,LAMBDA(a,IF(ISNUMBER(SEARCH("B",a)),1,0)))*MAP(hl,LAMBDA(b,IF(b="C",-4,0)))),LAMBDA(col,SUM(col))))
Dynamic array formulas.

 

[kizzie37]

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

5

 

[kizzie37]

Okay, here are a couple scenarios, let me know if the points add up as you expect:

Book1 5-31-2024.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1
2			Answer Given							Answer Created
3	Colin	Bob	Sid	Dave	Sherry		Question	Colin	Bob	Sid	Dave	Sherry	TRUE
4				G		A	Mud			C
5	G			B		B	Grapes					C
6		B			G	C	Jello						T
7	B					D	Yogurt		C
8			G		B	E	Marshmellows				C
9		G	B			F	Liver	C
10
11							Total	7	2	5	7	17
Sheet1

Cell Formulas
Range	Formula
H11:L11	H11	=LET(ae,$A$4:$E$9,hl,$H$4:$L$9,mg,MAP(ae,LAMBDA(a,IF(ISNUMBER(SEARCH("G",a)),1,0))),BYCOL(VSTACK(mg*MAP($M$4:$M$9,LAMBDA(a,IF(a="T",10,0))),MAP(hl,LAMBDA(b,IF(b="C",1,0)))*BYROW(MAP(ae,LAMBDA(a,IF(ISNUMBER(SEARCH("G",a)),5,0))),LAMBDA(row,SUM(row)))+mg*MAP(hl,LAMBDA(b,IF(b="C",-7,0))),MAP(hl,LAMBDA(b,IF(b="C",1,0)))*BYROW(MAP(ae,LAMBDA(a,IF(ISNUMBER(SEARCH("B",a)),2,0))),LAMBDA(row,SUM(row)))+MAP(ae,LAMBDA(a,IF(ISNUMBER(SEARCH("B",a)),1,0)))*MAP(hl,LAMBDA(b,IF(b="C",-4,0)))),LAMBDA(col,SUM(col))))
Dynamic array formulas.

Book1 5-31-2024.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
13			Answer Given							Answer Created
14	Colin	Bob	Sid	Dave	Sherry		Question	Colin	Bob	Sid	Dave	Sherry	TRUE
15				GB		A	Mud			C
16	G				BG	B	Grapes					C
17		B				C	Jello						T
18	B	G				D	Yogurt		C
19			G			E	Marshmellows				C
20			B			F	Liver	C
21
22							Total	2	0	7	5	1
Sheet1

Cell Formulas
Range	Formula
H22:L22	H22	=LET(ae,$A$15:$E$20,hl,$H$15:$L$20,mg,MAP(ae,LAMBDA(a,IF(ISNUMBER(SEARCH("G",a)),1,0))),BYCOL(VSTACK(mg*MAP($M$15:$M$20,LAMBDA(a,IF(a="T",10,0))),MAP(hl,LAMBDA(b,IF(b="C",1,0)))*BYROW(MAP(ae,LAMBDA(a,IF(ISNUMBER(SEARCH("G",a)),5,0))),LAMBDA(row,SUM(row)))+mg*MAP(hl,LAMBDA(b,IF(b="C",-7,0))),MAP(hl,LAMBDA(b,IF(b="C",1,0)))*BYROW(MAP(ae,LAMBDA(a,IF(ISNUMBER(SEARCH("B",a)),2,0))),LAMBDA(row,SUM(row)))+MAP(ae,LAMBDA(a,IF(ISNUMBER(SEARCH("B",a)),1,0)))*MAP(hl,LAMBDA(b,IF(b="C",-4,0)))),LAMBDA(col,SUM(col))))
Dynamic array formulas.

wowsers!! this seems to so the trick perfectly Thanks so much

 

[dreid1011]

wowsers!! this seems to so the trick perfectly Thanks so much

You're welcome. It was a fun one to work on, now, if there is a more efficient method to do it, I don't know, but I am sure something better is out there.

 

[smozgur]

wowsers!! this seems to so the trick perfectly Thanks so much

The marked solution has been changed accordingly. In your future questions, please mark the post as the solution that actually answered your question, instead of your feedback message as it will help future readers. No further action is required for this thread.