Find 2nd Largest, 3rd Largest Value

gtd526

gtd526

Well-known Member
Joined
Jul 30, 2013
Messages
660
Office Version
  1. 2019
Platform
  1. Windows
Hello,
Im using the following array to find the MAX value of a Named Range (NBAGL as array), which is working.
How can I find the 2nd Largest, 3rd Largest value using this array?
{=INDIRECT(INDEX(NBAGL,MATCH(MAX(N(INDIRECT(NBAGL))),N(INDIRECT(NBAGL)),0)))}
thank you.
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
You could just use
Excel Formula: 
=LARGE(NBAGL,1)
The 1 at the end denotes largest, change to 2 for 2nd, etc, or use ROWS(A$1:A1) to make it dynamic as you fill down the sheet. Preferably use the cell that holds the first formula instead of A1 so that it doesn't error if you delete / insert rows.
 
Upvote 0
jasonb75 said:
You could just use
Excel Formula: 
=LARGE(NBAGL,1)
The 1 at the end denotes largest, change to 2 for 2nd, etc, or use ROWS(A$1:A1) to make it dynamic as you fill down the sheet. Preferably use the cell that holds the first formula instead of A1 so that it doesn't error if you delete / insert rows.
Click to expand...
I used your Large formula (AU1, AU2) but receive and error. Can I used the your Formula alone, as I did? I used Formula and Array, but error on both. Im only showing part of the entire sheet, but includes the Formula.

NBA.xlsm
AJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBC
1MAX $$Lime = 4,250,140MAX %Pink = 255,110,1702nd $:#NUM!2nd %:#NUM!
2NRtg/A (Y:Y)# of Bets:150(S,T,U,V,W) S:W# of Bets:123rd $:#NUM!3rd %:#NUM!
3026 026- NRtg/A (Y:Y) $90.4058.7%015015- (S,T,U,V,W) S:W75.0%$25.95
4ATS ONLYml ONLY
5001- MOV>ATS (R:R > 0)008- Asst/Tovr Ratio (V:V)015- (S,T,U,V,W) S:W022- Asst/Tovr Ratio (V:V)
6CurrentTotalCurrentTotalCurrentTotalCurrentTotal
7W:0W:360.0%W:0W:7751.3%W:0W:975.0%W:0W:7852.0%
8L:0L:2L:0L:73L:0L:3L:0L:72
9Psh:0Psh:0Psh:0Psh:2Psh:0Psh:0Psh:0Psh:0
10Previous5Previous150Previous12Previous150
11W:3$ 13.65W:77$ 350.35W:9$ 40.95W:78$ 354.90
12L:2$ 10.00$ 3.65L:73$ 365.00$ (14.65)L:3$ 15.00$ 25.95L:72$ 360.00$ (5.10)
13Psh:0Psh:2Psh:0Psh:0
14002- Ct of 9009- Asst/FGM (W:W)016- (S,T,U) S:U023- Asst/FGM (W:W)
15CurrentTotalCurrentTotalCurrentTotalCurrentTotal
16W:0W:1045.5%W:0W:8455.6%W:0W:3357.9%W:0W:8053.0%
17L:0L:12L:0L:67L:0L:24L:0L:71
18Psh:0Psh:0Psh:0Psh:2Psh:0Psh:0Psh:0Psh:0
19Previous22Previous151Previous57Previous151
20W:10$ 45.50W:84$ 382.20W:33$ 150.15W:80$ 364.00
21L:12$ 60.00$ (14.50)L:67$ 335.00$ 47.20L:24$ 120.00$ 30.15L:71$ 355.00$ 9.00
22Psh:0Psh:2Psh:0Psh:0
Favs
Cell Formulas
RangeFormula
AJ2AJ2=TRIM(MID($AK$3,FIND("-",$AK$3)+1,99))
AM2AM2=OFFSET(INDIRECT(INDEX(NBAGL,MATCH(MAX(N(INDIRECT(NBAGL))),N(INDIRECT(NBAGL)),0))),-2,0)
AO2AO2=TRIM(MID($AP$3,FIND("-",$AP$3)+1,99))
AR2AR2=OFFSET(INDIRECT(INDEX(NBAMaxRange,MATCH(MAX(N(INDIRECT(NBAMaxRange))),N(INDIRECT(NBAMaxRange)),0))),3,-1)
AU1AU1=LARGE(NBAGL,2)
AU2AU2=LARGE(NBAGL,3)
AW1AW1=LARGE(NBAMaxRange,2)
AW2AW2=LARGE(NBAMaxRange,3)
AJ3,AO3AJ3=IF(LEN(LEFT(AK3,SEARCH("-",AK3)-1))=3,LEFT(AK3,3),LEFT(AK3,1))
AK3AK3=OFFSET(INDIRECT(INDEX(NBAGL,MATCH(MAX(N(INDIRECT(NBAGL))),N(INDIRECT(NBAGL)),0))),-7,-3)
AL3AL3=INDIRECT(INDEX(NBAGL,MATCH(MAX(N(INDIRECT(NBAGL))),N(INDIRECT(NBAGL)),0)))
AM3AM3=OFFSET(INDIRECT(INDEX(NBAGL,MATCH(MAX(N(INDIRECT(NBAGL))),N(INDIRECT(NBAGL)),0))),-5,1)
AP3AP3=OFFSET(INDIRECT(INDEX(NBAMaxRange,MATCH(MAX(N(INDIRECT(NBAMaxRange))),N(INDIRECT(NBAMaxRange)),0))),-2,-4)
AQ3AQ3=INDIRECT(INDEX(NBAMaxRange,MATCH(MAX(N(INDIRECT(NBAMaxRange))),N(INDIRECT(NBAMaxRange)),0)))
AR3AR3=OFFSET(INDIRECT(INDEX(NBAMaxRange,MATCH(MAX(N(INDIRECT(NBAMaxRange))),N(INDIRECT(NBAMaxRange)),0))),5,-1)
AN7,BC16,AX16,AS16,AN16,BC7,AX7,AS7AN7=IFERROR((AM7/AM10),"")
AK7AK7=COUNTIFS($R$4:$R$29,">0",$D$4:$D$29,"W")+(AK9*0.5)
AK8AK8=COUNTIFS($R$4:$R$29,">0",$D$4:$D$29,"L")+(AK9*0.5)
AK9AK9=COUNTIFS($R$4:$R$29,">0",$D$4:$D$29,"Psh")
AP7AP7=SUMPRODUCT(($D$4:$D$29="W")*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1))))+(AP9*0.5)
AP8AP8=SUMPRODUCT(($D$4:$D$29="L")*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1))))+(AP9*0.5)
AP9AP9=SUMPRODUCT(($D$4:$D$29="Psh")*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1))))
AU7AU7=SUMPRODUCT(($D$4:$D$29="W")*($S$4:$S$29=SUBTOTAL(4,OFFSET($S$4,ROW($S$4:$S$29)-ROW($S$4),,IF(MOD(ROW($S$4:$S$29),2),-2,2),1)))*($T$4:$T$29=SUBTOTAL(4,OFFSET($T$4,ROW($T$4:$T$29)-ROW($T$4),,IF(MOD(ROW($T$4:$T$29),2),-2,2),1)))*($U$4:$U$29=SUBTOTAL(4,OFFSET($U$4,ROW($U$4:$U$29)-ROW($U$4),,IF(MOD(ROW($U$4:$U$29),2),-2,2),1)))*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1)))*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))+(AU9*0.5)
AU8AU8=SUMPRODUCT(($D$4:$D$29="L")*($S$4:$S$29=SUBTOTAL(4,OFFSET($S$4,ROW($S$4:$S$29)-ROW($S$4),,IF(MOD(ROW($S$4:$S$29),2),-2,2),1)))*($T$4:$T$29=SUBTOTAL(4,OFFSET($T$4,ROW($T$4:$T$29)-ROW($T$4),,IF(MOD(ROW($T$4:$T$29),2),-2,2),1)))*($U$4:$U$29=SUBTOTAL(4,OFFSET($U$4,ROW($U$4:$U$29)-ROW($U$4),,IF(MOD(ROW($U$4:$U$29),2),-2,2),1)))*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1)))*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))+(AU9*0.5)
AU9AU9=SUMPRODUCT(($D$4:$D$29="psh")*($S$4:$S$29=SUBTOTAL(4,OFFSET($S$4,ROW($S$4:$S$29)-ROW($S$4),,IF(MOD(ROW($S$4:$S$29),2),-2,2),1)))*($T$4:$T$29=SUBTOTAL(4,OFFSET($T$4,ROW($T$4:$T$29)-ROW($T$4),,IF(MOD(ROW($T$4:$T$29),2),-2,2),1)))*($U$4:$U$29=SUBTOTAL(4,OFFSET($U$4,ROW($U$4:$U$29)-ROW($U$4),,IF(MOD(ROW($U$4:$U$29),2),-2,2),1)))*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1)))*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))
AZ7AZ7=SUMPRODUCT(($C$4:$C$29="W")*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1))))+(AZ9*0.5)
AZ8AZ8=SUMPRODUCT(($C$4:$C$29="L")*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1))))+(AZ9*0.5)
AZ9AZ9=SUMPRODUCT(($C$4:$C$29="Psh")*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1))))
BB16:BB17,AW16:AW17,AR16:AR17,AM16:AM17,BB7:BB8,AW7:AW8,AR7:AR8,AM7:AM8AM7=AK11+AK7
AM9,BB18,AW18,AR18,AM18,BB9,AW9,AR9AM9=AK9+AK13
AM10,BB19,AW19,AR19,AM19,BB10,AW10,AR10AM10=AM7+AM8
AL11,BA20,AV20,AQ20,AL20,BA11,AV11,AQ11AL11=AK11*4.55
AL12,BA21,AV21,AQ21,AL21,BA12,AV12,AQ12AL12=AK12*5
AM12,BB21,AW21,AR21,AM21,BB12,AW12,AR12AM12=AL11-AL12
AK16AK16=COUNTIFS(AD4:AD29,"9",D4:D29,"W")+(AK18*0.5)
AK17AK17=COUNTIFS(AD4:AD29,"9",D4:D29,"L")+(AK18*0.5)
AK18AK18=COUNTIFS(AD4:AD29,"9",D4:D29,"Psh")
AP16AP16=SUMPRODUCT(($D$4:$D$29="W")*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))+(AP18*0.5)
AP17AP17=SUMPRODUCT(($D$4:$D$29="L")*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))+(AP18*0.5)
AP18AP18=SUMPRODUCT(($D$4:$D$29="psh")*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))
AU16AU16=SUMPRODUCT(($D$4:$D$29="W")*($S$4:$S$29=SUBTOTAL(4,OFFSET($S$4,ROW($S$4:$S$29)-ROW($S$4),,IF(MOD(ROW($S$4:$S$29),2),-2,2),1)))*($T$4:$T$29=SUBTOTAL(4,OFFSET($T$4,ROW($T$4:$T$29)-ROW($T$4),,IF(MOD(ROW($T$4:$T$29),2),-2,2),1)))*($U$4:$U$29=SUBTOTAL(4,OFFSET($U$4,ROW($U$4:$U$29)-ROW($U$4),,IF(MOD(ROW($U$4:$U$29),2),-2,2),1))))+(AU18*0.5)
AU17AU17=SUMPRODUCT(($D$4:$D$29="L")*($S$4:$S$29=SUBTOTAL(4,OFFSET($S$4,ROW($S$4:$S$29)-ROW($S$4),,IF(MOD(ROW($S$4:$S$29),2),-2,2),1)))*($T$4:$T$29=SUBTOTAL(4,OFFSET($T$4,ROW($T$4:$T$29)-ROW($T$4),,IF(MOD(ROW($T$4:$T$29),2),-2,2),1)))*($U$4:$U$29=SUBTOTAL(4,OFFSET($U$4,ROW($U$4:$U$29)-ROW($U$4),,IF(MOD(ROW($U$4:$U$29),2),-2,2),1))))+(AU18*0.5)
AU18AU18=SUMPRODUCT(($D$4:$D$29="psh")*($S$4:$S$29=SUBTOTAL(4,OFFSET($S$4,ROW($S$4:$S$29)-ROW($S$4),,IF(MOD(ROW($S$4:$S$29),2),-2,2),1)))*($T$4:$T$29=SUBTOTAL(4,OFFSET($T$4,ROW($T$4:$T$29)-ROW($T$4),,IF(MOD(ROW($T$4:$T$29),2),-2,2),1)))*($U$4:$U$29=SUBTOTAL(4,OFFSET($U$4,ROW($U$4:$U$29)-ROW($U$4),,IF(MOD(ROW($U$4:$U$29),2),-2,2),1))))
AZ16AZ16=SUMPRODUCT(($C$4:$C$29="W")*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))+(AZ18*0.5)
AZ17AZ17=SUMPRODUCT(($C$4:$C$29="L")*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))+(AZ18*0.5)
AZ18AZ18=SUMPRODUCT(($C$4:$C$29="psh")*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Upvote 0
If LARGE(NBAGL,2) is returning a #NUM! error then it means that NBAGL only contains 1 number.
 
Upvote 0
jasonb75 said:
If LARGE(NBAGL,2) is returning a #NUM! error then it means that NBAGL only contains 1 number.
Click to expand...
NBAGL contains 28+ cells formatted as Accounting. The results of AK3 and AP3 change constantly, with no errors, returning the MAX value. For the Large formula to work, do all cells have to be formatted as Number? If so, I may need a different (Large) formula/array.
 
Upvote 0
Valid numbers will work but not if they are in a text format. Accounting, currency, date, time, percentage, etc will all work.

How is the ranged defined?
 
Upvote 0
jasonb75 said:
Valid numbers will work but not if they are in a text format. Accounting, currency, date, time, percentage, etc will all work.

How is the ranged defined?
Click to expand...
Named Range (NBAGL)=
={"AM12","AM21","AM30","AM39","AM48","AM57","AM66","AR12","AR21","AR30","AR39","AR48","AR57","AR66","AW12","AW21","AW30","AW39","AW48","AW57","AW66","BB12","BB21","BB30","BB30","BB30","BB39","BB48","BB57","BB66","BE30","BE39","BE48","BE57","BE66"}

If this is what your asking to see.
 
Upvote 0
That's the important bit, I was wondering why you had used INDIRECT in the original formula, it makes sense now. See if this one works.
Excel Formula: 
=LARGE(INDIRECT(NBAGL,1))
If not then we might have to modify it in a similar way to the original.
 
Upvote 0
jasonb75 said:
That's the important bit, I was wondering why you had used INDIRECT in the original formula, it makes sense now. See if this one works.
Excel Formula: 
=LARGE(INDIRECT(NBAGL,1))
If not then we might have to modify it in a similar way to the original.
Click to expand...
I entered the formula, but it said I had too few arguments. So I changed it to:
LARGE(INDIRECT(NBAGL,3),1), as a formula and an array (AU1, AU2). But its giving me the same result. It seems to bring back the 1st value in Named Range.

NBA.xlsm
AJAKALAMANAOAPAQARASATAUAVAWAXAYAZBABBBC
1MAX $$Lime = 4,250,140MAX %Pink = 255,110,1702nd $:3.652nd %:#NUM!
2NRtg/A (Y:Y)# of Bets:150(S,T,U,V,W) S:W# of Bets:123rd $:3.653rd %:#NUM!
3026 026- NRtg/A (Y:Y) $90.4058.7%015015- (S,T,U,V,W) S:W75.0%$25.95
4ATS ONLYml ONLY
5001- MOV>ATS (R:R > 0)008- Asst/Tovr Ratio (V:V)015- (S,T,U,V,W) S:W022- Asst/Tovr Ratio (V:V)
6CurrentTotalCurrentTotalCurrentTotalCurrentTotal
7W:0W:360.0%W:0W:7751.3%W:0W:975.0%W:0W:7852.0%
8L:0L:2L:0L:73L:0L:3L:0L:72
9Psh:0Psh:0Psh:0Psh:2Psh:0Psh:0Psh:0Psh:0
10Previous5Previous150Previous12Previous150
11W:3$ 13.65W:77$ 350.35W:9$ 40.95W:78$ 354.90
12L:2$ 10.00$ 3.65L:73$ 365.00$ (14.65)L:3$ 15.00$ 25.95L:72$ 360.00$ (5.10)
13Psh:0Psh:2Psh:0Psh:0
14002- Ct of 9009- Asst/FGM (W:W)016- (S,T,U) S:U023- Asst/FGM (W:W)
15CurrentTotalCurrentTotalCurrentTotalCurrentTotal
16W:0W:1045.5%W:0W:8455.6%W:0W:3357.9%W:0W:8053.0%
17L:0L:12L:0L:67L:0L:24L:0L:71
18Psh:0Psh:0Psh:0Psh:2Psh:0Psh:0Psh:0Psh:0
19Previous22Previous151Previous57Previous151
20W:10$ 45.50W:84$ 382.20W:33$ 150.15W:80$ 364.00
21L:12$ 60.00$ (14.50)L:67$ 335.00$ 47.20L:24$ 120.00$ 30.15L:71$ 355.00$ 9.00
22Psh:0Psh:2Psh:0Psh:0
Favs
Cell Formulas
RangeFormula
AJ2AJ2=TRIM(MID($AK$3,FIND("-",$AK$3)+1,99))
AM2AM2=OFFSET(INDIRECT(INDEX(NBAGL,MATCH(MAX(N(INDIRECT(NBAGL))),N(INDIRECT(NBAGL)),0))),-2,0)
AO2AO2=TRIM(MID($AP$3,FIND("-",$AP$3)+1,99))
AR2AR2=OFFSET(INDIRECT(INDEX(NBAMaxRange,MATCH(MAX(N(INDIRECT(NBAMaxRange))),N(INDIRECT(NBAMaxRange)),0))),3,-1)
AU1AU1=LARGE(INDIRECT(NBAGL,2),1)
AU2AU2=LARGE(INDIRECT(NBAGL,3),1)
AW1AW1=LARGE(NBAMaxRange,2)
AW2AW2=LARGE(NBAMaxRange,3)
AJ3,AO3AJ3=IF(LEN(LEFT(AK3,SEARCH("-",AK3)-1))=3,LEFT(AK3,3),LEFT(AK3,1))
AK3AK3=OFFSET(INDIRECT(INDEX(NBAGL,MATCH(MAX(N(INDIRECT(NBAGL))),N(INDIRECT(NBAGL)),0))),-7,-3)
AL3AL3=INDIRECT(INDEX(NBAGL,MATCH(MAX(N(INDIRECT(NBAGL))),N(INDIRECT(NBAGL)),0)))
AM3AM3=OFFSET(INDIRECT(INDEX(NBAGL,MATCH(MAX(N(INDIRECT(NBAGL))),N(INDIRECT(NBAGL)),0))),-5,1)
AP3AP3=OFFSET(INDIRECT(INDEX(NBAMaxRange,MATCH(MAX(N(INDIRECT(NBAMaxRange))),N(INDIRECT(NBAMaxRange)),0))),-2,-4)
AQ3AQ3=INDIRECT(INDEX(NBAMaxRange,MATCH(MAX(N(INDIRECT(NBAMaxRange))),N(INDIRECT(NBAMaxRange)),0)))
AR3AR3=OFFSET(INDIRECT(INDEX(NBAMaxRange,MATCH(MAX(N(INDIRECT(NBAMaxRange))),N(INDIRECT(NBAMaxRange)),0))),5,-1)
AN7,BC16,AX16,AS16,AN16,BC7,AX7,AS7AN7=IFERROR((AM7/AM10),"")
AK7AK7=COUNTIFS($R$4:$R$29,">0",$D$4:$D$29,"W")+(AK9*0.5)
AK8AK8=COUNTIFS($R$4:$R$29,">0",$D$4:$D$29,"L")+(AK9*0.5)
AK9AK9=COUNTIFS($R$4:$R$29,">0",$D$4:$D$29,"Psh")
AP7AP7=SUMPRODUCT(($D$4:$D$29="W")*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1))))+(AP9*0.5)
AP8AP8=SUMPRODUCT(($D$4:$D$29="L")*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1))))+(AP9*0.5)
AP9AP9=SUMPRODUCT(($D$4:$D$29="Psh")*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1))))
AU7AU7=SUMPRODUCT(($D$4:$D$29="W")*($S$4:$S$29=SUBTOTAL(4,OFFSET($S$4,ROW($S$4:$S$29)-ROW($S$4),,IF(MOD(ROW($S$4:$S$29),2),-2,2),1)))*($T$4:$T$29=SUBTOTAL(4,OFFSET($T$4,ROW($T$4:$T$29)-ROW($T$4),,IF(MOD(ROW($T$4:$T$29),2),-2,2),1)))*($U$4:$U$29=SUBTOTAL(4,OFFSET($U$4,ROW($U$4:$U$29)-ROW($U$4),,IF(MOD(ROW($U$4:$U$29),2),-2,2),1)))*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1)))*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))+(AU9*0.5)
AU8AU8=SUMPRODUCT(($D$4:$D$29="L")*($S$4:$S$29=SUBTOTAL(4,OFFSET($S$4,ROW($S$4:$S$29)-ROW($S$4),,IF(MOD(ROW($S$4:$S$29),2),-2,2),1)))*($T$4:$T$29=SUBTOTAL(4,OFFSET($T$4,ROW($T$4:$T$29)-ROW($T$4),,IF(MOD(ROW($T$4:$T$29),2),-2,2),1)))*($U$4:$U$29=SUBTOTAL(4,OFFSET($U$4,ROW($U$4:$U$29)-ROW($U$4),,IF(MOD(ROW($U$4:$U$29),2),-2,2),1)))*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1)))*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))+(AU9*0.5)
AU9AU9=SUMPRODUCT(($D$4:$D$29="psh")*($S$4:$S$29=SUBTOTAL(4,OFFSET($S$4,ROW($S$4:$S$29)-ROW($S$4),,IF(MOD(ROW($S$4:$S$29),2),-2,2),1)))*($T$4:$T$29=SUBTOTAL(4,OFFSET($T$4,ROW($T$4:$T$29)-ROW($T$4),,IF(MOD(ROW($T$4:$T$29),2),-2,2),1)))*($U$4:$U$29=SUBTOTAL(4,OFFSET($U$4,ROW($U$4:$U$29)-ROW($U$4),,IF(MOD(ROW($U$4:$U$29),2),-2,2),1)))*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1)))*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))
AZ7AZ7=SUMPRODUCT(($C$4:$C$29="W")*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1))))+(AZ9*0.5)
AZ8AZ8=SUMPRODUCT(($C$4:$C$29="L")*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1))))+(AZ9*0.5)
AZ9AZ9=SUMPRODUCT(($C$4:$C$29="Psh")*($V$4:$V$29=SUBTOTAL(4,OFFSET($V$4,ROW($V$4:$V$29)-ROW($V$4),,IF(MOD(ROW($V$4:$V$29),2),-2,2),1))))
BB16:BB17,AW16:AW17,AR16:AR17,AM16:AM17,BB7:BB8,AW7:AW8,AR7:AR8,AM7:AM8AM7=AK11+AK7
AM9,BB18,AW18,AR18,AM18,BB9,AW9,AR9AM9=AK9+AK13
AM10,BB19,AW19,AR19,AM19,BB10,AW10,AR10AM10=AM7+AM8
AL11,BA20,AV20,AQ20,AL20,BA11,AV11,AQ11AL11=AK11*4.55
AL12,BA21,AV21,AQ21,AL21,BA12,AV12,AQ12AL12=AK12*5
AM12,BB21,AW21,AR21,AM21,BB12,AW12,AR12AM12=AL11-AL12
AK16AK16=COUNTIFS(AD4:AD29,"9",D4:D29,"W")+(AK18*0.5)
AK17AK17=COUNTIFS(AD4:AD29,"9",D4:D29,"L")+(AK18*0.5)
AK18AK18=COUNTIFS(AD4:AD29,"9",D4:D29,"Psh")
AP16AP16=SUMPRODUCT(($D$4:$D$29="W")*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))+(AP18*0.5)
AP17AP17=SUMPRODUCT(($D$4:$D$29="L")*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))+(AP18*0.5)
AP18AP18=SUMPRODUCT(($D$4:$D$29="psh")*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))
AU16AU16=SUMPRODUCT(($D$4:$D$29="W")*($S$4:$S$29=SUBTOTAL(4,OFFSET($S$4,ROW($S$4:$S$29)-ROW($S$4),,IF(MOD(ROW($S$4:$S$29),2),-2,2),1)))*($T$4:$T$29=SUBTOTAL(4,OFFSET($T$4,ROW($T$4:$T$29)-ROW($T$4),,IF(MOD(ROW($T$4:$T$29),2),-2,2),1)))*($U$4:$U$29=SUBTOTAL(4,OFFSET($U$4,ROW($U$4:$U$29)-ROW($U$4),,IF(MOD(ROW($U$4:$U$29),2),-2,2),1))))+(AU18*0.5)
AU17AU17=SUMPRODUCT(($D$4:$D$29="L")*($S$4:$S$29=SUBTOTAL(4,OFFSET($S$4,ROW($S$4:$S$29)-ROW($S$4),,IF(MOD(ROW($S$4:$S$29),2),-2,2),1)))*($T$4:$T$29=SUBTOTAL(4,OFFSET($T$4,ROW($T$4:$T$29)-ROW($T$4),,IF(MOD(ROW($T$4:$T$29),2),-2,2),1)))*($U$4:$U$29=SUBTOTAL(4,OFFSET($U$4,ROW($U$4:$U$29)-ROW($U$4),,IF(MOD(ROW($U$4:$U$29),2),-2,2),1))))+(AU18*0.5)
AU18AU18=SUMPRODUCT(($D$4:$D$29="psh")*($S$4:$S$29=SUBTOTAL(4,OFFSET($S$4,ROW($S$4:$S$29)-ROW($S$4),,IF(MOD(ROW($S$4:$S$29),2),-2,2),1)))*($T$4:$T$29=SUBTOTAL(4,OFFSET($T$4,ROW($T$4:$T$29)-ROW($T$4),,IF(MOD(ROW($T$4:$T$29),2),-2,2),1)))*($U$4:$U$29=SUBTOTAL(4,OFFSET($U$4,ROW($U$4:$U$29)-ROW($U$4),,IF(MOD(ROW($U$4:$U$29),2),-2,2),1))))
AZ16AZ16=SUMPRODUCT(($C$4:$C$29="W")*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))+(AZ18*0.5)
AZ17AZ17=SUMPRODUCT(($C$4:$C$29="L")*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))+(AZ18*0.5)
AZ18AZ18=SUMPRODUCT(($C$4:$C$29="psh")*($W$4:$W$29=SUBTOTAL(4,OFFSET($W$4,ROW($W$4:$W$29)-ROW($W$4),,IF(MOD(ROW($W$4:$W$29),2),-2,2),1))))
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Last edited:
Upvote 0
I had suspected that might be the case, your original formula is one of those where the full purpose is not immediately obvious without supporting data and all of the necessary information.

It works for me by adding the N() function, although that may not be the same for excel 2019
Excel Formula: 
=LARGE(N(INDIRECT(NBAGL)),1)
The formula below also works as a non-volatile alternative, although, noting that you are using INDIRECT with the same range in several formulas, only making one non-volatile would not really serve any purpose.
Excel Formula: 
=AGGREGATE(14,6,AM12:BE66/(MOD(ROW(AM12:BE66)-ROW(AM12),9)=0)/(MOD(COLUMN(AM12:BE66)-COLUMN(AM12),5)=0),1)
 
Upvote 0
Solution
You must log in or register to reply here.

Similar threads

E
  • Question
Function to return array of 10 largest absolute values while maintaining sign
Replies
0
Views
98
eurobonds
E
D
  • Solved
Identify top three maximum unique values in a range using VBA
Replies
2
Views
467
DonEB
D
L
  • Solved
Using FILTER in INDIRECT to calculate row value
Replies
1
Views
240
Ludwig
L
N
  • Solved
Vlookup max value and finding number in specific column
Replies
6
Views
826
Joe4
Joe4
G
  • Solved
Find the nth largest value and return the value in another column
2
Replies
10
Views
660
Fluff
Fluff

Forum statistics

Threads
1,215,425
Messages
6,124,825
Members
449,190
Latest member
rscraig11

Share this page

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