# Offset from MAX value

#### gtd526

##### Active Member
Hello,
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.

### Excel Facts

Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

#### gtd526

##### Active Member
Hello,
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 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 value
=OFFSET(INDEX(result1,MATCH(MAX(result1),result1,FALSE)),-2,-4)
result1 = BB:BB
result2 = BG:BG

#### pgc01

##### MrExcel MVP
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

#### gtd526

##### Active Member
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
I created the Named Range (CellsList), but get #VALUE! as a result.
I get the MAX value using =MAX(CellsList).
The MAX value are for Percentages.

#### pgc01

##### MrExcel MVP

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

#### pgc01

##### MrExcel MVP
Did a quick test and worked OK

Book1
AXAYAZBABBBCBDBEBFBGBH
1
2Result
3Val3
4Val1Val11
5
60,120,1
7
8
9
10
11
12
13Val2Val12
14
150,030,06
16
17
18
19
20
21
22Val3
23Val13
240,140,11
25
26
27
28
29
30
31Val4Val14
32
330,050,04
34
Sheet3

#### gtd526

##### Active Member

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
#VALUE!
array formula CTRL-SHIFT-ENTER

#### gtd526

##### Active Member
Did a quick test and worked OK

Book1
AXAYAZBABBBCBDBEBFBGBH
1
2Result
3Val3
4Val1Val11
5
60,120,1
7
8
9
10
11
12
13Val2Val12
14
150,030,06
16
17
18
19
20
21
22Val3
23Val13
240,140,11
25
26
27
28
29
30
31Val4Val14
32
330,050,04
34
Sheet3
Here's what Im working with:
NFL.xlsm
AXAYAZBABBBCBDBEBFBG
3ATSATS
46 ATSATS Cover % >= 60%
5CurrentTotalCurrentTotal
6W:0W:952.9%W:0W:939.1%
7L:0L:8L:0L:14
8Psh:0Psh:0Psh:0Psh:0
9Previous17Previous23
10W:9 \$ 40.95 W:9 \$ 40.95
11L:8 \$ 40.00 \$ 0.95 L:14 \$ 70.00 \$ (29.05)
12Psh:0Psh:0
135 ATSMOV > ATS, IFF ATS<0
14CurrentTotalCurrentTotal
15W:0W:527.8%W:0W:428.6%
16L:0L:13L:0L:10
17Psh:0Psh:0Psh:Psh:0
18Previous18Previous14
19W:5 \$ 22.75 W:4 \$ 18.20
20L:13 \$ 65.00 \$ (42.25)L:10 \$ 50.00 \$ (31.80)
21Psh:0Psh:0
224 ATSATS <= -7
23CurrentTotalCurrentTotal
24W:0W:872.7%W:0W:433.3%
25L:0L:3L:0L:8
26Psh:0Psh:0Psh:0Psh:0
27Previous11Previous12
28W:8 \$ 36.40 W:4 \$ 18.20
29L:3 \$ 15.00 \$ 21.40 L:8 \$ 40.00 \$ (21.80)
30Psh:0Psh:0
313 ATSATS >= 10
32CurrentTotalCurrentTotal
33W:1W:6.546.4%W:0W:266.7%
34L:1L:6.5L:0L:1
35Psh:0Psh:1Psh:0Psh:0
36Previous14Previous3
37W:5.5 \$ 25.03 W:2 \$ 9.10
38L:5.5 \$ 27.50 \$ (2.48)L:1 \$ 5.00 \$ 4.10
39Psh:1Psh:0
Weekly Picks
Cell Formulas
RangeFormula
BB6,BG33,BB33,BG24,BB24,BG15,BB15,BG6BB6=BA6/BA9
AY6AY6=COUNTIFS(\$AT\$4:\$AT\$35,"6",\$C\$4:\$C\$35,"W")+(\$AY\$8*0.5)
AY7AY7=COUNTIFS(\$AT\$4:\$AT\$35,"6",\$C\$4:\$C\$35,"L")+(\$AY\$8*0.5)
AY8AY8=COUNTIFS(\$AT\$4:\$AT\$35,"6",\$C\$4:\$C\$35,"Psh")
BD6BD6=COUNTIFS(\$C\$4:\$C\$35,"W",\$U\$4:\$U\$35,">=.6")
BD7BD7=COUNTIFS(\$C\$4:\$C\$35,"L",\$U\$4:\$U\$35,">=.6")
BD8BD8=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:BA7BA6=AY10+AY6
BA8,BF17,BF8BA8=AY13+AY8
BA9,BF36,BA36,BF27,BA27,BF18,BA18,BF9BA9=SUM(BA6:BA8)
AZ10,BE37,AZ37,AZ28,BE19,AZ19,BE10AZ10=AY10*4.55
AZ11,BE38,AZ38,AZ29,BE20,AZ20,BE11AZ11=AY11*5
BA11,BF38,BA38,BF29,BA29,BF20,BA20,BF11BA11=AZ10-AZ11
BD15BD15=SUMPRODUCT((V4:V35>=IFERROR(B4:B35*-1,0))*(C4:C35="W")*(B4:B35<0))
BD16BD16=SUMPRODUCT((V4:V35>=IFERROR(B4:B35*-1,0))*(C4:C35="L")*(B4:B35<0))
AY15AY15=COUNTIFS(\$AT\$4:\$AT\$35,"5",\$C\$4:\$C\$35,"W")+(\$AY\$17*0.5)
AY16AY16=COUNTIFS(\$AT\$4:\$AT\$35,"5",\$C\$4:\$C\$35,"L")+(\$AY\$17*0.5)
AY17AY17=COUNTIFS(\$AT\$4:\$AT\$35,"5",\$C\$4:\$C\$35,"Psh")
AY24AY24=COUNTIFS(\$AT\$4:\$AT\$35,"4",\$C\$4:\$C\$35,"W")+(\$AY\$26*0.5)
AY25AY25=COUNTIFS(\$AT\$4:\$AT\$35,"4",\$C\$4:\$C\$35,"L")+(\$AY\$26*0.5)
AY26AY26=COUNTIFS(\$AT\$4:\$AT\$35,"4",\$C\$4:\$C\$35,"Psh")
BD24BD24=COUNTIFS(\$C\$4:\$C\$35,"W",\$B\$4:\$B\$35,"<=-7")
BD25BD25=COUNTIFS(\$C\$4:\$C\$35,"L",\$B\$4:\$B\$35,"<=-7")
BD26BD26=COUNTIFS(\$C\$4:\$C\$35,"Psh",\$B\$4:\$B\$35,"<=-7")
BA26BA26=AY32+AY26
BE28BE28=BF24*4.55
BE29BE29=BF25*5
AY33AY33=COUNTIFS(\$AT\$4:\$AT\$35,"3",\$C\$4:\$C\$35,"W")+(\$AY\$35*0.5)
AY34AY34=COUNTIFS(\$AT\$4:\$AT\$35,"3",\$C\$4:\$C\$35,"L")+(\$AY\$35*0.5)
AY35AY35=COUNTIFS(\$AT\$4:\$AT\$35,"3",\$C\$4:\$C\$35,"Psh")
BD33BD33=COUNTIFS(\$C\$4:\$C\$35,"W",\$B\$4:\$B\$35,">=10")
BD34BD34=COUNTIFS(\$C\$4:\$C\$35,"L",\$B\$4:\$B\$35,">=10")
BD35BD35=COUNTIFS(\$C\$4:\$C\$35,"Psh",\$B\$4:\$B\$35,">=10")
Press CTRL+SHIFT+ENTER to enter array formulas.

#### gtd526

##### Active Member
Here's what Im working with:
NFL.xlsm
AXAYAZBABBBCBDBEBFBG
3ATSATS
46 ATSATS Cover % >= 60%
5CurrentTotalCurrentTotal
6W:0W:952.9%W:0W:939.1%
7L:0L:8L:0L:14
8Psh:0Psh:0Psh:0Psh:0
9Previous17Previous23
10W:9 \$ 40.95 W:9 \$ 40.95
11L:8 \$ 40.00 \$ 0.95 L:14 \$ 70.00 \$ (29.05)
12Psh:0Psh:0
135 ATSMOV > ATS, IFF ATS<0
14CurrentTotalCurrentTotal
15W:0W:527.8%W:0W:428.6%
16L:0L:13L:0L:10
17Psh:0Psh:0Psh:Psh:0
18Previous18Previous14
19W:5 \$ 22.75 W:4 \$ 18.20
20L:13 \$ 65.00 \$ (42.25)L:10 \$ 50.00 \$ (31.80)
21Psh:0Psh:0
224 ATSATS <= -7
23CurrentTotalCurrentTotal
24W:0W:872.7%W:0W:433.3%
25L:0L:3L:0L:8
26Psh:0Psh:0Psh:0Psh:0
27Previous11Previous12
28W:8 \$ 36.40 W:4 \$ 18.20
29L:3 \$ 15.00 \$ 21.40 L:8 \$ 40.00 \$ (21.80)
30Psh:0Psh:0
313 ATSATS >= 10
32CurrentTotalCurrentTotal
33W:1W:6.546.4%W:0W:266.7%
34L:1L:6.5L:0L:1
35Psh:0Psh:1Psh:0Psh:0
36Previous14Previous3
37W:5.5 \$ 25.03 W:2 \$ 9.10
38L:5.5 \$ 27.50 \$ (2.48)L:1 \$ 5.00 \$ 4.10
39Psh:1Psh:0
Weekly Picks
Cell Formulas
RangeFormula
BB6,BG33,BB33,BG24,BB24,BG15,BB15,BG6BB6=BA6/BA9
AY6AY6=COUNTIFS(\$AT\$4:\$AT\$35,"6",\$C\$4:\$C\$35,"W")+(\$AY\$8*0.5)
AY7AY7=COUNTIFS(\$AT\$4:\$AT\$35,"6",\$C\$4:\$C\$35,"L")+(\$AY\$8*0.5)
AY8AY8=COUNTIFS(\$AT\$4:\$AT\$35,"6",\$C\$4:\$C\$35,"Psh")
BD6BD6=COUNTIFS(\$C\$4:\$C\$35,"W",\$U\$4:\$U\$35,">=.6")
BD7BD7=COUNTIFS(\$C\$4:\$C\$35,"L",\$U\$4:\$U\$35,">=.6")
BD8BD8=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:BA7BA6=AY10+AY6
BA8,BF17,BF8BA8=AY13+AY8
BA9,BF36,BA36,BF27,BA27,BF18,BA18,BF9BA9=SUM(BA6:BA8)
AZ10,BE37,AZ37,AZ28,BE19,AZ19,BE10AZ10=AY10*4.55
AZ11,BE38,AZ38,AZ29,BE20,AZ20,BE11AZ11=AY11*5
BA11,BF38,BA38,BF29,BA29,BF20,BA20,BF11BA11=AZ10-AZ11
BD15BD15=SUMPRODUCT((V4:V35>=IFERROR(B4:B35*-1,0))*(C4:C35="W")*(B4:B35<0))
BD16BD16=SUMPRODUCT((V4:V35>=IFERROR(B4:B35*-1,0))*(C4:C35="L")*(B4:B35<0))
AY15AY15=COUNTIFS(\$AT\$4:\$AT\$35,"5",\$C\$4:\$C\$35,"W")+(\$AY\$17*0.5)
AY16AY16=COUNTIFS(\$AT\$4:\$AT\$35,"5",\$C\$4:\$C\$35,"L")+(\$AY\$17*0.5)
AY17AY17=COUNTIFS(\$AT\$4:\$AT\$35,"5",\$C\$4:\$C\$35,"Psh")
AY24AY24=COUNTIFS(\$AT\$4:\$AT\$35,"4",\$C\$4:\$C\$35,"W")+(\$AY\$26*0.5)
AY25AY25=COUNTIFS(\$AT\$4:\$AT\$35,"4",\$C\$4:\$C\$35,"L")+(\$AY\$26*0.5)
AY26AY26=COUNTIFS(\$AT\$4:\$AT\$35,"4",\$C\$4:\$C\$35,"Psh")
BD24BD24=COUNTIFS(\$C\$4:\$C\$35,"W",\$B\$4:\$B\$35,"<=-7")
BD25BD25=COUNTIFS(\$C\$4:\$C\$35,"L",\$B\$4:\$B\$35,"<=-7")
BD26BD26=COUNTIFS(\$C\$4:\$C\$35,"Psh",\$B\$4:\$B\$35,"<=-7")
BA26BA26=AY32+AY26
BE28BE28=BF24*4.55
BE29BE29=BF25*5
AY33AY33=COUNTIFS(\$AT\$4:\$AT\$35,"3",\$C\$4:\$C\$35,"W")+(\$AY\$35*0.5)
AY34AY34=COUNTIFS(\$AT\$4:\$AT\$35,"3",\$C\$4:\$C\$35,"L")+(\$AY\$35*0.5)
AY35AY35=COUNTIFS(\$AT\$4:\$AT\$35,"3",\$C\$4:\$C\$35,"Psh")
BD33BD33=COUNTIFS(\$C\$4:\$C\$35,"W",\$B\$4:\$B\$35,">=10")
BD34BD34=COUNTIFS(\$C\$4:\$C\$35,"L",\$B\$4:\$B\$35,">=10")
BD35BD35=COUNTIFS(\$C\$4:\$C\$35,"Psh",\$B\$4:\$B\$35,">=10")
Press CTRL+SHIFT+ENTER to enter array formulas.
I did a small test:
NFL.xlsm
XYZAK
39ONE1TWO2
40THREE3FOUR4
41FIVE5SIX6
42
43MAX=6
44Offset=#VALUE!
Weekly Picks
Cell Formulas
RangeFormula
Y43Y43=MAX(List)
Y44Y44=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
NameRefers ToCells
List='Weekly Picks'!\$Y\$39,'Weekly Picks'!\$Y\$40,'Weekly Picks'!\$Y\$41,'Weekly Picks'!\$AK\$39,'Weekly Picks'!\$AK\$40,'Weekly Picks'!\$AK\$41Y43:Y44

#### pgc01

##### MrExcel MVP
Thanks for the small test.

The name you posted is very different from the one I posted
I defined the named array:

Name: CellsList
Refers to: ={"BB6","BB15","BB24","BG6","BG15","BG24","BB33","BG33"}

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.

Replies
9
Views
125
Replies
1
Views
107
Replies
7
Views
88
Replies
12
Views
153
Replies
1
Views
59

1,127,857
Messages
5,627,278
Members
416,236
Latest member
Lynchbox

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back