Bowling Stats

Posted by James on June 29, 2001 12:22 PM

I am creating a bowling stat sheet where the row contains a name, and three game scores, with multiple iterances of the data on fidefferent rows to represent the dates bowled. I need to review the multiple rows of detail to find the high game and high series for a specific bowler over all rows as recorded. I need somethnig like the MAX() function, but with it to be able to qualify a match of a bowler name and then of the many games that would be recorded.

Game1 Game2 Game3 Series Game
Bowler1 100 200 130 430 200
Bowler2 130 140 150 420 150
Bowler1 190 180 170 540 190
Bowler2 110 120 130 360 130

Bowler1 High Game = 200
Bowler2 Hugh Game = 150

Bowler1 High Series = 430
Bowler2 High Series = 420

Thanks,

J Savery

Posted by Aladin Akyurek on June 29, 2001 12:51 PM

James,

I'll assume your sample data to be in A1:F5, labels included.

Enter names of the distinct bowlers in H from H2 on. Enter "High series" I1 and "High Game" in J1.

In I2 array-enter: =MAX(($A$2:$A$5=$H2)*(E$2:E$5)) [ Hit CONTROL+SHIFT+ENTER at the same time (not just ENTER) in order to enter this formula ]

Copy this formula first to J2 then down.

You should get in H1:J3 the following based on sample data as result:

{0,"High Series","High Game";"Bowler1",540,200;"Bowler2",420,150}

Note. 0 means a blank cell.

Aladin

===============

Posted by IML on June 29, 2001 12:51 PM

I assumed your data was in cells B2:F5 was header in col A and row 1.

Use =MAX(($A$2:$A$5=A9)*($F$2:$F$5)) for high score, with the bowler name being entered in cell A9

be sure to hit enter while control and shift and entered for this array formula to work.

For the high series, array enter the following.
=MAX(($A$2:$A$5=A12)*($E$2:$E$5))

Also, Bowler one high series should be 540, right?

good luck

Posted by lenze on June 29, 2001 12:52 PM

You might look at the DMAX function

Posted by James on July 02, 2001 6:50 AM

: Game1 Game2 Game3 Series Game : Bowler1 100 200 130 430 200 : Bowler2 130 140 150 420 150 : Bowler1 190 180 170 540 190 : Bowler2 110 120 130 360 130 : Bowler2 Hugh Game = 150 : Bowler2 High Series = 420

Aladin,

Great tip!!! Works well. I am curious how to see the actual data the array creates, as well as doesn't the * character mean to multiply, or is this a special char within an array instruction.

Thanks again.

J Savery

Posted by Aladin Akyurek on July 02, 2001 7:51 AM

James,

The * sign is simply the multiplication operator. To see what an (array) formula computes, go the formula bar, select a term (a well-delimited part) and hit F9. You can do this for each term of a formula to make its computations visible. At the end, you should give an escape, otherwise you'd loose the original formula.

Aladin

================ : James,