Updating Formulas

[cardosol]

Hi,

I have a large workbook of sales data. It is broken down into weekly sales
by number of sales and dollar value. So A&B are one week, and C&D are the
next week and so forth.

My last column I need to average the number of sales and the dollar value of
sales for the last five weeks. Is there an easier way to create formulas
every week than manually creating formulas? Is there a formula I can use to
reference the five specific cells I need or VBA?

 

[Roger Govier]

Hi

One way.

Assuming you place your averages in columns A and B, and that you insert 2
new columns each week at C and D to take the new data.
i.e. you are continuously pushing older data across the screen.

Also assuming you have header in row 1 and that your data starts in row 2.
Enter the following array formula into A2

{=SUM((OFFSET($A$3,0,2,1,10)*(MOD(COLUMN(OFFSET($A$3,0,2,1,10)),2)=1)))/5}

To enter (or amend( an array formula, use Control,+Shift+Enter (CSE) instead
of just Enter.
When you use CSE, Excel will insert the curly braces { } for you. Do not
type them yourself.

Repeat the same formula in cell B2
Copy A2 and B2 down the sheet as far as required.

When you first insert 2 new blank columns, the values in A and B will
temporarily reduce (as the total of 4 weeks is divide by 5, but will adjust
back to the correct value as soon as new data is entered into the blank
columns C and D.