Dynamic Growth formula?

**GetYours** · 01-29-2010, 04:54 AM

Hi everyone,

I have run into a problem. I am collecting data for my work and inputting it by date in excel. The data is in columns. I want to calculate data growth in increments of 1 year, 6 months, and 3 months. I don't have a problem putting the formula in, but everytime I add a new row of data, the formula moves down with the same cell, so the growth measures the same year period instead of the new period.
For example, 1 year growth between Jan 1st 2010, and Jan 1 2009 is fine, but when I add a row for February 1 the formula moves down and stays on Jan 1st.
The formula I use is (cell1a-cell15a)/cell15a. When I add a new row however, the formula moves down to (cell2a-cell16a)/cell16a. I want it to stay on the first number in the data set always. Any assistance is greatly appreciated!

**teylyn** · 01-29-2010, 05:00 AM

Sound like you are inserting new rows for new data. Have you considered simply adding new data in the next empty row?

This would be a bit easier to advise on if we could see your spreadsheet. Include a few dozen rows of data, and explain how you want to sum up.

You can upload a file by clicking "Go Advanced" below the Quick Reply box and then the paper clip icon.

**GetYours** · 02-04-2010, 10:35 AM

Hi teylyn,

Please find the attached document that I am working on. It is an example using currencies. The data starts on row 9, and is calculated on a yearly, 6 month, and 3 month growth formulas. Whenever I enter new data, the formula slides down with the row. I want the formula to always be on row 9, and the respective row for one year, 6 months, three months. Take a look and as always, any help is greatly appreciated.

Best,

GetYours

**teylyn** · 02-04-2010, 03:51 PM

Try this:

B2 =(INDEX(C:C,9)-INDEX(C:C,61))/INDEX(C:C,61)
B3 =(INDEX(C:C,9)-INDEX(C:C,39))/INDEX(C:C,39)
B4 =(INDEX(C:C,9)-INDEX(C:C,22))/INDEX(C:C,22)

copy across.

hth