Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Updating Formulas
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Updating Formulas
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. -- Regards Roger Govier "cardosol" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formulas updating | Excel Worksheet Functions | |||
Formulas aren't updating | Excel Worksheet Functions | |||
Updating formulas | Excel Worksheet Functions | |||
formulas not updating | Excel Discussion (Misc queries) | |||
Formulas not updating | Excel Discussion (Misc queries) |