Formula across multiple sheets

[DougN]

I am using Excel 2003.

I am a basketball coach looking to create an effeciency chart for my
players. What I have so far is a seperate sheet for all of our games. There
is a row for each player and columns for each stat. The last column is their
Effeciency Rating for that particular game that is calculate by a formula I
created.

Also on each sheet (one sheet per game remember) I have a box that indicates
whether the game was at home or on the road. I would like to be able to make
a formula for my last sheet that will be able to average ONLY the home games
and then another that would average their rating for ONLY the road games. So,
in my logic it would be "IF LocationCell = H, then average the rating" or "IF
LocationCell is R, then average the rating"

I hope this makes sense- thanks for your help.

 

[pdberger]

Doug --

In the long run, I think it would be better to put all the data on one page
in one long table. You'd have to add two additional columns perhaps
(Against, and Home/Away). The benefit is that, once you've done that, you
can create a Pivot Table that would allow you to answer questions that come
up on the fly.

To do this, add the additional info to the data before you copy it into the
long table, then move it so that each player's activity is a single line, a
single "record", in the table. Then use >Data>Pivot Table. It'll take a
couple of minutes to figure it out, but the Help is pretty good and you'll
like it.

HTH

 

[kbee]

doug, i am also movice at this, but I would sort the sheets of each game so
I will get two lists, which can be calculated. and in the last sheet u will
refer back to the calculation u did already on the other sheets. but the pros
will probably will have a better idea.
best regards
bee

 

[DougN]

Thanks for the quick help guys.

Pdberger- I have a cell on each sheet that signifies where the game is Home
or away...its basically noted by an "H" or an "A". I was thinking that I
could just average the cells IF cell#=H.

I can put it all on one table, but I would like to be able to seperate them
so that I can't print game by game tables if my head coach asks for them.

 

[Tyro]

If you have a 20 game season with, say, 10 home games and 10 road games you
might be able to simplify your life if you kept each home game on a separate
sheet with sheet names such as HOME1, HOME2 .... HOME10 and the road games
on separate sheets with with sheets names such as ROAD1, ROAD2 .... ROAD10.
If all the home games are grouped together followed by the road games, on
your last sheet, SUMMARY, you can simply average a cell such as A1 on each
sheet with something like:

=AVERAGE(HOME1:HOME10!A1) for the home games or
=AVERAGE(ROAD1:ROAD10!A1) for the road games or
=AVERAGE(HOME1:ROAD10!A1) for all the games.

Also you can easily print your sheets, one for each game

Just be certain the the first home game sheet is HOME1 and the last home
game sheet is HOME10 and the other home game sheets are located between
HOME1 and HOME10 in the workbook. Same for the road games.

Tyro

 

[pdberger]

Doug --

That's the cool thing about pivot tables -- you can pull out data by game,
or chronologically, or home/away chronologically, or whether they tied their
left shoe first on away games, whatever.

Try it you'll like it.

 

[Tyro]

But I would go the pivot table route.

Tyro

If you have a 20 game season with, say, 10 home games and 10 road games
you might be able to simplify your life if you kept each home game on a
separate sheet with sheet names such as HOME1, HOME2 .... HOME10 and the
road games on separate sheets with with sheets names such as ROAD1, ROAD2
.... ROAD10. If all the home games are grouped together followed by the
road games, on your last sheet, SUMMARY, you can simply average a cell
such as A1 on each sheet with something like:

=AVERAGE(HOME1:HOME10!A1) for the home games or
=AVERAGE(ROAD1:ROAD10!A1) for the road games or
=AVERAGE(HOME1:ROAD10!A1) for all the games.

Also you can easily print your sheets, one for each game

Just be certain the the first home game sheet is HOME1 and the last home
game sheet is HOME10 and the other home game sheets are located between
HOME1 and HOME10 in the workbook. Same for the road games.

Tyro