I used a Named Range to find the Offset of Max value for 1 range (BB:BB), but using 2 ranges I can't get the Max valueHello,
Offset from MAX value of certain cells: MAX(BB6,BB15,BB24,BG6,BG15,BG24,BB33,BG33)
Which does give me the MAX value, but I want to Offset from that cell(MAX value) by (-2,-2).
Thank you.
I created the Named Range (CellsList), but get #VALUE! as a result.Hi
This is a solution that you can apply to this type of problems with a range of non-countiguous cells
I defined the named array:
Name: CellsList
Refers to: ={"BB6","BB15","BB24","BG6","BG15","BG24","BB33","BG33"}
and used for the result the array formula:
=OFFSET(INDIRECT(INDEX(CellsList,MATCH(MAX(N(INDIRECT(CellsList))),N(INDIRECT(CellsList)),0))),-2,-2)
This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER
ADVERTISEMENT
#VALUE!What is the value of the internal (array) formula:
=INDEX(CellsList,MATCH(MAX(N(INDIRECT(CellsList))),N(INDIRECT(CellsList)),0))
This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER
Here's what Im working with:
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 | ||||||||
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") |
Press CTRL+SHIFT+ENTER to enter array formulas. |
I did a small test:Here's what Im working with:
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 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") Press CTRL+SHIFT+ENTER to enter array formulas.
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 defined the named array:
Name: CellsList
Refers to: ={"BB6","BB15","BB24","BG6","BG15","BG24","BB33","BG33"}