I changed the Offset to -11 from -1 due to 'hidden columns'I did a small test:
Cell Formulas Range Formula Y43 Y43 =MAX(List) Y44 Y44 =OFFSET(INDIRECT(INDEX(List,MATCH(MAX(N(INDIRECT(List))),N(INDIRECT(List)),0))),0,-1) Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges Name Refers To Cells List ='Weekly Picks'!$Y$39,'Weekly Picks'!$Y$40,'Weekly Picks'!$Y$41,'Weekly Picks'!$AK$39,'Weekly Picks'!$AK$40,'Weekly Picks'!$AK$41 Y43:Y44
I only did the small test separately so I used a different name (list).Thanks for the small test.
The name you posted is very different from the one I posted
I defined a named array and you defined a named range.
Please edit the CellList name and define it as a named array of strings like the one I posted and then test the internal formula again.
NFL.xlsm | ||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|
AX | AY | AZ | BA | BB | BC | BD | BE | BF | BG | |||
3 | ATS | ATS | ||||||||||
4 | 6 ATS | ATS Cover % >= 60% | ||||||||||
5 | Current | Total | Current | Total | ||||||||
6 | W: | 0 | W: | 9 | 52.9% | W: | 0 | W: | 9 | 39.1% | ||
7 | L: | 0 | L: | 8 | L: | 0 | L: | 14 | ||||
8 | Psh: | 0 | Psh: | 0 | Psh: | 0 | Psh: | 0 | ||||
9 | Previous | 17 | Previous | 23 | ||||||||
10 | W: | 9 | $ 40.95 | W: | 9 | $ 40.95 | ||||||
11 | L: | 8 | $ 40.00 | $ 0.95 | L: | 14 | $ 70.00 | $ (29.05) | ||||
12 | Psh: | 0 | Psh: | 0 | ||||||||
13 | 5 ATS | MOV > ATS, IFF ATS<0 | ||||||||||
14 | Current | Total | Current | Total | ||||||||
15 | W: | 0 | W: | 5 | 27.8% | W: | 0 | W: | 4 | 28.6% | ||
16 | L: | 0 | L: | 13 | L: | 0 | L: | 10 | ||||
17 | Psh: | 0 | Psh: | 0 | Psh: | Psh: | 0 | |||||
18 | Previous | 18 | Previous | 14 | ||||||||
19 | W: | 5 | $ 22.75 | W: | 4 | $ 18.20 | ||||||
20 | L: | 13 | $ 65.00 | $ (42.25) | L: | 10 | $ 50.00 | $ (31.80) | ||||
21 | Psh: | 0 | Psh: | 0 | ||||||||
22 | 4 ATS | ATS <= -7 | ||||||||||
23 | Current | Total | Current | Total | ||||||||
24 | W: | 0 | W: | 8 | 72.7% | W: | 0 | W: | 4 | 33.3% | ||
25 | L: | 0 | L: | 3 | L: | 0 | L: | 8 | ||||
26 | Psh: | 0 | Psh: | 0 | Psh: | 0 | Psh: | 0 | ||||
27 | Previous | 11 | Previous | 12 | ||||||||
28 | W: | 8 | $ 36.40 | W: | 4 | $ 18.20 | ||||||
29 | L: | 3 | $ 15.00 | $ 21.40 | L: | 8 | $ 40.00 | $ (21.80) | ||||
30 | Psh: | 0 | Psh: | 0 | ||||||||
31 | 3 ATS | ATS >= 10 | ||||||||||
32 | Current | Total | Current | Total | ||||||||
33 | W: | 1 | W: | 6.5 | 46.4% | W: | 0 | W: | 2 | 66.7% | ||
34 | L: | 1 | L: | 6.5 | L: | 0 | L: | 1 | ||||
35 | Psh: | 0 | Psh: | 1 | Psh: | 0 | Psh: | 0 | ||||
36 | Previous | 14 | Previous | 3 | ||||||||
37 | W: | 5.5 | $ 25.03 | W: | 2 | $ 9.10 | ||||||
38 | L: | 5.5 | $ 27.50 | $ (2.48) | L: | 1 | $ 5.00 | $ 4.10 | ||||
39 | Psh: | 1 | Psh: | 0 | ||||||||
40 | ||||||||||||
41 | ||||||||||||
42 | Good for 1 column | #VALUE! | ||||||||||
43 | 4 ATS | #N/A | ||||||||||
44 | 4 | #VALUE! | ||||||||||
45 | #VALUE! | 73% | ||||||||||
46 | ||||||||||||
47 | #VALUE! | |||||||||||
Weekly Picks |
Cell Formulas | ||
---|---|---|
Range | Formula | |
BB6,BG33,BB33,BG24,BB24,BG15,BB15,BG6 | BB6 | =BA6/BA9 |
AY6 | AY6 | =COUNTIFS($AT$4:$AT$35,"6",$C$4:$C$35,"W")+($AY$8*0.5) |
AY7 | AY7 | =COUNTIFS($AT$4:$AT$35,"6",$C$4:$C$35,"L")+($AY$8*0.5) |
AY8 | AY8 | =COUNTIFS($AT$4:$AT$35,"6",$C$4:$C$35,"Psh") |
BD6 | BD6 | =COUNTIFS($C$4:$C$35,"W",$U$4:$U$35,">=.6") |
BD7 | BD7 | =COUNTIFS($C$4:$C$35,"L",$U$4:$U$35,">=.6") |
BD8 | BD8 | =COUNTIFS($C$4:$C$35,"Psh",$U$4:$U$35,">=.6") |
BF33:BF35,BA33:BA35,BF24:BF26,BA24:BA25,BF15:BF16,BA15:BA17,BF6:BF7,BA6:BA7 | BA6 | =AY10+AY6 |
BA8,BF17,BF8 | BA8 | =AY13+AY8 |
BA9,BF36,BA36,BF27,BA27,BF18,BA18,BF9 | BA9 | =SUM(BA6:BA8) |
AZ10,BE37,AZ37,AZ28,BE19,AZ19,BE10 | AZ10 | =AY10*4.55 |
AZ11,BE38,AZ38,AZ29,BE20,AZ20,BE11 | AZ11 | =AY11*5 |
BA11,BF38,BA38,BF29,BA29,BF20,BA20,BF11 | BA11 | =AZ10-AZ11 |
BD15 | BD15 | =SUMPRODUCT((V4:V35>=IFERROR(B4:B35*-1,0))*(C4:C35="W")*(B4:B35<0)) |
BD16 | BD16 | =SUMPRODUCT((V4:V35>=IFERROR(B4:B35*-1,0))*(C4:C35="L")*(B4:B35<0)) |
AY15 | AY15 | =COUNTIFS($AT$4:$AT$35,"5",$C$4:$C$35,"W")+($AY$17*0.5) |
AY16 | AY16 | =COUNTIFS($AT$4:$AT$35,"5",$C$4:$C$35,"L")+($AY$17*0.5) |
AY17 | AY17 | =COUNTIFS($AT$4:$AT$35,"5",$C$4:$C$35,"Psh") |
AY24 | AY24 | =COUNTIFS($AT$4:$AT$35,"4",$C$4:$C$35,"W")+($AY$26*0.5) |
AY25 | AY25 | =COUNTIFS($AT$4:$AT$35,"4",$C$4:$C$35,"L")+($AY$26*0.5) |
AY26 | AY26 | =COUNTIFS($AT$4:$AT$35,"4",$C$4:$C$35,"Psh") |
BD24 | BD24 | =COUNTIFS($C$4:$C$35,"W",$B$4:$B$35,"<=-7") |
BD25 | BD25 | =COUNTIFS($C$4:$C$35,"L",$B$4:$B$35,"<=-7") |
BD26 | BD26 | =COUNTIFS($C$4:$C$35,"Psh",$B$4:$B$35,"<=-7") |
BA26 | BA26 | =AY32+AY26 |
BE28 | BE28 | =BF24*4.55 |
BE29 | BE29 | =BF25*5 |
AY33 | AY33 | =COUNTIFS($AT$4:$AT$35,"3",$C$4:$C$35,"W")+($AY$35*0.5) |
AY34 | AY34 | =COUNTIFS($AT$4:$AT$35,"3",$C$4:$C$35,"L")+($AY$35*0.5) |
AY35 | AY35 | =COUNTIFS($AT$4:$AT$35,"3",$C$4:$C$35,"Psh") |
BD33 | BD33 | =COUNTIFS($C$4:$C$35,"W",$B$4:$B$35,">=10") |
BD34 | BD34 | =COUNTIFS($C$4:$C$35,"L",$B$4:$B$35,">=10") |
BD35 | BD35 | =COUNTIFS($C$4:$C$35,"Psh",$B$4:$B$35,">=10") |
AZ43 | AZ43 | =OFFSET(INDEX(result1,MATCH(MAX(result1),result1,FALSE)),-2,-4) |
AZ44 | AZ44 | =LEFT(OFFSET(INDEX(result1,MATCH(MAX(result1),result1,FALSE)),-2,-4),1) |
AZ45 | AZ45 | =OFFSET(INDEX(result1:result2,MATCH(MAX(result1:result2),result1:result2,FALSE)),0,-1) |
BC42,BC44 | BC42 | =OFFSET(INDIRECT(INDEX(CellsList,MATCH(MAX(N(INDIRECT(CellsList))),N(INDIRECT(CellsList)),0))),-2,-4) |
BC43 | BC43 | =OFFSET(INDEX(CellsList,MATCH(MAX(CellsList),CellsList,FALSE)),-2,-4) |
BC45 | BC45 | =MAX(CellsList) |
BC47 | BC47 | =INDEX(CellsList,MATCH(MAX(N(INDIRECT(CellsList))),N(INDIRECT(CellsList)),0)) |
Press CTRL+SHIFT+ENTER to enter array formulas. |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
CellsList | ='Weekly Picks'!$BB$6,'Weekly Picks'!$BG$6,'Weekly Picks'!$BB$15,'Weekly Picks'!$BG$15,'Weekly Picks'!$BB$24,'Weekly Picks'!$BG$24,'Weekly Picks'!$BB$33,'Weekly Picks'!$BG$33 | AZ43:AZ45, BC47, BC42:BC45 |
result1 | ='Weekly Picks'!$BB:$BB | AZ43:AZ45 |
result2 | ='Weekly Picks'!$BG:$BG | AZ45 |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
BB6,BB15,BB24,BB33,BG6,BG15,BG24,BG33 | Cell Value | top 1 values | text | NO |
BB33,BB24,BB15,BB6 | Cell Value | top 1 values | text | NO |
BG33,BG24,BG15,BG6 | Cell Value | top 1 bottom values | text | NO |
BG33,BG24,BG15,BG6 | Cell Value | top 1 values | text | NO |
BG33,BG24,BG15,BG6 | Cell Value | <"MAX" | text | NO |
BG33,BG24,BG15,BG6 | Cell Value | >"MIN" | text | NO |
BB33,BB24,BB15,BB6 | Cell Value | top 1 bottom values | text | NO |
BB33,BB24,BB15,BB6 | Cell Value | >"MIN" | text | NO |
BB33,BB24,BB15,BB6 | Cell Value | <"MAX" | text | NO |
BA38 | Cell Value | <0 | text | NO |
BA38 | Cell Value | >0 | text | NO |
BB24,BB15,BB6 | Cell Value | top 1 bottom values | text | NO |
BB24,BB15,BB6 | Cell Value | >"MIN" | text | NO |
BB24,BB15,BB6 | Cell Value | <"MAX" | text | NO |
BA11,BF11,BA20,BF20,BA29,BF29,BA38,BF38 | Cell Value | <0 | text | NO |
BA11,BF11,BA20,BF20,BA29,BF29,BA38,BF38 | Cell Value | >0 | text | NO |
Cell Formulas | ||
---|---|---|
Range | Formula | |
Y47 | Y47 | {=MAX(N(INDIRECT(List)))} |
Y49 | Y49 | {=OFFSET(INDIRECT(INDEX(List,MATCH(MAX(N(INDIRECT(List))),N(INDIRECT(List)),0))),0,-1)} |
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces. |
I arranged my array as you did, but still receive an error. How am I not defining the names correctly? or is there a setting not correct?Remark: in your last post you are still not defining the names the right way.
Please try your small test with the name defined as in my last post (#13)
Notice that you are using offset -1 for the column. This means that if the max value is in column Y you get the value in column X, but if the max value is in column AK you get the value in AJ (not seen in your post.
NFL.xlsm | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
X | Y | Z | AK | |||||||||||||
39 | ONE | 1 | TWO | 1 | ||||||||||||
40 | THREE | 10 | FOUR | 1 | ||||||||||||
41 | FIVE | 5 | SIX | 1 | ||||||||||||
42 | ||||||||||||||||
43 | MAX= | #VALUE! | ||||||||||||||
44 | Offset= | #VALUE! | ||||||||||||||
45 | ||||||||||||||||
46 | MAX= | 10 | ||||||||||||||
47 | Offset= | THREE | ||||||||||||||
Weekly Picks |
Cell Formulas | ||
---|---|---|
Range | Formula | |
Y43 | Y43 | =MAX(N(INDIRECT(List))) |
Y44 | Y44 | =OFFSET(INDIRECT(INDEX(List,MATCH(MAX(N(INDIRECT(List))),N(INDIRECT(List)),0))),0,-1) |
Y46 | Y46 | =MAX(List) |
Y47 | Y47 | =OFFSET(Y40,0,-1) |
Press CTRL+SHIFT+ENTER to enter array formulas. |
Named Ranges | ||
---|---|---|
Name | Refers To | Cells |
List | ='Weekly Picks'!$Y$39,'Weekly Picks'!$Y$40,'Weekly Picks'!$Y$41,'Weekly Picks'!$AK$39,'Weekly Picks'!$AK$40,'Weekly Picks'!$AK$41 | Y43:Y44, Y46 |
How do you create it that way?Your name is still not correct.
You are defining a named range and I'm defining a named array.
It's not
List: ='Weekly Picks'!$Y$39,'Weekly Picks'!$Y$40,'Weekly Picks'!$Y$41,'Weekly Picks'!$AK$39,'Weekly Picks'!$AK$40,'Weekly Picks'!$AK$41
it's
List: ={"'Weekly Picks'!$Y$39","'Weekly Picks'!$Y$40","'Weekly Picks'!$Y$41","'Weekly Picks'!$AK$39","'Weekly Picks'!$AK$40","'Weekly Picks'!$AK$41"}
What you want the name to refer to is an array of strings, the addresses of the cells.
That fixed the formula. I need to change the original range as above. Why does my 'refers to' look like this. Not as an array?Just like any name
Formulas->Name Manager->New
Name: List
Refers to: ={"'Weekly Picks'!$Y$39","'Weekly Picks'!$Y$40","'Weekly Picks'!$Y$41","'Weekly Picks'!$AK$39","'Weekly Picks'!$AK$40","'Weekly Picks'!$AK$41"}
That fixed the formula.