Good morning fellow excel'ers,
I am trying to create this formula to put an "x" in column L for each of the players listed in cells A29:A35 for the lowest numbers in column X if there name is in column G. It puts 30 x's for each name based on what is in column C rows 29:45 Please see the current formulas I am using. Currently I believe it is selecting the highest values in X for each name. I tried changing large to small, but that then puts an x in every row.
Thanks for any assistance!
I am trying to create this formula to put an "x" in column L for each of the players listed in cells A29:A35 for the lowest numbers in column X if there name is in column G. It puts 30 x's for each name based on what is in column C rows 29:45 Please see the current formulas I am using. Currently I believe it is selecting the highest values in X for each name. I tried changing large to small, but that then puts an x in every row.
correlationrandom1(in_progress)5(USE4FORNOW)REVISION4.xlsm | ||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | |||
1 | QB | FLEX | FLEX | FLEX | FLEX | QB | FLEX | FLEX | FLEX | FLEX | Selection | ComboID | Salary | Projection | Floor Projection | Ceiling Projection↑ | ^ Difference | Target | Depth Total | TARGET PT 12+ | Ownership L|U | Correlation | Value↓ | |||
2 | Justin Herbert | Justin Herbert | Justin Herbert | Justin Herbert | Justin Herbert | Nick Foles | Justin Herbert | Michael Pittman Jr. | Cameron Dicker | Joshua Kelley | x | 101265 | 59000 | 64.305 | 44 | 89 | 2.7537 | 61.53 | 6 | 3 | 0.99% | 1.09 | 4.690876068 | |||
3 | Austin Ekeler | Austin Ekeler | Austin Ekeler | Austin Ekeler | Austin Ekeler | Justin Herbert | Nick Foles | Michael Pittman Jr. | Cameron Dicker | Joshua Kelley | 16215 | 59000 | 68.24 | 49 | 92.5 | 2.7537 | 66.225 | 6 | 3 | 0.99% | 1.09 | 4.690876068 | ||||
4 | Keenan Allen | Keenan Allen | Keenan Allen | Keenan Allen | Keenan Allen | Nick Foles | Austin Ekeler | Michael Pittman Jr. | Cameron Dicker | Joshua Kelley | 105990 | 59500 | 61.155 | 37.5 | 87.5 | 2.7427 | 58.57 | 6 | 3 | 0.47% | 0.77 | 4.460648796 | ||||
5 | Nick Foles | Nick Foles | Nick Foles | Nick Foles | Nick Foles | Austin Ekeler | Nick Foles | Michael Pittman Jr. | Cameron Dicker | Joshua Kelley | 49290 | 59500 | 63.515 | 41.5 | 91.5 | 2.7372 | 61.785 | 6 | 3 | 0.47% | 0.77 | 4.460648796 | ||||
6 | Mike Williams | Mike Williams | Mike Williams | Mike Williams | Mike Williams | Mike Williams | Justin Herbert | Nick Foles | Cameron Dicker | Gerald Everett | x | 133391 | 60000 | 69.11 | 45.5 | 103 | 2.75865 | 66.25 | 6 | 3 | 1.30% | 1.52 | 5.109707096 | |||
7 | Deon Jackson | Deon Jackson | Deon Jackson | Deon Jackson | Deon Jackson | Nick Foles | Justin Herbert | Mike Williams | Cameron Dicker | Gerald Everett | x | 100631 | 60000 | 69.225 | 48.5 | 99.5 | 2.7614 | 66.2 | 6 | 3 | 1.30% | 1.52 | 5.109707096 | |||
8 | Michael Pittman Jr. | Michael Pittman Jr. | Michael Pittman Jr. | Michael Pittman Jr. | Michael Pittman Jr. | Nick Foles | Justin Herbert | Michael Pittman Jr. | DeAndre Carter | Cameron Dicker | 101350 | 59500 | 63.835 | 47.5 | 93.5 | 2.7507 | 63.64 | 8 | 3 | 0.02% | 1.91 | 4.584590354 | ||||
9 | Zack Moss | Zack Moss | Zack Moss | Nick Foles | Justin Herbert | Michael Pittman Jr. | DeAndre Carter | Chase McLaughlin | 101349 | 60000 | 64.635 | 42 | 92.5 | 2.755 | 61.59 | 8 | 3 | 0.02% | 1.91 | 4.616052342 | ||||||
10 | Chase McLaughlin | Chase McLaughlin | Chase McLaughlin | Mike Williams | Justin Herbert | Nick Foles | Cameron Dicker | Alec Pierce | x | 133394 | 59500 | 67.07 | 46.5 | 101.5 | 2.75035 | 66.1 | 8 | 3 | 1.04% | 1.91 | 4.909486508 | |||||
11 | Cameron Dicker | Cameron Dicker | Cameron Dicker | Nick Foles | Justin Herbert | Mike Williams | Cameron Dicker | Alec Pierce | x | 100634 | 59500 | 67.185 | 49.5 | 99.5 | 2.7531 | 66.05 | 8 | 3 | 1.04% | 1.91 | 4.909486508 | |||||
12 | Gerald Everett | Gerald Everett | Gerald Everett | Mike Williams | Justin Herbert | Nick Foles | Chase McLaughlin | Alec Pierce | x | 133379 | 60000 | 67.87 | 39.5 | 102.5 | 2.75465 | 64.05 | 8 | 3 | 1.20% | 1.91 | 4.940948496 | |||||
13 | Joshua Palmer | Joshua Palmer | Joshua Palmer | Nick Foles | Justin Herbert | Mike Williams | Chase McLaughlin | Alec Pierce | x | 100619 | 60000 | 67.985 | 43.5 | 101 | 2.7574 | 64 | 8 | 3 | 1.20% | 1.91 | 4.940948496 | |||||
14 | Parris Campbell | Parris Campbell | Parris Campbell | Justin Herbert | Nick Foles | Michael Pittman Jr. | DeAndre Carter | Cameron Dicker | 16300 | 59500 | 67.77 | 51 | 98 | 2.7507 | 68.335 | 8 | 3 | 0.02% | 1.91 | 4.584590354 | ||||||
15 | Alec Pierce | Alec Pierce | Alec Pierce | Justin Herbert | Nick Foles | Michael Pittman Jr. | DeAndre Carter | Chase McLaughlin | 16299 | 60000 | 68.57 | 47 | 100.5 | 2.755 | 66.285 | 8 | 3 | 0.02% | 1.91 | 4.616052342 | ||||||
16 | Indianapolis Colts | Joshua Kelley | Joshua Kelley | Nick Foles | Keenan Allen | Michael Pittman Jr. | Chase McLaughlin | Cameron Dicker | x | 110695 | 59000 | 61.805 | 38.5 | 88.5 | 2.7703 | 56.49 | 5 | 3 | 0.05% | 0.89 | 4.737219009 | |||||
17 | DeAndre Carter | Keenan Allen | Nick Foles | Michael Pittman Jr. | Chase McLaughlin | Cameron Dicker | x | 82345 | 59000 | 61.87 | 40 | 86 | 2.77205 | 56.76 | 5 | 3 | 0.05% | 0.89 | 4.737219009 | |||||||
18 | Donald Parham Jr. | Mike Williams | Nick Foles | Michael Pittman Jr. | Chase McLaughlin | Cameron Dicker | 148495 | 58000 | 61.33 | 29.5 | 91.5 | 2.75855 | 56.1 | 6 | 3 | 0.04% | 0.89 | 4.785574565 | ||||||||
19 | Jelani Woods | Nick Foles | Mike Williams | Michael Pittman Jr. | Chase McLaughlin | Cameron Dicker | x | 120145 | 58000 | 61.445 | 32.5 | 88.5 | 2.7613 | 56.05 | 6 | 3 | 0.04% | 0.89 | 4.785574565 | |||||||
20 | Los Angeles Chargers | Mike Williams | Justin Herbert | Michael Pittman Jr. | Chase McLaughlin | Cameron Dicker | x | 134320 | 60000 | 69.2 | 42 | 103 | 2.75855 | 65.49 | 6 | 3 | 0.05% | 0.89 | 5.162360279 | |||||||
21 | Indianapolis Colts | Nick Foles | Keenan Allen | Michael Pittman Jr. | Cameron Dicker | Gerald Everett | 110711 | 58000 | 59.435 | 36.5 | 85.5 | 2.7687 | 55.71 | 5 | 3 | 0.55% | 1.07 | 4.580005387 | ||||||||
22 | Ashton Dulin | Keenan Allen | Nick Foles | Michael Pittman Jr. | Cameron Dicker | Gerald Everett | 82361 | 58000 | 59.5 | 42.5 | 86.5 | 2.77045 | 55.98 | 5 | 3 | 0.55% | 1.07 | 4.580005387 | ||||||||
23 | Nick Foles | Keenan Allen | Michael Pittman Jr. | Chase McLaughlin | Gerald Everett | 110696 | 58500 | 60.235 | 34 | 87.5 | 2.773 | 53.66 | 5 | 3 | 0.63% | 0.89 | 4.611467375 | |||||||||
24 | Keenan Allen | Nick Foles | Michael Pittman Jr. | Chase McLaughlin | Gerald Everett | 82346 | 58500 | 60.3 | 37.5 | 87.5 | 2.77475 | 53.93 | 5 | 3 | 0.63% | 0.89 | 4.611467375 | |||||||||
25 | Mike Williams | Nick Foles | Michael Pittman Jr. | Cameron Dicker | Gerald Everett | 148511 | 57000 | 58.96 | 34 | 89 | 2.75695 | 55.32 | 6 | 3 | 0.49% | 1.07 | 4.628360942 | |||||||||
26 | Nick Foles | Mike Williams | Michael Pittman Jr. | Cameron Dicker | Gerald Everett | 120161 | 57000 | 59.075 | 34.5 | 89.5 | 2.7597 | 55.27 | 6 | 3 | 0.49% | 1.07 | 4.628360942 | |||||||||
27 | Mike Williams | Nick Foles | Michael Pittman Jr. | Chase McLaughlin | Gerald Everett | 148496 | 57500 | 59.76 | 30 | 93 | 2.76125 | 53.27 | 6 | 3 | 0.57% | 0.89 | 4.659822931 | |||||||||
28 | MVP | NumberOfLineUps | #OfLineUpsToTake | %OfLineUpsToTake | Nick Foles | Mike Williams | Michael Pittman Jr. | Chase McLaughlin | Gerald Everett | 120146 | 57500 | 59.875 | 31.5 | 91 | 2.764 | 53.22 | 6 | 3 | 0.57% | 0.89 | 4.659822931 | |||||
29 | Justin Herbert | 228 | 30 | 0.131578947 | Mike Williams | Justin Herbert | Michael Pittman Jr. | Chase McLaughlin | Gerald Everett | x | 134321 | 59500 | 67.63 | 37 | 99 | 2.76125 | 62.66 | 6 | 3 | 0.62% | 1.12 | 5.036608645 | ||||
30 | Austin Ekeler | 223 | 30 | 0.134529148 | Keenan Allen | Justin Herbert | Michael Pittman Jr. | Cameron Dicker | Gerald Everett | x | 68186 | 60000 | 67.37 | 52.5 | 100.5 | 2.77045 | 65.37 | 5 | 3 | 0.60% | 1.15 | 4.956791101 | ||||
31 | Keenan Allen | 255 | 30 | 0.117647059 | Mike Williams | Justin Herbert | Michael Pittman Jr. | Cameron Dicker | Gerald Everett | x | 134336 | 59000 | 66.83 | 46 | 99.5 | 2.75695 | 64.71 | 6 | 3 | 0.54% | 1.15 | 5.005146657 | ||||
32 | Nick Foles | 266 | 30 | 0.112781955 | Justin Herbert | Keenan Allen | Michael Pittman Jr. | Cameron Dicker | Gerald Everett | x | 11486 | 60000 | 71.24 | 55 | 102.5 | 2.7687 | 69.795 | 5 | 3 | 0.60% | 1.15 | 4.956791101 | ||||
33 | Mike Williams | 290 | 30 | 0.103448276 | Justin Herbert | Mike Williams | Michael Pittman Jr. | Cameron Dicker | Gerald Everett | x | 20936 | 59000 | 70.88 | 53.5 | 105.5 | 2.7597 | 69.355 | 6 | 3 | 0.54% | 1.15 | 5.005146657 | ||||
34 | Deon Jackson | 0 | 30 | 0 | Nick Foles | Keenan Allen | Michael Pittman Jr. | DeAndre Carter | Chase McLaughlin | 110799 | 57500 | 56.895 | 34.5 | 83.5 | 2.7585 | 52.74 | 8 | 3 | 0.01% | 0.87 | 4.282996787 | |||||
35 | Michael Pittman Jr. | 0 | 30 | 0 | Keenan Allen | Nick Foles | Michael Pittman Jr. | DeAndre Carter | Chase McLaughlin | 82449 | 57500 | 56.96 | 34 | 86.5 | 2.76025 | 53.01 | 8 | 3 | 0.01% | 0.87 | 4.282996787 | |||||
36 | Mike Williams | Nick Foles | Michael Pittman Jr. | DeAndre Carter | Chase McLaughlin | 148599 | 56500 | 56.42 | 27.5 | 94 | 2.74675 | 52.35 | 9 | 3 | 0.01% | 0.87 | 4.331352342 | |||||||||
Worksheet |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B29 | B29 | =COUNTIF(G2:G$1263,A29) |
B30 | B30 | =COUNTIF(G2:G$1263,A30) |
B31 | B31 | =COUNTIF(G2:G$1263,A31) |
B32 | B32 | =COUNTIF(G2:G$1263,A32) |
B33 | B33 | =COUNTIF(G2:G$1263,A33) |
B34 | B34 | =COUNTIF(G2:G$1263,A34) |
B35 | B35 | =COUNTIF(G2:G$1263,A35) |
D29:D35 | D29 | =IFERROR(C29/B29,0) |
L2:L36 | L2 | =IF(X2>=LARGE((G2=$G$2:$G$1263)*($X$2:$X$1263),VLOOKUP(G2,$A$29:$D$35,3,0)),"x","") |
Press CTRL+SHIFT+ENTER to enter array formulas. |
Thanks for any assistance!