Top numbers for two columns? Intersection?

C

cspengel

Board Regular
Joined
Oct 29, 2022
Messages
173
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
What I am trying to do is the following:

If Column L does not contain "LINES SET TO 0" then select the highest from column M and Column N where they intersect. By intersect I mean where they are the highest for both categories respectively. In the example I show in the xl2bb, I do this with helper columns in AO, AP, and AQ using the top 25 percent for each column. If it is true for both columns, then that is where the intersection lies. My issue with using percentage is that the number of rows being selected changes based on percentage used. I would like the number of rows that result from the intersection to always be static. So for example, If I have the number "40" in range C29, then it will select the 40 highest rows that intersect. Any suggestions would be appreciated!



REVISION36.xlsm
GHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQ
1MVPFLEXFLEXFLEXFLEXMVP SelectionCeiling Projection↑Ownership L|UDak PrescottTom BradyCeeDee LambEzekiel ElliottTony PollardMike EvansChris GodwinLeonard FournetteBrett MaherRussell GageDalton SchultzMichael GallupRyan SuccopDallas CowboysRachaad WhiteJulio JonesTampa Bay BuccaneersNoah BrownCade OttonT.Y. HiltonJake FergusonMalik DavisLines Set To 0
2Dak PrescottTom BradyBrett MaherTampa Bay BuccaneersDalton Schultz1116.38120660.46%01000000101000001000000000FALSEFALSE0
3Dak PrescottTom BradyBrett MaherCade OttonDallas CowboysLINES SET TO 0102.28086610.31%010000001000010000LINES SET TO 00000001FALSEFALSE0
4Dak PrescottTom BradyBrett MaherDalton SchultzDallas Cowboys1116.33098740.50%01000000101001000000000000FALSEFALSE0
5Dak PrescottTom BradyBrett MaherCade OttonDalton SchultzLINES SET TO 0103.92117871.01%010000001010000000LINES SET TO 00000001FALSEFALSE0
6Dak PrescottTom BradyBrett MaherTampa Bay BuccaneersRachaad White1104.97725370.31%01000000100000101000000000FALSEFALSE0
7Dak PrescottTom BradyLeonard FournetteRyan SuccopDallas Cowboys1125.39021080.36%01000001000011000000000000TRUEFALSE0
8Dak PrescottTom BradyLeonard FournetteTampa Bay BuccaneersBrett Maher1123.79672630.76%01000001100000001000000000TRUEFALSE0
9Dak PrescottTom BradyLeonard FournetteTampa Bay BuccaneersRyan Succop1124.95353380.33%01000001000010001000000000TRUEFALSE0
10Dak PrescottTom BradyBrett MaherCade OttonRachaad WhiteLINES SET TO 095.539291140.69%010000001000001000LINES SET TO 00000001FALSEFALSE0
11Dak PrescottTom BradyLeonard FournetteDalton SchultzRyan Succop1125.83025231.18%01000001001010000000000000TRUEFALSE0
12Dak PrescottTom BradyTony PollardCade OttonBrett MaherLINES SET TO 0105.19389940.89%010010001000000000LINES SET TO 00000001FALSEFALSE0
13Dak PrescottTom BradyLeonard FournetteCade OttonBrett MaherLINES SET TO 0117.5083511.66%010000011000000000LINES SET TO 00000001FALSEFALSE0
Worksheet
Cell Formulas
RangeFormula
O2:O13O2=IF(COUNTIF(H2:K2,$A$38)>0,IF(COUNTIF($C$38,">0"),"1","LINES SET TO 0"),0)
P2:P13P2=IF(COUNTIF(H2:K2,$A$39)>0,IF(COUNTIF($C$39,">0"),"1","LINES SET TO 0"),0)
Q2:Q13Q2=IF(COUNTIF(H2:K2,$A$40)>0,IF(COUNTIF($C$40,">0"),"1","LINES SET TO 0"),0)
R2:R13R2=IF(COUNTIF(H2:K2,$A$41)>0,IF(COUNTIF($C$41,">0"),"1","LINES SET TO 0"),0)
S2:S13S2=IF(COUNTIF(H2:K2,$A$42)>0,IF(COUNTIF($C$42,">0"),"1","LINES SET TO 0"),0)
T2:T13T2=IF(COUNTIF(H2:K2,$A$43)>0,IF(COUNTIF($C$43,">0"),"1","LINES SET TO 0"),0)
U2:U13U2=IF(COUNTIF(H2:K2,$A$44)>0,IF(COUNTIF($C$44,">0"),"1","LINES SET TO 0"),0)
V2:V13V2=IF(COUNTIF(H2:K2,$A$45)>0,IF(COUNTIF($C$45,">0"),"1","LINES SET TO 0"),0)
W2:W13W2=IF(COUNTIF(H2:K2,$A$46)>0,IF(COUNTIF($C$46,">0"),"1","LINES SET TO 0"),0)
X2:X13X2=IF(COUNTIF(H2:K2,$A$47)>0,IF(COUNTIF($C$47,">0"),"1","LINES SET TO 0"),0)
Y2:Y13Y2=IF(COUNTIF(H2:K2,$A$48)>0,IF(COUNTIF($C$48,">0"),"1","LINES SET TO 0"),0)
Z2:Z13Z2=IF(COUNTIF(H2:K2,$A$49)>0,IF(COUNTIF($C$49,">0"),"1","LINES SET TO 0"),0)
AA2:AA13AA2=IF(COUNTIF(H2:K2,$A$50)>0,IF(COUNTIF($C$50,">0"),"1","LINES SET TO 0"),0)
AB2:AB13AB2=IF(COUNTIF(H2:K2,$A$51)>0,IF(COUNTIF($C$51,">0"),"1","LINES SET TO 0"),0)
AC2:AC13AC2=IF(COUNTIF(H2:K2,$A$52)>0,IF(COUNTIF($C$52,">0"),"1","LINES SET TO 0"),0)
AD2:AD13AD2=IF(COUNTIF(H2:K2,$A$53)>0,IF(COUNTIF($C$53,">0"),"1","LINES SET TO 0"),0)
AE2:AE13AE2=IF(COUNTIF(H2:K2,$A$54)>0,IF(COUNTIF($C$54,">0"),"1","LINES SET TO 0"),0)
AF2:AF13AF2=IF(COUNTIF(H2:K2,$A$55)>0,IF(COUNTIF($C$55,">0"),"1","LINES SET TO 0"),0)
AG2:AG13AG2=IF(COUNTIF(H2:K2,$A$56)>0,IF(COUNTIF($C$56,">0"),"1","LINES SET TO 0"),0)
AH2:AH13AH2=IF(COUNTIF(H2:K2,$A$57)>0,IF(COUNTIF($C$57,">0"),"1","LINES SET TO 0"),0)
AI2:AI13AI2=IF(COUNTIF(H2:K2,$A$58)>0,IF(COUNTIF($C$58,">0"),"1","LINES SET TO 0"),0)
AJ2:AJ13AJ2=IF(COUNTIF(H2:K2,$A$59)>0,IF(COUNTIF($C$59,">0"),"1","LINES SET TO 0"),0)
AK2:AK13AK2=IF(COUNTIF(H2:K2,$A$60)>0,IF(COUNTIF($C$60,">0"),"1","LINES SET TO 0"),0)
AL2:AL13AL2=IF(COUNTIF(H2:K2,$A$61)>0,IF(COUNTIF($C$61,">0"),"1","LINES SET TO 0"),0)
AM2:AM13AM2=IF(COUNTIF(H2:K2,$A$62)>0,IF(COUNTIF($C$62,">0"),"1","LINES SET TO 0"),0)
AN2:AN13AN2=COUNTIF(O2:AM2,"LINES SET TO 0")
AO2:AO13AO2=IF(L2<>"LINES SET TO 0",M2>=PERCENTILE($M$2:$M$358,0.75),FALSE)
AP2:AP13AP2=IF(L2<>"LINES SET TO 0",N2>=PERCENTILE($N$2:$N$358,0.75),FALSE)
AQ2:AQ13AQ2=IF(AO2=TRUE,IF(AP2=TRUE,1,0),0)
L2:L13L2=IF(AN2>0,"LINES SET TO 0",IFERROR(IF(N2>=AGGREGATE(14,6,$N$2:$N$1075/(($G$2:$G$1075=G2)*($AN$2:$AN$1075=0)),VLOOKUP(G2,$A$29:$C$35,3,0)),"x","1"),"LINES SET TO 0"))
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
Basically I need to combine these two formulas

VBA Code: 
=IF(L2<>"LINES SET TO 0",IFERROR(IF(P2>=AGGREGATE(14,6,$P$2:$P$827/($AY$2:$AY$827=0),$C$29),1,0),0))
VBA Code: 
=IF(L2<>"LINES SET TO 0",IFERROR(IF(S2>=AGGREGATE(14,6,$S$2:$S$827/($AY$2:$AY$827=0),$C$29),1,0),0))
 
Upvote 0
cspengel said:
Basically I need to combine these two formulas

VBA Code: 
=IF(L2<>"LINES SET TO 0",IFERROR(IF(P2>=AGGREGATE(14,6,$P$2:$P$827/($AY$2:$AY$827=0),$C$29),1,0),0))
VBA Code: 
=IF(L2<>"LINES SET TO 0",IFERROR(IF(S2>=AGGREGATE(14,6,$S$2:$S$827/($AY$2:$AY$827=0),$C$29),1,0),0))
Click to expand...

See if this helps

Excel Formula: 
=IF(L2<>"LINES SET TO 0",IFERROR(IF(P2>=AGGREGATE(14,6,$P$2:$P$827/($AY$2:$AY$827=0),$C$29),1,0),IFERROR(IF(S2>=AGGREGATE(14,6,$S$2:$S$827/($AY$2:$AY$827=0),$C$29),1,0),0)))
 
Upvote 0
Unfortunately, I am not getting the results I am expecting. Maybe I am thinking about this the wrong way. I have 304 rows in this test. For columns P and S I have the top 25 percent of values highlighted. Only 27 overlap as shown by conditional formatting. However, with your formula in column T which is supposed to select the highest 30, only 13 are matching what the conditional formatting has.

REVISION36.xlsm
GHIJKLMNOPQRST
1MVPFLEXFLEXFLEXFLEXMVP SelectionSalaryPROJFloor ProjectionCeiling Projection↑TargetDepth TotalOwnership L|UMREXCEL
2Daniel JonesSaquon BarkleyKirk CousinsGraham GanoDaniel Bellinger16000079.8845.33281785102.78681211.18552.41%0
3Daniel JonesSaquon BarkleyKirk CousinsNew York GiantsDaniel Bellinger16000073.1446.02183533101.71977885.91554.88%0
4Daniel JonesKirk CousinsDalvin CookIrv Smith Jr.Minnesota Vikings15700070.5253.08487147106.42423476.41562.68%0
5Daniel JonesSaquon BarkleyKirk CousinsIrv Smith Jr.Daniel Bellingerx5650058.8743.00592554101.69440038.705610.26%0
6Daniel JonesJustin JeffersonKirk CousinsIrv Smith Jr.Minnesota Vikings16000093.2455.20809197111.21395068.27562.07%1
7Daniel JonesKirk CousinsDalvin CookGraham GanoK.J. Osborn15950070.1750.79009771108.781696411.46571.79%1
8Daniel JonesKirk CousinsDalvin CookNew York GiantsK.J. Osborn15950068.9755.83735341108.52298866.19573.63%1
9Daniel JonesKirk CousinsDalvin CookK.J. OsbornMinnesota Vikings16000077.2854.73885876107.3441747.59573.54%0
10Daniel JonesKirk CousinsDalvin CookAdam ThielenDaniel Bellingerx5950080.3348.67213225103.55261496.05569.39%0
11Daniel JonesSaquon BarkleyKirk CousinsIrv Smith Jr.Darius Slaytonx6000061.9344.03645885109.57400948.89568.22%1
12Daniel JonesSaquon BarkleyKirk CousinsIrv Smith Jr.Richie James Jr.x5800064.747.59534323111.525994912.345816.67%1
13Daniel JonesKirk CousinsDalvin CookIrv Smith Jr.Adam Thielen15850075.1655.10502261115.26451086.07574.14%1
14Daniel JonesKirk CousinsDalvin CookIrv Smith Jr.K.J. Osbornx5600072.6749.20245624107.70456988.98587.64%1
15Daniel JonesSaquon BarkleyRichie James Jr.T.J. HockensonMinnesota Vikings15950056.437.1882506102.0531194.54571.63%0
16Daniel JonesJustin JeffersonKirk CousinsIrv Smith Jr.K.J. Osborn15900078.8854.73132932110.956655510.84585.90%1
17Daniel JonesKirk CousinsDalvin CookRichie James Jr.Graham Gano15900067.9949.07278693104.554280413.90574.99%0
18Daniel JonesKirk CousinsDalvin CookNew York GiantsRichie James Jr.x5900077.6851.98325461103.53829628.635710.12%0
19Daniel JonesSaquon BarkleyDalvin CookIrv Smith Jr.Isaiah Hodgins15900078.5641.66823369113.17324135.96573.94%1
20Daniel JonesSaquon BarkleyDalvin CookIrv Smith Jr.Darius Slayton15850069.4938.71571106110.87533747.59565.74%1
21Daniel JonesSaquon BarkleyDalvin CookIrv Smith Jr.Richie James Jr.x565008239.83369851107.84996711.045811.64%1
22Daniel JonesJustin JeffersonSaquon BarkleyIrv Smith Jr.K.J. Osborn16000061.7645.93490565114.948443510.46583.23%1
23Daniel JonesKirk CousinsDalvin CookRichie James Jr.Minnesota Vikingsx5950073.7453.90918857104.323369310.03579.85%0
24Daniel JonesJustin JeffersonSaquon BarkleyIrv Smith Jr.Daniel Bellinger15800065.0438.92843187106.66835449.26565.54%0
25Daniel JonesKirk CousinsAdam ThielenIrv Smith Jr.Isaiah Hodgins15500070.1349.95045459103.15464992.26581.78%0
26Daniel JonesKirk CousinsRichie James Jr.T.J. HockensonAdam Thielen16000074.9347.51824433102.04314244.58584.61%0
27Daniel JonesKirk CousinsDalvin CookRichie James Jr.K.J. Osbornx5850081.5949.07979804103.165650212.605928.12%0
28Daniel JonesJustin JeffersonSaquon BarkleyIrv Smith Jr.Richie James Jr.x5950061.6740.86843157115.968346812.90588.99%1
Worksheet
Cell Formulas
RangeFormula
L2:L28L2=IF(AZ2>0,"LINES SET TO 0",IFERROR(IF(S2>=AGGREGATE(14,6,$S$2:$S$827/(($G$2:$G$827=G2)*($AZ$2:$AZ$827=0)),VLOOKUP(G2,$A$29:$C$35,3,0)),"x","1"),"LINES SET TO 0"))
T2:T28T2=IF(L2<>"LINES SET TO 0",IFERROR(IF(P2>=AGGREGATE(14,6,$P$2:$P$827/($AY$2:$AY$827=0),$C$29),1,0),IFERROR(IF(S2>=AGGREGATE(14,6,$S$2:$S$827/($AY$2:$AY$827=0),$C$29),1,0),0)))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
S:SCell Valuetop 25% valuestextNO
P:PCell Valuetop 25% valuestextNO
 
Upvote 0
You must log in or register to reply here.

Similar threads

Asbestos_Jen
  • Question Question
Compare multiple parameters of samples to reference values (Excel 2007)
Replies
3
Views
359
Asbestos_Jen
Asbestos_Jen
C
  • Solved
Formula assistance - identifying full or part counts
Replies
5
Views
266
RobP
R
Z
  • Question Question
Massive Nested IF Statement Help Needed
Replies
1
Views
366
Zutalores
Z
E
  • Solved
Formula not working
Replies
2
Views
316
esmarques
E
I
  • Solved
Current Code taking 10 Hours to run. Need Help to Optimize
Replies
5
Views
512
ItalianPlatinum
I

Forum statistics

Threads
1,223,895
Messages
6,175,257
Members
452,625
Latest member
saadat28

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