Offset from MAX value

gtd526

Active Member
Joined
Jul 30, 2013
Messages
334
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Excel Can Read to You
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
Joined
Jul 30, 2013
Messages
334
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Apr 25, 2006
Messages
19,884
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
 
Solution

gtd526

Active Member
Joined
Jul 30, 2013
Messages
334
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Apr 25, 2006
Messages
19,884

ADVERTISEMENT

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
 

gtd526

Active Member
Joined
Jul 30, 2013
Messages
334
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

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
Joined
Jul 30, 2013
Messages
334
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Jul 30, 2013
Messages
334
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Apr 25, 2006
Messages
19,884
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top