max value & max value, finding the best

gtd526

Active Member
Joined
Jul 30, 2013
Messages
342
Office Version
  1. 2019
Platform
  1. Windows
Hello,
How can you find the max value in AA and max value in AD to return AB that has the highest rank for each column (AA & AD)?
Not sure if Im stating it correctly, but I know it exists.
Thank you.


Cell Formulas
RangeFormula
AA13AA13=MAX(IF($Y$9:$AP$9,$Y$9:$AP$9,""))
AB13:AB19AB13=INDEX($Y$1:$AP$1,MATCH(AA13,$Y$9:$AP$9,0))
AC13:AC19AC13=INDEX($Y$6:$AP$6,MATCH(AA13,$Y$9:$AP$9,0))
AD13AD13=OFFSET($X$1,MATCH(W5,$W$2:$W$9,0),MATCH(AB13,$Y$1:$AP$1,0))
AA14:AA19AA14=MAX(IF($Y$9:$AP$9<AA13,$Y$9:$AP$9,""))
AD14:AD19AD14=OFFSET($X$1,MATCH($Z$12,$W$2:$W$9,0),MATCH(AB14,$Y$1:$AP$1,0))
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

gtd526

Active Member
Joined
Jul 30, 2013
Messages
342
Office Version
  1. 2019
Platform
  1. Windows
I'm sure we can help you trap it ...

... but can you point us to the result we're chasing?
thank you for responding.
looking for the best (top result) of AB using the results of AA($) and AD(%).

using 2 columns(AA & AD) to come up with a winner(AB).
AB with the highest rank in AA & AD combined.
??taking the highest rank in AA&AD for AB??

There is a mathematical name for this, but I cant recall.
hope this makes 'alittle' sense. the range will expand, just looking for the correct formula or macro.
 

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,124
Office Version
  1. 365
Platform
  1. Windows
I can see that the product of columns AA and AD matches your ranks (from 1 to 7) in column AB.

Beyond that, I'm no clearer what these numbers represent (the formulae refer to cells you haven't provided) or what result(s) you're looking for?

Unless someone else has any insights, it's a little hard for us to provide help.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Hi,

In your sample above, according to your description/requirements, Is there a Winner?
If so, which one? and Why?
If not, Why not?
 

gtd526

Active Member
Joined
Jul 30, 2013
Messages
342
Office Version
  1. 2019
Platform
  1. Windows
Hello,
In the following sample, how can I find the highest rank Criteria using % and GL?
Maybe using the highest rank % and highest rank GL to figure the highest rank Criteria.

Wagers.xlsm
WXY
16%G/LCriteria
1765%$9.10R:T-W
1823%$12.40R:V-Y:Z
1955%$3.50R:V-Q-W
2084%$8.56R:V
2165%$10.40R:Z
2225%$6.05R:T-W-Q
2354%$9.50R:T-W-Q-M
NBA
 
Last edited by a moderator:

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,124
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

You still haven't told us what calculation you want, or the required result, so this is purely a guess.

ABCD
1%G/LCriteriaRank?
265%$9.10R:T-W3
323%$12.40R:V-Y:Z5
455%$3.50R:V-Q-W6
584%$8.56R:V1
665%$10.40R:Z2
725%$6.05R:T-W-Q7
854%$9.50R:T-W-Q-M4
Sheet1
Cell Formulas
RangeFormula
D2:D8D2=SUMPRODUCT(--(A$2:A$8*B$2:B$8>=A2*B2))

Does this provide the ranks you're looking for?
 
Solution

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
@StephenCrump , honestly, I'm not even going to "guess", as OP has made No attempt to clarify his question/requirements as requested multiple times, Post #2, #4, #5.
 

gtd526

Active Member
Joined
Jul 30, 2013
Messages
342
Office Version
  1. 2019
Platform
  1. Windows
You still haven't told us what calculation you want, or the required result, so this is purely a guess.

ABCD
1%G/LCriteriaRank?
265%$9.10R:T-W3
323%$12.40R:V-Y:Z5
455%$3.50R:V-Q-W6
584%$8.56R:V1
665%$10.40R:Z2
725%$6.05R:T-W-Q7
854%$9.50R:T-W-Q-M4
Sheet1
Cell Formulas
RangeFormula
D2:D8D2=SUMPRODUCT(--(A$2:A$8*B$2:B$8>=A2*B2))

Does this provide the ranks you're looking for?
I believe it does. I just wanted to compare % and $ to figure the best criteria. its not the max % or max $, but the best of both.
 

gtd526

Active Member
Joined
Jul 30, 2013
Messages
342
Office Version
  1. 2019
Platform
  1. Windows
You still haven't told us what calculation you want, or the required result, so this is purely a guess.

ABCD
1%G/LCriteriaRank?
265%$9.10R:T-W3
323%$12.40R:V-Y:Z5
455%$3.50R:V-Q-W6
584%$8.56R:V1
665%$10.40R:Z2
725%$6.05R:T-W-Q7
854%$9.50R:T-W-Q-M4
Sheet1
Cell Formulas
RangeFormula
D2:D8D2=SUMPRODUCT(--(A$2:A$8*B$2:B$8>=A2*B2))

Does this provide the ranks you're looking for?
Thank you. thx for your patience!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,427
Messages
5,642,058
Members
417,252
Latest member
selbysam

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