Golf Anyone

[duchem02]

I have come to realize that I know less about Excel than I thought. Please
help. I am constructing a growing database of golf scores from which I am
performing simple calculations (sum, average, standard deviation) for each
player represented. I add about 150 rows of information (one per player) per
week, then sort it by player name. The problem I am running into is when I
do this is changes the locations of my calculations into my summary table.
This requires me to manually adjust them, taking way to much time. In the
end I will have about 250 players who have played 1 to 20 tournaments. The
length of my raw data will be around 4000 lines.

 

[ryguy7272]

Sounds like a job for Access. If you want to use Excel, you will probably
need some dynamic offsets. Send me the file and I will take a look at it.
Also, give a little more description of what you want to see Excel do; like a
before and after scenario, maybe a little color too...

(e-mail address removed)
remove the 'XXX' part.


Regards,
Ryan--

 

[Spiky]

I have come to realize that I know less about Excel than I thought. Please
help. I am constructing a growing database of golf scores from which I am
performing simple calculations (sum, average, standard deviation) for each
player represented. I add about 150 rows of information (one per player) per
week, then sort it by player name. The problem I am running into is when I
do this is changes the locations of my calculations into my summary table.
This requires me to manually adjust them, taking way to much time. In the
end I will have about 250 players who have played 1 to 20 tournaments. The
length of my raw data will be around 4000 lines.

I don't think you told us enough. What are the calculations that are
getting messed up? Sounds like you need database functions instead of
plain functions. Or SUMIF instead of SUM, stuff like that.

 

[duchem02]

Sorry, I'll try again.

I am trying to get to a summary report that will contain columns labeled
player name, total events, rounds played, average rounds per event, average
place finished, scoring average (rounds 1,2,3,4 and total), minimum score
recorded (rounds 1,2,3,4, and total), maximum score recorded (rounds 1,2,3,4
and total), standard deviation of scores (rounds 1,2,3,4 and total), total
shots made, total winnings and dollars per shot made.

Each week I add the latest tournament results to my raw data columns and
sort by the players name, ending up with each tournament result as a single
line item. I am currently at 7 tournaments meaning there are players with
results from 1 to 7 tournaments. At the end of the sixth week, I sorted all
of the raw data by player name and placed simple calculation in the summary
report.

Example:
Player 1 played in 5 tournaments so their statistics are stored in rows 1-5
Line 1 = tournament 1
Line 2 = tournament 2
Line 3 = tournamnet 3
Line 4 = tournament 4
Line 5 = tournament 5

Player 2 played in 3 tournaments so their statistics are stored in rows 6-8
Line 6 = tournament 1
Line 7 = tournament 2
Line 8 = tournament 5 (if they missed tournaments 3 and 4)

Player 3 played in 6 tournaments so their statistics are stored in rows 9-14
Line 9 = tournament 1
Line 10 = tournament 2
Line 11 = tournament 3
Line 12 = tournament 4
Line 13 = tournament 5
Line 14 = tournament 6

.......this continues for about 200 players.


AFTER ADDING IN TOURNAMENT #7...

Player 1 played in 6 tournaments so their statistics are stored in rows 1-6
Line 1 = tournament 1
Line 2 = tournament 2
Line 3 = tournamnet 3
Line 4 = tournament 4
Line 5 = tournament 5
Line 6 = tournament 7

But there summary report information again pulled from lines 1-5


Player 2 played in 4 tournaments so their statistics are stored in rows 7-10
Line 7 = tournament 1
Line 8 = tournament 2
Line 9 = tournament 5 (if they missed tournaments 3 and 4)
Line 10 = tournamnt 7

There summary report information again pulled from lines 6-8


Player 3 played in 6 tournaments so their statistics are stored in rows 10-14
Line 9 = tournament 1
Line 10 = tournament 2
Line 11 = tournament 3
Line 12 = tournament 4
Line 13 = tournament 5
Line 14 = tournament 6


There summary report information again pulled from lines 6-8


You can imagine the result of this pattern on 1000 lines of data.

I tried assigning each player 20 rows into which their data would be loaded.
Player 1 would be assigned rows 1-20 =sum(aa1:aa20)
Player 2 would be assigned rows 21-40 =sum(aa21:aa40)
Player 3 would be assigned rows 41-60 =sum(aa41:aa60)

After adding results from the latest tournament (#7) I then deleted an empty
row so that each player still only had twenty rows. This still did not work.

I need something that will summarize 28 different statistical columns (sum,
average, standard deviation, minimum and maximum) for each players results
added and re-sorted weekly. Calculations will also have to take into
consideration empty cells and various number of tournaments (1-20).






The results of tournament #7, about 145 lines of new data were added into to
my raw data and sorted again by player name Now the original calculations on
my summary report (one line per player) do not line up with the original rows
of data as stated above.

 

[Mike Middleton]

duchem02 -

Maybe you could arrange the data in standard flat-file database format (a
line for each player/event/score/etc combination), and then use the Pivot
Table feature to obtain your summaries.

- Mike Middleton
http://www.DecisionToolworks.com
Decision Analysis Add-ins for Excel

 

[Spiky]

Sorry, I'll try again.

I am trying to get to a summary report that will contain columns labeled
player name, total events, rounds played, average rounds per event, average
place finished, scoring average (rounds 1,2,3,4 and total), minimum score
recorded (rounds 1,2,3,4, and total), maximum score recorded (rounds 1,2,3,4
and total), standard deviation of scores (rounds 1,2,3,4 and total), total
shots made, total winnings and dollars per shot made.

Each week I add the latest tournament results to my raw data columns and
sort by the players name, ending up with each tournament result as a single
line item. I am currently at 7 tournaments meaning there are players with
results from 1 to 7 tournaments. At the end of the sixth week, I sorted all
of the raw data by player name and placed simple calculation in the summary
report.

Example:
Player 1 played in 5 tournaments so their statistics are stored in rows 1-5
Line 1 = tournament 1
Line 2 = tournament 2
Line 3 = tournamnet 3
Line 4 = tournament 4
Line 5 = tournament 5

Player 2 played in 3 tournaments so their statistics are stored in rows 6-8
Line 6 = tournament 1
Line 7 = tournament 2
Line 8 = tournament 5 (if they missed tournaments 3 and 4)

Player 3 played in 6 tournaments so their statistics are stored in rows 9-14
Line 9 = tournament 1
Line 10 = tournament 2
Line 11 = tournament 3
Line 12 = tournament 4
Line 13 = tournament 5
Line 14 = tournament 6

......this continues for about 200 players.

AFTER ADDING IN TOURNAMENT #7...

Player 1 played in 6 tournaments so their statistics are stored in rows 1-6
Line 1 = tournament 1
Line 2 = tournament 2
Line 3 = tournamnet 3
Line 4 = tournament 4
Line 5 = tournament 5
Line 6 = tournament 7

But there summary report information again pulled from lines 1-5

Player 2 played in 4 tournaments so their statistics are stored in rows 7-10
Line 7 = tournament 1
Line 8 = tournament 2
Line 9 = tournament 5 (if they missed tournaments 3 and 4)
Line 10 = tournamnt 7

There summary report information again pulled from lines 6-8

Player 3 played in 6 tournaments so their statistics are stored in rows 10-14
Line 9 = tournament 1
Line 10 = tournament 2
Line 11 = tournament 3
Line 12 = tournament 4
Line 13 = tournament 5
Line 14 = tournament 6

There summary report information again pulled from lines 6-8

You can imagine the result of this pattern on 1000 lines of data.

I tried assigning each player 20 rows into which their data would be loaded.
Player 1 would be assigned rows 1-20 =sum(aa1:aa20)
Player 2 would be assigned rows 21-40 =sum(aa21:aa40)
Player 3 would be assigned rows 41-60 =sum(aa41:aa60)

After adding results from the latest tournament (#7) I then deleted an empty
row so that each player still only had twenty rows. This still did not work.

I need something that will summarize 28 different statistical columns (sum,
average, standard deviation, minimum and maximum) for each players results
added and re-sorted weekly. Calculations will also have to take into
consideration empty cells and various number of tournaments (1-20).

The results of tournament #7, about 145 lines of new data were added into to
my raw data and sorted again by player name Now the original calculations on
my summary report (one line per player) do not line up with the original rows
of data as stated above.

You are using straight SUM formulas and similar. Switch to the DSUM,
DSTDEV, etc. These will encompass your whole database as you add
lines, if you set the range large enough to cover all the entries you
will make, all 4000 lines. (use 5000 to be sure, perhaps) Or possibly
the strangely universal SUMPRODUCT, but that will work slower.

You do have to set up criteria cells for the Dxxx formulas, but you
can just shove those off to the side or to a different sheet to be out
of the way.