Excel Average Across Worksheets

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Allyson227

Thread Starter
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.

I appreciate any help you can provide!
 
Joined
Feb 14, 1999
Messages
11,746
First Name
Anne
Ready?

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...
 
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:

DBA.... I think your =average(First!G1:Last!G1)
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 ;)
 
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
 
Joined
Feb 14, 1999
Messages
11,746
First Name
Anne
CH put: needs to be =average(First!:Last!G1)


Which is correct! Thank you!
 
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
 
Joined
Feb 14, 1999
Messages
11,746
First Name
Anne
I hate pivot tables, so I never suggest them.
That's prolly bad, huh, Andy?
 
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
 
Joined
Feb 14, 1999
Messages
11,746
First Name
Anne
LOL
I've just never liked pivot tables.
Ask me about ANYTHING but that!
LOL!!
(And I call meself an Office expert...)
 
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
 
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 ! :D :D
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 ?
 
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
 

Allyson227

Thread Starter
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
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

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

Join over 807,865 other people just like you!

Latest posts

Staff online

Top