# max value & max value, finding the best

#### gtd526

##### Active Member
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))
AA14:AA19AA14=MAX(IF(\$Y\$9:\$AP\$9<AA13,\$Y\$9:\$AP\$9,""))
Press CTRL+SHIFT+ENTER to enter array formulas.

### Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

#### StephenCrump

##### MrExcel MVP
I know it exists.

... but can you point us to the result we're chasing?

#### gtd526

##### Active Member

... 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
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

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

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?

#### jtakw

##### Well-known Member
@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
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
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!

Replies
5
Views
92
Replies
2
Views
52
Replies
6
Views
76
Replies
3
Views
152
Replies
6
Views
302

1,130,220
Messages
5,640,960
Members
417,183
Latest member
CuteLeo

### 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.

### Which adblocker are you using?

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

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