how do i retrieve the largest value from several records/rows in e

[Louis]

i'm trying to retrieve the largest value from a field. the records are many
but i only want to include the records related to a specific person. "DMAX"
seems to be the closest function that would do the job but ...

 

[Max]

Assume your source table in A2:B2 down
names in col A, real numbers in col B
In E2 down are the inputs for col A, ie specific names: Name1, Name2 ..
In F2, array-enter, ie press CTRL+SHIFT+ENTER to confirm the formula:
=MAX(IF(A$2:A$100=E2,B$2:B$100))
F2 will return the max value from col B for the name in E2. Copy F2 down as
required. Adapt the ranges to suit the actual extents. Success? hit the YES
below.

 

[Louis]

Assume your source table in A2:B2 down
names in col A, real numbers in col B
In E2 down are the inputs for col A, ie specific names: Name1, Name2 ..
In F2, array-enter, ie press CTRL+SHIFT+ENTER to confirm the formula:
=MAX(IF(A$2:A$100=E2,B$2:B$100))
F2 will return the max value from col B for the name in E2. Copy F2 down as
required. Adapt the ranges to suit the actual extents. Success? hit the YES
below.

 

[Louis]

Hi Max,

I'm confused by your use of 2 columns (A and E) for names...

Let me clarify my needs:

I'm writing a squash ranking program for our club. Only the winner's ranking
is affected.

....A... ......B...... .C. .....D..... .E. ...... ...M...
....... ...P...
DATE WINNER GW LOSER GL WINNER'S WINNER'S
"CURRENT"
"NEW"
RANKING
RANKING

1/1/10 Rorke 3 McQueen 2 FORMULA
1/1/10 McQueen 3 Roper 2
1/1/10 Bird 3 McQueen 2
1/1/10 Juster 3 Laughlin 2
1/1/10 McQueen 3 Griffith 0
etc.

For a change to take place, the winner's rating must be less than the
loser's rating. If the weaker player wins, then the winner will move ahead of
the loser.The loser's rating does not change. For this reason I need to be
able to find both players' current ratings. Let's just deal with the winner
for now. So I need the winner's current rating for column M. This comes from
column P and is the maximum value achieved by the winner in his previous
matches.

So with the above in mind, would the formula for M30 look like the following?

=MAX(IF($B$2:B29=B30,$P$229))

 

[Max]

I'm confused by your use of 2 columns (A and E) for names...
Col A is presumed to be source data, with names repeated
Col E is where you input the specific names of interest or the unique list
of names (no repeats)

Let's try to understand what your adapted array formula does:
=MAX(IF($B$2:$B$29=B30,$P$2:$P$29))
(I fixed the ranges with $ signs)

It presumes you have source names in B2:B29 (these names may repeat), and
you are comparing these with a specific name of interest in B30. And you want
to return the max number from the corresponding range P229 for that
specific name in B30.

 

[Louis]

Hi Max,
That's exactly right... The repeating player names are in column B and their
ratings/points are in column P. When a match is recorded, all previous
records/matches are searched to find the winning player's best/maximum
rating. This would be his current rating.
MY FORMULA: =MAX(IF($B$2:B29=B30,$P$229)) is without "your" $ signs since
I need to fill column M "Winner's Current Ranking" with this formula and the
2 ranges
$B$2:B29 and $P$229 must grow to include all records/matches prior to the
current one. Right?
I've entered the formula into the column in question but the result was...
#VALUE!
Apparently "a value being used in the formula is of the wrong data type". As
far as I can see my data types are ok. Any ideas?

 

[Max]

It means you have a #value! error in col B's source range. Check & remove the
error, and it should work fine.

 

[Louis]

i'm not sure how to go about finding this error...everything "looks" fine.
the player names were entered using a validation.

 

[Max]

Please check col P as well, there's probably a formula returned/residual
#VALUE! error somewhere in that range. You can apply autofilter to col P to
isolate where these errors are (it'll show up at the bottom in the
autofilter dropdown)

 

[Louis]

Hi Max,
Below are the first 6 rows of my worksheet:

Col. B C D E ..... M ... P

1 WINNER GWw LOSER GLw W'CUR'R W'NEW'R
2 McQueen 3 Rorke 2 0.0000 0.0000
3 McQueen 3 Vowles 0 #VALUE! 1.0000
4 McQueen 3 Bird 0 #VALUE! 2.0000
5 McQueen 3 Rorke 1 #VALUE! 3.0000
6 Vowles 3 McQueen 1 #VALUE! 4.0000

The formula in question starts in row 2 of column M and changes as follows:
m2 formula: =MAX(IF($B$1:B1=B2,$P$11))
m3 formula: =MAX(IF($B$1:B2=B3,$P$12))
m4 formula: =MAX(IF($B$1:B3=B4,$P$13))
m5 formula: =MAX(IF($B$1:B4=B5,$P$14))
m6 formula: =MAX(IF($B$1:B5=B6,$P$15))
m7 formula: =MAX(IF($B$1:B6=B7,$P$16))
etc.
I've tested the formula by changing the values in col P with letters,
numbers, blanks but this does not affect the results in column M. When
tracing the calculation, the #VALUE! error appears upon calculating the first
part of the IF statement ...$B$1:B6=B7...
I've no idea what this implies nor how to proceed...
Louis

 

[Louis]

Hi Max,
Below are the first 6 rows of my worksheet:

Col. B C D E ..... M ... P

1 WINNER GWw LOSER GLw W'CUR'R W'NEW'R
2 McQueen 3 Rorke 2 0.0000 0.0000
3 McQueen 3 Vowles 0 #VALUE! 1.0000
4 McQueen 3 Bird 0 #VALUE! 2.0000
5 McQueen 3 Rorke 1 #VALUE! 3.0000
6 Vowles 3 McQueen 1 #VALUE! 4.0000

The formula in question starts in row 2 of column M and changes as follows:
m2 formula: =MAX(IF($B$1:B1=B2,$P$11))
m3 formula: =MAX(IF($B$1:B2=B3,$P$12))
m4 formula: =MAX(IF($B$1:B3=B4,$P$13))
m5 formula: =MAX(IF($B$1:B4=B5,$P$14))
m6 formula: =MAX(IF($B$1:B5=B6,$P$15))
m7 formula: =MAX(IF($B$1:B6=B7,$P$16))
etc.
I've tested the formula by changing the values in col P with letters,
numbers, blanks but this does not affect the results in column M. When
tracing the calculation, the #VALUE! error appears upon calculating the first
part of the IF statement ...$B$1:B6=B7...
I've no idea what this implies nor how to proceed...
Louis

 

[Max]

I've no idea what this implies nor how to proceed...
The earlier discussions had presumed that you did correctly array-enter the
formula in the top cell before copying down. Re-check this array-entry,
visually look for the curly braces { } in the formula bars of the cells in
M2 down. These curlies are inserted by Excel if the CSE** is correctly done.
If you see the curlies, its fine. If not, then it means that the formula was
NOT array-entered. I know of no other way to check that a formula has been
correctly array-entered.
**CSE = pressing CTRL+SHIFT+ENTER

 

[Max]

Without having to do the arduous re-check of every formula cell in M2 down
(and the re-CSE confirmation for those cells w/o curlies), the easiest way
here is to check/re-do the array-entry in the top cell, verify that the
curlies are there, then drag M2 to copy down all the way.

 

[Louis]

Wow...jackpot!
My formulae did NOT have these curlies. I had simply pressed "enter" as I've
always done. Never ran into this "CSE" before and have not run into array
problems until now. Are you supposed to use this method for all arrays or
just certain instances?
I've followed your steps and everything looks good...THANKS!
Louis.

 

[Max]

Marvellous to hear. Yes, you need to do the CSE confirmation for array
formulas. Most regular responders would point this out in their responses
(the CSE bit). My 1st response in this thread was no exception. If nothing
is mentioned about CSE in the response, then it can be assumed that the
formula can be normally entered (ie just press ENTER)