Need help combining IndexMatch, Concatenate, & Indirect

[accountfornada]

Hi, so basically I have an excel assessment as a starting point for a job interview, and I'm trying to pull data from a sheet called "stats for test" to figure out many things. In the Unit 1 & Unit 2 are dropdown menus that can change the level from 1-60, rarity from A-C, and Class between Pistol, Grenade, and Bazooka. As the values in the units change the results below are supposed to change as well. As stated in the blue box, the goal is to use all functions I've learned up to this point - Meaning Index Match, Concatenate, and Indirect. Any help at all is truly appreciated. Thank you.

Design Assessment.xlsx
	B	C	D	E	F	G
3	Using all that you have learned, create a Kill/Death model with hits to kill for two units fighting each other using the following combat model parameters: • Pistol does 50% bonus damage against Grenade • Grenade does 50% bonus damage against Bazoooka • Bazooke does 50% bonus damage against Pistol • Crit rate is the unit's CRT rating divided by the function of the highest CRT rating in the game times 4 • If a unit crits, their CRD is added to their attack damage • Damage dealt is a simple attack - defense calculation The goal is to use all the functions you have learned as well as using spreadsheet deisgn best practices. It will be helpful to create a parameters table for the combat model. The three input fields (Winner, Hits to Kill, and Winner health remaining) will require a long formula comprising of multiple parts. A tip is to construct the formula in parts, and then assemble the parts into the final formula.
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19	Tutorial #3: Using concatenate with indirect
20
21	Unit 1				Unit 2
22	Level	50			Level	27
23	Rarity	C			Rarity	A
24	Class:	Bazooka			Class:	Bazooka
25
26
27	Winner:
28	Hits to kill:
29	Winner health remaining:
30
Test

Cells with Data Validation
Cell	Allow	Criteria
C22	List	1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60
C23	List	A,B,C
C24	List	Pistol,Grenade,Bazooka
G22	List	1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49,50,51,52,53,54,55,56,57,58,59,60
G23	List	A,B,C
G24	List	Pistol,Grenade,Bazooka

Design Assessment.xlsx
	A	B	C	D	E	F	G	H
1	level	rarity	class	attack	defense	health	crt	crd
2	1	C	Pistol	100	100	100	100	100
3	2	C	Pistol	109	106	104	109	104
4	3	C	Pistol	119	113	108	119	108
5	4	C	Pistol	128	119	113	128	113
6	5	C	Pistol	137	126	117	137	117
7	6	C	Pistol	146	132	121	146	121
8	7	C	Pistol	156	139	126	156	126
9	8	C	Pistol	165	146	131	165	131
10	9	C	Pistol	174	153	136	174	136
11	10	C	Pistol	184	160	141	184	141
12	11	C	Pistol	193	167	146	193	146
13	12	C	Pistol	202	174	151	202	151
14	13	C	Pistol	211	181	156	211	156
15	14	C	Pistol	221	188	162	221	162
16	15	C	Pistol	230	196	168	230	168
17	16	C	Pistol	239	203	173	239	173
18	17	C	Pistol	249	211	179	249	179
19	18	C	Pistol	258	219	185	258	185
20	19	C	Pistol	267	227	192	267	192
21	20	C	Pistol	277	235	198	277	198
22	21	C	Pistol	286	243	205	286	205
23	22	C	Pistol	295	251	211	295	211
24	23	C	Pistol	304	259	218	304	218
25	24	C	Pistol	314	267	226	314	226
26	25	C	Pistol	323	276	233	323	233
27	26	C	Pistol	332	284	240	332	240
28	27	C	Pistol	342	293	248	342	248
29	28	C	Pistol	351	302	256	351	256
30	29	C	Pistol	360	311	264	360	264
31	30	C	Pistol	370	319	272	370	272
32	31	C	Pistol	379	329	281	379	281
33	32	C	Pistol	388	338	289	388	289
34	33	C	Pistol	398	347	298	398	298
35	34	C	Pistol	407	357	308	407	308
36	35	C	Pistol	416	366	317	416	317
37	36	C	Pistol	426	376	327	426	327
38	37	C	Pistol	435	386	337	435	337
39	38	C	Pistol	444	396	347	444	347
40	39	C	Pistol	454	406	357	454	357
41	40	C	Pistol	463	416	368	463	368
42	41	C	Pistol	472	426	379	472	379
43	42	C	Pistol	482	437	390	482	390
44	43	C	Pistol	491	447	402	491	402
45	44	C	Pistol	500	458	413	500	413
46	45	C	Pistol	510	469	426	510	426
47	46	C	Pistol	519	480	438	519	438
48	47	C	Pistol	528	491	451	528	451
49	48	C	Pistol	538	503	464	538	464
50	49	C	Pistol	547	514	477	547	477
51	50	C	Pistol	556	526	491	556	491
52	51	C	Pistol	566	537	505	566	505
53	52	C	Pistol	575	549	520	575	520
54	53	C	Pistol	584	561	535	584	535
55	54	C	Pistol	594	573	550	594	550
56	55	C	Pistol	603	586	565	603	565
57	56	C	Pistol	613	598	582	613	582
58	57	C	Pistol	622	611	598	622	598
59	58	C	Pistol	631	624	615	631	615
60	59	C	Pistol	641	637	632	641	632
61	60	C	Pistol	650	650	650	650	650
62	1	B	Pistol	100	100	100	100	100
63	2	B	Pistol	114	110	106	114	106
Stats For Test

 

[VincentNL99]

That's an awesome question!
I imagine for the "stats for test" tabblad all the levels, rarity and classes their stats are given? Not only for Pistol rairity C?

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Advanced Concatenate help
If you have posted the question at more places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Fluff]

I have an excel assessment as a starting point for a job interview

Please do not ask us to help you with an interview test. IMO it would be totally unethical & if we help you get the job it would be unfair on the other candidates.

 

[accountfornada]

That's an awesome question!
I imagine for the "stats for test" tabblad all the levels, rarity and classes their stats are given? Not only for Pistol rairity C?

Yes, that's correct. It totals to just over 500 rows, so I was unsure if I should have copied the entire thing or not.

 

[accountfornada]

Please do not ask us to help you with an interview test. IMO it would be totally unethical & if we help you get the job it would be unfair on the other candidates.

I understand.

Can I ask for assistance with one specific part of the question rather than the question as a whole then? I have gotten everything set up pulling data correctly from the "Stats for test" sheet, however, I don't exactly understand how to go about with calculating the critical rate, and how to figure the *chance* of it happening, and then how to plug that chance into the calculations already made.

Thanks

 

[Fluff]

You would need to explain exactly what you are trying to do & what you have already tried. Members here give tips & advice, but don't expect them to give you a formula, for the reasons already outlined.

 

[accountfornada]

After figuring out most everything last night, I have everything figured as far as pulling data from the other sheet, and calculating it all to get the 3 answers requested except for one part of the question.
The problem wants to figure "Crt rate" of units (Row 10 + Row 11) and if the unit does Crt it doubles the damage of the unit before taking into account the other units defense.

As the units Level, Rarity, and Class are all dynamic and changeable via drop down menus I have created a table off to the side that pulls data from the Stats sheet, and have Crt chance of each unit figured into the table as well. However I have no idea how I would begin to plug a chance of something happening into the damage formulas

Test
Using all that you have learned, create a Kill/Death model with hits to kill for two units fighting each other using the following combat model parameters: • Pistol does 50% bonus damage against Grenade • Grenade does 50% bonus damage against Bazoooka • Bazooke does 50% bonus damage against Pistol • Crit rate is the unit's CRT rating divided by the function of the highest CRT rating in the game times 4 • If a unit crits, their CRD is added to their attack damage • Damage dealt is a simple attack - defense calculation The goal is to use all the functions you have learned as well as using spreadsheet deisgn best practices. It will be helpful to create a parameters table for the combat model. The three input fields (Winner, Hits to Kill, and Winner health remaining) will require a long formula comprising of multiple parts. A tip is to construct the formula in parts, and then assemble the parts into the final formula.
Tutorial #3: Using concatenate with indirect							Stats	Unit 1	Unit 2
Unit 1				Unit 2			Atk	188	993
Level	6			Level	54		Def	161	949
Rarity	A			Rarity	A		HP	140	993
Class:	Pistol			Class:	Grenade
							Bonus	1.5	1
							Dmg Dealt	-479.49561	832
Winner:	Unit 2						Crt	188	993
Hits to kill:	0.168269231						Crd	140	993
Winner health remaining:	993						Crit Rate	0.01032513	0.054536
							Hits req'ed	-2.0709262	0.168269
							Winner	Unit 2	Unit 2
							Remaining HP	993

 

[accountfornada]

I believe I'm making progress, after looking into it some more I believe it would just be a simple IF statement, if critical occurs, add crit_dmg.

However, how would I go about converting that into a formula?