max value & max value, finding the best

gtd526

Well-known Member
Joined
Jul 30, 2013
Messages
657
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

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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.
 
Upvote 0
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.
 
Upvote 0
Hi,

In your sample above, according to your description/requirements, Is there a Winner?
If so, which one? and Why?
If not, Why not?
 
Upvote 0
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:
Upvote 0
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?
 
Upvote 0
Solution
@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.
 
Upvote 0
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.
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

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