Referencing a formula that will automatically update several cells

[joevan]

I have a spreadsheet calculating the average of numerical values in different rows. Each month a new column of data is added and the formula for average has to be changed on each row to reflect the average of the data for the last six columns.

Every time I update the data, have to change the formula on every row.

Can I have one formula cell that can be updated to reflect the average of the last six columns that can in turn be referenced by all the other rows that need to have their formula updated so that this is done automatically. In other words would only have to update the one cell in order for all the other cells to be updated?

[Zodeeak]

You could try just having an formula that looks like:
"=average(A1:F1)"
and copy that cell to the right as data gets added

[kvsrinivasamurthy]

Update sample file how data looks for different months and where you want average.

[LJMetzger]

Hi joevan,

The INDIRECT function may be able to solve your problem. For example if your formula is:

Please Login or Register  to view this content.

Now I'll try to interpret what I just did.
Cell C11 contains the value 'A'
Cell C12 contains the value 'F' (from the following formula: =CHAR(CODE(C11)+5))
ROW() returns the current row number

(INDIRECT($C$11 & ROW()&":" & $C$12 & ROW()) becomes A5:F5 as follows:
a. $C$11 is 'A'
b. ROW() is 5 (i.e. the current row number)
c. : (self explanatory)
d. $C$12 is 'F'
e. ROW() is 5 (i.e. the current row number)

There is a limitation that the last row being averaged is Column 'Z', so the maximum legal value for cell 'C11' is 'U'.

The limitation can be eliminated by either:
a. Manually entering the column in cell $C$12
b. Using R1C1 notation - formula conversion is beyond my skill level.
c. Using Macros to calculate two character column numbers

Please Login or Register  to view this content.

See the attached sample workbook with a working example.

Lewis

[joevan]

How do I upload this spreadsheet so that you can see it?

[LJMetzger]

To upload a file:

Below the reply box where you enter text, press 'Go Advanced'.


When you scrool down, below the message box, you will find a button labelled 'Manage Attachments'. Clicking this button will open a new window for uploading attachments. You can upload an attachment either from your computer or from another URL by using the appropriate box on this page. Alternatively you can click the Attachment Icon to open this page.

To upload a file from your computer, click the 'Browse' button and locate the file. To upload a file from another URL, enter the full URL for the file in the second box on this page. Once you have completed one of the boxes, click 'Upload'.

Once the upload is completed the file name will appear below the input boxes in this window. You can then close the window to return to the new post screen.

[boopathiraja]

Check it out the attachment,

[Glenn Kennedy]

This makes an average for each row, for the most recent N columns, where N is defined in cell B17.

[joevan]

Spreadsheet is attached for reference

The cells highlighted in green are the cells containing the formula that I want to update from a central point.
Currently the six month average is column "N" to "S" once the September data in entered the new six month average period should be "Column "O" to "T"

I have only included two sets of data (Stock Items)on this spreadsheet, generally there will be in the region of 25 sets of data meaning that the Average for each set must be manually changed to move to the next column forward.

I want to be able to make one change that will update the formula for each stock item, and reference the current cells highlight4ed in green to that one cell.

Hope this makes sense!

[Glenn Kennedy]

You caused a bit of panic there, as it looked like a TOTALLY different problem from the one you started with. Nowhere, before Post 10, did you say that there were values in the cells for the months that are still to come! However, there was an easy way round it. I have left it so that you can change the rolling average period by adjusting the number in column AA. At the moment these can be done individually, or all set from one cell for the sheet, or simply hard-coded as the digit 6.

Decide what you want & try it out. If it trips you up, shout. It'll only take a moment to fix.

[joevan]

Thanks Glen, this got me on the right track!

[Glenn Kennedy]

If that's it; please mark the thread as solved and (preferably) say thanks to those who helped by clicking the Add Reputation button at the foot of their posts.