# Thread: Updating weekly averages (Excel 2000/SP3)

1. ## 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. ## 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. ## 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

4. ## 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. ## 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.

6. ## 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.

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

11. ## 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
•