# Excel Average Across Worksheets

Discussion in 'Business Applications' started by Allyson227, Oct 2, 2003.

Not open for further replies.

Joined:
Apr 22, 2003
Messages:
22
Hi Everyone,

In Excel, if I have a worksheet for each week, what is the best way to keep a running year-to-date average of a value that is in each sheet?

More detail: Each day I am tracking the hours spent on work orders and total hours on the clock. At the end of the week I calculate a "documented hours" percentage. (sum of work order ours/sum of total hours). I want to compare this week with previous weeks and keep a running ytd percentage.

2. ### Anne Troy

Joined:
Feb 14, 1999
Messages:
11,746
First Name:
Anne

Put a worksheet in called FIRST and put it as the FIRST worksheet in FRONT (to the left) of the worksheets that contain the sums you want to average.

Put a worksheet in called LAST and put it as the LAST worksheet in BACK (to the right) of the worksheets that contain the sums you want to average.

Now, assuming your sheet averages are in the same cell (I'll use G1 in my example) of every sheet, put this on the sheet that contains the totals:

=average(First!G1:Last!G1)

You may need to put a 0 in G1 of both the First and Last worksheets, but I'm not positive.

Let me know if it doesn't work. I didn't test, but will...

3. ### CastleHeart

Joined:
May 4, 2002
Messages:
743
Hi, folks....

"I want to compare this week with previous weeks and keep a running ytd percentage."

With regard to the running percentage:

needs to be =average(First!:Last!G1)

Secondly I don't understand the"I want to compare this week with previous weeks ."
But then I often don't understand stuff!

Is this a second task you wish to accomplish? If so, then what does "Compare" mean. If not....then never mind.

- Castleheart

4. ### CastleHeart

Joined:
May 4, 2002
Messages:
743
One thing more....

I think if you include a ZERO in your sheet then it will assume that was one of your percentages ( seems like we have weeks like that !) and average that in as well.

- C

5. ### Anne Troy

Joined:
Feb 14, 1999
Messages:
11,746
First Name:
Anne
CH put: needs to be =average(First!:Last!G1)

Which is correct! Thank you!

6. ### XL Guru

Joined:
Aug 30, 2003
Messages:
2,702
Allyson227,

I hate to say this, because

(a) it's the kind of answer you'd get from an IT dept (NB - I have nothing against IT dept people, most of them are marvellous but on the whole they're bound by ridiculous policies)

(b) you may have put in a lot of work already

, but ...

... the best tool in XL for summarising data is a pivot table. With this, there's no need to have weekly sheets, just one sheet with a continuous table of data and the table on the side.

With 4 columns (Date, JobID, Hours [that day], week commencing) you could have a simple table that would show the hours for each job for all weeks, or any combination of weeks you choose. With a few clicks, you could filp this to show %ages.

If a sample file might interest, PM me your email.

Rgds,
Andy

7. ### Anne Troy

Joined:
Feb 14, 1999
Messages:
11,746
First Name:
Anne
I hate pivot tables, so I never suggest them.

8. ### XL Guru

Joined:
Aug 30, 2003
Messages:
2,702
Not "bad" -- there's usually 2 or more ways to do something, so what's text-book best may be pointless if you're not comfortable with it.

I wasn't trying to pontificate, there's XL things I can't stand and will avoid at all costs (subtotals -- ugh!) ; the bottom line is love 'em or hate 'em, PTs can be very powerful and useful.

,
Andy

9. ### Anne Troy

Joined:
Feb 14, 1999
Messages:
11,746
First Name:
Anne
LOL
I've just never liked pivot tables.
LOL!!
(And I call meself an Office expert...)

10. ### XL Guru

Joined:
Aug 30, 2003
Messages:
2,702
Correction (bum steer i.r.o WEEKDAY, which always does my head in).

Say you have 22-Sep-03 in A2. The formula
=WEEKDAY(A2,2)
should return 1, ie: weekday for 22-Sep-03 = Monday (this is true, I checked a calendar). The ",2" bit is the return type, ie: whether you class your weeks as beginning on a Sunday or Monday -- see XL Help for more info.

To create the w/comm date, use
=IF(WEEKDAY(A2,2)=1,A2,A2-(WEEKDAY(A2,2)-1))

There are 2 parts to this ;

=IF(WEEKDAY(A2,2)=1,A2
says if A2 weekday is Monday, then w/comm is the same as A2.

The 2nd part,
A2-(WEEKDAY(A2,2)-1))
says if A2 weekday <> 1 (not a Monday), w/comm = A2 minus whatever weekday A2 is (minus 1, to top & tail).

DB's comments i.r.o. pivot tables are noted, I'll get back later with an array formula fix.

Rgds,
Andy

11. ### CastleHeart

Joined:
May 4, 2002
Messages:
743
Andy,

When you go to sleep at night ..... (if you do sleep) .... when other people count sheep - do you count cells?

You know too much !
Makes my head hurt just thinking about it. I don't think I have enough room up there for all that!

- C

PS: This may be another chance to show my ignorance but what is " i.r.o "? It is not in my book of East Texas Colloquialisms. Is it similar to w.r.t ?

12. ### XL Guru

Joined:
Aug 30, 2003
Messages:
2,702
C-H, you don't fool me, only a few days ago you were pointing RandyG towards DMAX. Here's the array version.

(Preamble -- I'm continually baffled by the oft-seeming obsession to compartmentalise "stuff". Why have 1 sheet per week? I'm sure it's based on "if I split stuff up, it'll be easier to ref later". But with all data on one sheet, you can just use AutoFilter to show all dates >=start date and <=end date. Ultimately you're just gonna make yourself more work. Anyway ...)

Let's say A1:C1 are labels ; "Date", "JobID", "Hours". For testing, I used dates in A2:A23, stuck with A, B, C options in B2:B23 & poked some hours into C2:C23.

F1:F3 = "W/comm", "JobID", "Hours". G1 = a valid date, ie: a Monday. G2:I2 = "A", "B", "C". In G3,
=SUM((\$B2:\$B23=G2)*(\$A2:\$A23<\$G\$1+7)*\$C2:\$C23)-SUM((\$B2:\$B23=G2)*(\$A2:\$A23<\$G\$1)*\$C2:\$C23)

NB1 -- this is an array formula ; enter with CTRL+Shift+Enter, not plain Enter. Do it right & it'll appear in the formula bar wrapped in curly brackets, viz {...}

NB2 -- its' in 2 parts, defined by the minus in the middle.

Part1 says sum where B2:B3 (JobID) = G2 (A) and A2:A23 is less than G1 (start date) + 7. This sums everything in C2:C23 where date is up to the last day of start date week.

Part2 strips out old data, ie: pre-start date. The key is the A2:A23 is less than G1 (start date) bit. So in a nutshell, it adds everything (for JobID) up to the end of the week specified, then subtracts anything before the start of week specified.

The \$s are set up so you can drag it from G3 thru H3 and I3, to return the totals for the other JobIDs for the week in question. Enjoy.

Rgds,
Andy

Joined:
Apr 22, 2003
Messages:
22
Hi Everyone,
Not only did I just learn the equivalent of weeks worth of Excel stuff, I enjoyed reading it too! I love this forum.

I'm going to work through these. I do a lot of things in Excel that get the job done but aren't the most efficient way.

Thank you all!
Allyson

As Seen On

### Welcome to Tech Support Guy!

If you're not already familiar with forums, watch our Welcome Guide to get started.

over 733,556 other people just like you!