Formula Help

[dylancock]

Hey guys,
I just need another formula. I'll do my best to explain using the attached image. I want the J column named "Result" on the first table to be linked to the U column named "Result" on the second table. I want to create the value for the J column using the second table's U column. I also want this to coincide the Reference columns "C" on the first table, and "N" on the second table. I'll use the example using the Reference 1. I want the value on the first tables Result column to be made up of the results on the second tables result columns. So reference 1 on the first table would be linked to the three reference 1s on the second table. If the result on the three second table results read "win, win, win", the result on the first table would be win. If the result on the three second table results read win win loss, the result on the first table would be loss. If any of the bets read loss, meaning if two of them did, or all three did, I want the first table to be loss. However, if any of the three results on the second table read void, the first table should say void, no matter if the others are win or loss. This is the best explanation I can give, so if I need to clarify anything, let me know!

dodgy book.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2	Date	Game	Reference	Variables	Chance	Confidence	Confidence Chance	Advantage	Number Value	Result	Profit/Loss	Overall		Reference	Player	Outcome	Chance	Confidence	Confidence Odds	Advantage	Result
3	27-May		1	3	3.25	0.4165	2.4009604	35.36%			#N/A	#N/A		1	Variable 1	To succeed	1.19	0.85	1.18		win
4			2	3	3	0.3	3.3333333	-10.00%			#N/A	#N/A		1	Variable 2	To succeed	1.99	0.7	1.43		win
5			3	3	3	0.343	2.9154519	2.90%			#N/A	#N/A		1	Variable 3	To succeed	1.35	0.7	1.43		loss
6			4	1	4	1	1	300.00%			#N/A	#N/A		2	Variable 4	To succeed	2.7	0.4	2.50		win
7						0	#DIV/0!	#DIV/0!			#N/A	#N/A		2	Variable 5	To succeed	1.25	0.75	1.33		win
8						0	#DIV/0!	#DIV/0!			#N/A	#N/A		3	Variable 6	To succeed	1.54	0.7	1.43		win
9						0	#DIV/0!	#DIV/0!			#N/A	#N/A		3	Variable 7	To succeed	1.61	0.7	1.43		void
10						0	#DIV/0!	#DIV/0!			#N/A	#N/A		3	Variable 8	To succeed	1.23	0.7	1.43		loss
11						0	#DIV/0!	#DIV/0!			#N/A	#N/A							#DIV/0!
12						0	#DIV/0!	#DIV/0!			#N/A	#N/A							#DIV/0!
13						0	#DIV/0!	#DIV/0!			#N/A	#N/A							#DIV/0!
Sheet1

Cell Formulas
Range	Formula
F3:F13	F3	=PRODUCT(IF(Table2[Reference]=[@Reference],Table2[Confidence],1))
G3:G13	G3	=1/([@Confidence])
H3:H13	H3	=([@Chance]-[@[Confidence Chance]])/[@[Confidence Chance]]
K3:K13	K3	=IFS(J3="win",[@[Number Value]]*[@Chance]-[@[Number Value]],[@Result]="loss",0-[@[Number Value]],[@Result]="void","0")
L3:L13	L3	=SUM($K$3:K3)
S3:S13	S3	=1/[@Confidence]

 

[bebo021999]

try in J3:

=IF(COUNTIF($N$3:$N$13,C3)=0,"",IF(COUNTIFS($N$3:$N$13,C3,$U$3:$U$13,"void"),"void",IF(COUNTIFS($N$3:$N$13,C3,$U$3:$U$13,"loss"),"loss","win")))

 

[dylancock]

Thanks mate, is there a way to just make the $N$3:$N$13 bit the whole column?

 

[Fluff]

Rather than using the entire column, you can use the table reference like

=IF(COUNTIF(Table2[Reference],[@Reference])=0,"",IF(COUNTIFS(Table2[Reference],[@Reference],Table2[Result],"void"),"void",IF(COUNTIFS(Table2[Reference],[@Reference],Table2[Result],"loss"),"loss","win")))

 

[Peter_SSs]

You could also make use of the LET function & some names.

=LET(ref,Table2[Reference],res,Table2[Result],IF(COUNTIF(ref,[@Reference]),IF(COUNTIFS(ref,[@Reference],res,"void"),"void",IF(COUNTIFS(ref,[@Reference],res,"loss"),"loss","win")),""))

 

[dylancock]

Rather than using the entire column, you can use the table reference like

=IF(COUNTIF(Table2[Reference],[@Reference])=0,"",IF(COUNTIFS(Table2[Reference],[@Reference],Table2[Result],"void"),"void",IF(COUNTIFS(Table2[Reference],[@Reference],Table2[Result],"loss"),"loss","win")))

says there is a problem with the formula and I'm too silly to figure it out

 

[Fluff]

What problem?

 

[Peter_SSs]

As I understand the problem, @Fluff's formula worked for me. My formula in column J below and Fluff's in column K

dylancock.xlsm
	C							J	K			N	O						U	V
1
2	Reference							Result	Result2				Reference						Advantage	Result
3	1							loss	loss				1							win
4	2							win	win				1							win
5	3							void	void				1							loss
6	4												2							win
7													2							win
8													3							win
9													3							void
10													3							loss
11
12
13
Sheet1

Cell Formulas
Range	Formula
J3:J13	J3	=LET(ref,Table2[Reference],res,Table2[Result],IF(COUNTIF(ref,[@Reference]),IF(COUNTIFS(ref,[@Reference],res,"void"),"void",IF(COUNTIFS(ref,[@Reference],res,"loss"),"loss","win")),""))
K3:K13	K3	=IF(COUNTIF(Table2[Reference],[@Reference])=0,"",IF(COUNTIFS(Table2[Reference],[@Reference],Table2[Result],"void"),"void",IF(COUNTIFS(Table2[Reference],[@Reference],Table2[Result],"loss"),"loss","win")))

 

[dylancock]

It was my mistake, I renamed the table so it didn't work thank you guys for all your help!

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.