# 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.

#### gtd526

##### Active Member
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
I changed the Offset to -11 from -1 due to 'hidden columns'

### Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

#### gtd526

##### Active Member
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.
I only did the small test separately so I used a different name (list).
BC47 contains the internal formula results.

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
40
41
42Good for 1 column#VALUE!
434 ATS#N/A
444#VALUE!
45#VALUE!73%
46
47#VALUE!
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")
AZ43AZ43=OFFSET(INDEX(result1,MATCH(MAX(result1),result1,FALSE)),-2,-4)
AZ44AZ44=LEFT(OFFSET(INDEX(result1,MATCH(MAX(result1),result1,FALSE)),-2,-4),1)
AZ45AZ45=OFFSET(INDEX(result1:result2,MATCH(MAX(result1:result2),result1:result2,FALSE)),0,-1)
BC42,BC44BC42=OFFSET(INDIRECT(INDEX(CellsList,MATCH(MAX(N(INDIRECT(CellsList))),N(INDIRECT(CellsList)),0))),-2,-4)
BC43BC43=OFFSET(INDEX(CellsList,MATCH(MAX(CellsList),CellsList,FALSE)),-2,-4)
BC45BC45=MAX(CellsList)
BC47BC47=INDEX(CellsList,MATCH(MAX(N(INDIRECT(CellsList))),N(INDIRECT(CellsList)),0))
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
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\$33AZ43:AZ45, BC47, BC42:BC45
result1='Weekly Picks'!\$BB:\$BBAZ43:AZ45
result2='Weekly Picks'!\$BG:\$BGAZ45
Cells with Conditional Formatting
CellConditionCell FormatStop If True
BB6,BB15,BB24,BB33,BG6,BG15,BG24,BG33Cell Valuetop 1 valuestextNO
BB33,BB24,BB15,BB6Cell Valuetop 1 valuestextNO
BG33,BG24,BG15,BG6Cell Valuetop 1 bottom valuestextNO
BG33,BG24,BG15,BG6Cell Valuetop 1 valuestextNO
BG33,BG24,BG15,BG6Cell Value<"MAX"textNO
BG33,BG24,BG15,BG6Cell Value>"MIN"textNO
BB33,BB24,BB15,BB6Cell Valuetop 1 bottom valuestextNO
BB33,BB24,BB15,BB6Cell Value>"MIN"textNO
BB33,BB24,BB15,BB6Cell Value<"MAX"textNO
BA38Cell Value<0textNO
BA38Cell Value>0textNO
BB24,BB15,BB6Cell Valuetop 1 bottom valuestextNO
BB24,BB15,BB6Cell Value>"MIN"textNO
BB24,BB15,BB6Cell Value<"MAX"textNO
BA11,BF11,BA20,BF20,BA29,BF29,BA38,BF38Cell Value<0textNO
BA11,BF11,BA20,BF20,BA29,BF29,BA38,BF38Cell Value>0textNO

#### pgc01

##### MrExcel MVP
With your small test using for 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"}

I got:

Book1
XYZAK
39ONE1TWO1
40THREE10FOUR1
41FIVE5SIX1
42
43
44
45
46
47MAX=10
48
49Offset=THREE
Weekly Picks
Cell Formulas
RangeFormula
Y47Y47{=MAX(N(INDIRECT(List)))}
Y49Y49{=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.

#### pgc01

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

#### gtd526

##### Active Member

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.
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?
I realize the hidden columns now. That was only regarding the test.

NFL.xlsm
XYZAK
39ONE1TWO1
40THREE10FOUR1
41FIVE5SIX1
42
43MAX=#VALUE!
44Offset=#VALUE!
45
46MAX=10
47Offset=THREE
Weekly Picks
Cell Formulas
RangeFormula
Y43Y43=MAX(N(INDIRECT(List)))
Y44Y44=OFFSET(INDIRECT(INDEX(List,MATCH(MAX(N(INDIRECT(List))),N(INDIRECT(List)),0))),0,-1)
Y46Y46=MAX(List)
Y47Y47=OFFSET(Y40,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, Y46

#### pgc01

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

#### gtd526

##### Active Member

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.
How do you create it that way?

#### pgc01

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

#### gtd526

##### Active Member
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. I need to change the original range as above. Why does my 'refers to' look like this. Not as an array?
The only reason List and CellsList is an array, because I copied from your previous link. thx.

Replies
1
Views
50
Replies
9
Views
127
Replies
1
Views
108
Replies
7
Views
88
Replies
12
Views
155

1,128,163
Messages
5,629,060
Members
416,363
Latest member
zaveedd

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