Offset from MAX value

[gtd526]

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]

I did a small test:

NFL.xlsm
	X	Y	Z											AK
39	ONE	1	TWO											2
40	THREE	3	FOUR											4
41	FIVE	5	SIX											6
42
43	MAX=	6
44	Offset=	#VALUE!
Weekly Picks

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

 

[gtd526]

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

 

[pgc01]

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
	X	Y	Z											AK
39	ONE	1	TWO											1
40	THREE	10	FOUR											1
41	FIVE	5	SIX											1
42
43
44
45
46
47	MAX=	10
48
49	Offset=	THREE
Weekly Picks

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.

 

[pgc01]

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]

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

 

[pgc01]

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]

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]

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]

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.

 

[pgc01]

That fixed the formula.

Great. I'm glad it's working OK!