accountfornada
New Member
- Joined
- Nov 30, 2021
- Messages
- 5
- Platform
- Windows
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 |