sales average across all employees and all months and more

**amirs318** · 07-24-2008, 02:28 AM

Hi. I'm having a hell of a time trying figure out a way to get excel to perform some of the following calculations. here is the background: I have a file that contains the sales for each of my employees. there are 40+ worksheets, each one corresponding to (and named after) an employee. the format of each worksheet is simple. the first column is the month (actually not quite one month, rather a label for a certain time period of the year), the second column is the sales in that month by that employee, and the third column is simply one cell long and is the number of the team the employee is on (there are 2 teams).

What I'd like to do is perform calculations such as the following:

1) the sales average across all employees and all months
2) the sales average across employees of team X (either 1 or 2)
3) the sales average of all employees just during certain months

Can excel handle this? if so, how? I'm kinda dreading the possibility of hearing that excel can't do this, and that I'd have to use (and learn) Access.

BTW, I just installed Excel 2007, which seems much more powerful than previous versions.

Thanks in advance!

**oldchippy** · 07-24-2008, 04:27 AM

Hi,

Take a look here on Summing across worksheets, you can also use Average to do the same thing

http://exceltips.vitalnews.com/Pages...orksheets.html

**olasa** · 07-24-2008, 04:29 AM

Here is one option PivotTables: Multiple Consolidation Ranges
HTH
Ola

**amirs318** · 07-24-2008, 04:46 PM

Thanks for the responses. Now I know how to perform functions on all the sheets...what about performing those functions on only some tables, based on a condition/flag (e.g. average sales per employee for those employees on Team A). How can I do that?

Thanks again.

**oldchippy** · 07-24-2008, 05:30 PM

Hi,

It would be better if you could post a sample of your spreadsheet with some dummy data so that we can see what you are trying to do.

I see that you are using 2007, but if you save your file in 2003 and zip that then more people will be able to open it and may be help.

To post a zipped example, go to “My Documents” or wherever you have your file stored, right-click and “Send to > Compressed file”, then attach this zipped file to your post.

**amirs318** · 07-24-2008, 06:30 PM

Sure. I've attached a small file with data very similar to what I have, except I have many more sheets and they each sheets just contain more of the same. Again, each sheet/employees has a Team (A or B) and their sales for each time period (several weeks long), as well as the season the time period occurs in. I've included three sheets, each representing one employee.

I'd like to calculate things such as:

1) Average sales per employee
2) Average sales per Team A (or B) employees
3) Average sales per Team A (or B) employees in season X.

Thanks again!