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]

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]

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]

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]

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]

Did a quick test and worked OK

Book1
	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH
1
2	Result
3	Val3
4			Val1					Val11
5
6					0,12					0,1
7
8
9
10
11
12
13			Val2					Val12
14
15					0,03					0,06
16
17
18
19
20
21
22			Val3
23								Val13
24					0,14					0,11
25
26
27
28
29
30
31			Val4					Val14
32
33					0,05					0,04
34
Sheet3

 

[gtd526]

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]

Did a quick test and worked OK

Book1
	AX	AY	AZ	BA	BB	BC	BD	BE	BF	BG	BH
1
2	Result
3	Val3
4			Val1					Val11
5
6					0,12					0,1
7
8
9
10
11
12
13			Val2					Val12
14
15					0,03					0,06
16
17
18
19
20
21
22			Val3
23								Val13
24					0,14					0,11
25
26
27
28
29
30
31			Val4					Val14
32
33					0,05					0,04
34
Sheet3

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.

 

[gtd526]

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:

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

 

[pgc01]

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.