1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Excel Average Across Worksheets

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

Thread Status:
Not open for further replies.
Advertisement
  1. Allyson227

    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!
     
  2. Anne Troy

    Anne Troy

    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...
     
  3. CastleHeart

    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:

    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 ;)
     
  4. CastleHeart

    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

    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

    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

    Anne Troy

    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?
     
  8. XL Guru

    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

    Anne Troy

    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...)
     
  10. XL Guru

    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

    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 ! :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 ?
     
  12. XL Guru

    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
     
  13. Allyson227

    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
     
  14. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/169079

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice