max value & max value, finding the best

[gtd526]

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.

Wagers.xlsm
	AA	AB	AC	AD
12	G/L $$	What (Rank)	# Bets	%
13	$83.80	R:V (1)	52	69%
14	$52.85	U:V-Y (2)	41	66%
15	$36.90	M:O - R:V (3)	27	67%
16	$33.70	R:V - X:Z (4)	20	70%
17	$19.65	M:O - R:T (5)	40	58%
18	$7.80	ATS>0:Pick (6)	29	55%
19	$2.22	M:O (7)	75	53%
NBA

Cell Formulas
Range	Formula
AA13	AA13	=MAX(IF($Y$9:$AP$9,$Y$9:$AP$9,""))
AB13:AB19	AB13	=INDEX($Y$1:$AP$1,MATCH(AA13,$Y$9:$AP$9,0))
AC13:AC19	AC13	=INDEX($Y$6:$AP$6,MATCH(AA13,$Y$9:$AP$9,0))
AD13	AD13	=OFFSET($X$1,MATCH(W5,$W$2:$W$9,0),MATCH(AB13,$Y$1:$AP$1,0))
AA14:AA19	AA14	=MAX(IF($Y$9:$AP$9<AA13,$Y$9:$AP$9,""))
AD14:AD19	AD14	=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.

 

[StephenCrump]

I know it exists.

I'm sure we can help you trap it ...

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

 

[gtd526]

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]

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]

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]

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
	W	X	Y
16	%	G/L	Criteria
17	65%	$9.10	R:T-W
18	23%	$12.40	R:V-Y:Z
19	55%	$3.50	R:V-Q-W
20	84%	$8.56	R:V
21	65%	$10.40	R:Z
22	25%	$6.05	R:T-W-Q
23	54%	$9.50	R:T-W-Q-M
NBA

 

[StephenCrump]

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

	A	B	C	D
1	%	G/L	Criteria	Rank?
2	65%	$9.10	R:T-W	3
3	23%	$12.40	R:V-Y:Z	5
4	55%	$3.50	R:V-Q-W	6
5	84%	$8.56	R:V	1
6	65%	$10.40	R:Z	2
7	25%	$6.05	R:T-W-Q	7
8	54%	$9.50	R:T-W-Q-M	4
Sheet1

Cell Formulas
Range	Formula
D2:D8	D2	=SUMPRODUCT(--(A$2:A$8*B$2:B$8>=A2*B2))

Does this provide the ranks you're looking for?

 

[jtakw]

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

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

	A	B	C	D
1	%	G/L	Criteria	Rank?
2	65%	$9.10	R:T-W	3
3	23%	$12.40	R:V-Y:Z	5
4	55%	$3.50	R:V-Q-W	6
5	84%	$8.56	R:V	1
6	65%	$10.40	R:Z	2
7	25%	$6.05	R:T-W-Q	7
8	54%	$9.50	R:T-W-Q-M	4
Sheet1

Cell Formulas
Range	Formula
D2:D8	D2	=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]

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

	A	B	C	D
1	%	G/L	Criteria	Rank?
2	65%	$9.10	R:T-W	3
3	23%	$12.40	R:V-Y:Z	5
4	55%	$3.50	R:V-Q-W	6
5	84%	$8.56	R:V	1
6	65%	$10.40	R:Z	2
7	25%	$6.05	R:T-W-Q	7
8	54%	$9.50	R:T-W-Q-M	4
Sheet1

Cell Formulas
Range	Formula
D2:D8	D2	=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!