Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts

    Updating weekly averages (Excel 2000/SP3)

    In the attached worksheet (20Kb), not authored by me, various conditional formatting controls the cell colours according to the amount of business. And basic SUM formulas display the required totals & with divisors averages; data is entered weekly.

    At present the weekly average formulas are altered manually. For example for week 27 the divisor is 1, for week 28 it is 2 and so on to week 39 where it 13. In the hands of a top class Sales Manager with little computer experience making these changes manually is fraught with the possibility of errors. Can the change from 1 through to 13 be automated? If so I would like to know how to do it.

    I will be grateful for any guidance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Updating weekly averages (Excel 2000/SP3)

    Where does this divisor come in? In the attached spreadsheet, there is a divisor of 11 in N3:N166, but I don't understand where it comes from.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Updating weekly averages (Excel 2000/SP3)

    Malcolm

    This mayne a cheat way of doing it.

    In cell N16 put this formula:

    =SUM(A16:M16)/COUNTIF(A16:M16,">0")

    If basically finds out if a cell contains a value above 0 and if does counts it. This will create your divisor in increments as you go through the months.

    I hope it works

    Jerry
    Jerry

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Ankeny, Iowa, USA
    Posts
    298
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating weekly averages (Excel 2000/SP3)

    Hans, the 11 looks like it comes into play because they're on the 11th week of a 13 week period. So, they're dividing by 11 to get a weekly average over the 11 weeks. When week 12 is complete and that data is entered into the table, they'll divide by 12, etc...

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating weekly averages (Excel 2000/SP3)

    Does the attached do what you want? It also automatically changed the number of weeks in the label above the average column. I wasn't sure what you wanted to do with weeks 40 thru 52 so I did not include them.
    Legare Coleman

  6. #6
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts

    Re: Updating weekly averages (Excel 2000/SP3)

    Hans, The divisor is the number of weeks in the quarter for which data has been entered. Week 37 is the 11th week of the quarter. Hence the weekly average is the total of weeks 27 to 37 divided by 11. And 11 is entered manually.

    When week 28 data is entered the divisor becomes 12 & at present this is done manually. I want to make that change automatic.

    I hope that answers your question.

  7. #7
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts

    Re: Updating weekly averages (Excel 2000/SP3)

    That's is it - I thought my explanation was adequate. Seems not - it is a luxury to be understood! {8;-))

  8. #8
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts

    Re: Updating weekly averages (Excel 2000/SP3)

    Jerry, Thank you for your assistance. Your `cheat' appears to work for N16 & should therefore work in the other cells in column N. Why is it a cheat way of doing it? Seems fair enough to me.

  9. #9
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts

    Re: Updating weekly averages (Excel 2000/SP3)

    With Jerry's contribution in cell N16 it does exactly what I want. Many thanks for your assistance. It is very much appreciated.

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Updating weekly averages (Excel 2000/SP3)

    Did you get the number of weeks in the column header to update automatically? If not, check out my worksheet.
    Legare Coleman

  11. #11
    3 Star Lounger
    Join Date
    May 2003
    Location
    Mid-Cheshire, England
    Posts
    275
    Thanks
    1
    Thanked 3 Times in 3 Posts

    Re: Updating weekly averages (Excel 2000/SP3)

    Yes indeed the number of weeks in N2 does update. I added Jerry's formula for N16 to your attachment so getting everything I needed for now. Thank you for following that up.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •