percentage change loop

[Eric]

Windows Excel 2003

Please excuse me, I'm going to have to go slowly
Any and all help is greatly appreciated. Can some
one suggest a strategy using several macros?
Breaking it up would make it easier for me to see
the parts and how they work together.

I have data in the following format:

A B C ... W
category 1 category 2 category 22

11 1960.01 49.8 53.3 161.8
12<empty row>
13 1960.02 49.8 53.2 161.1
14<empty row>
..
..
1069 2004.02 95.3 94.8 192.8
1070<empty row>

The mission is to find percent change for each
category, month on month.

I would like to return "1" for all categories in row 12,
assuming no change for interval 1959.12 to 1960.1

Otherwise, the goal is to compute the percent change
over the month interval for a category and write it
to the cell below, in the empty row.
For example, percentChange for category 1 over
interval 1960.01 to 1960.02 should be in B14.

Then move one cell right, repeat computation, move...
until wrapping around to A(currentRow plus 2). Continue
in such fashion to end of data, 1069W, with final percentChange
entry in 1070W.

I would like to return "1" for all categories in row 12,
assuming no change for interval 1959.12 to 1960.1

For the unchanged case, like (B13-B11)/B11*100, the value for
percentChange cannot be 100, but should be 0 instead.

And to make the sheet more readable, I want to add the same
background color to all even numbered rows (containing the
percentChange numbers).

Thank you for your patience in reading to the end.
I would be most greatful for any and all responses.

Eric

 

[Frank Kabel]

Hi Eric
try the following:
1. In row 12 insert manually a 1 for each column

2. Use the following macro to populate all other rows
Sub make_formulas()
Dim LastRow As Long
Dim row_index As Long

LastRow = ActiveSheet.Cells(Rows.count, "A").End(xlUp).row + 1
For row_index = 14 To LastRow Step 2
Range(Cells(row_index, "A"), Cells(row_index, "W")).FormulaR1C1 _
= "=(R[-1]C[0]-R[-3]C[0])/R[-3]C[0]"
Range(Cells(row_index, "A"), Cells(row_index, "W")).NumberFormat =
"0.00%"
Range(Cells(row_index, "A"), Cells(row_index,
"W")).interior.colorindex = 3
Next
End Sub

3. To color the even rows also have a look at
http://www.cpearson.com/excel/banding.htm
using conditional format for this or add the line

 

[Eric]

Dear Frank,

Thank you very much for your reply, and so
quickly.

Sorry for the multipost. I was afraid I would
be told to take my question to the new users
forum, and I thought I'd be... preemptive.

I have learned to trust the power of the single
post. Again, thank you.

Regards,
Eric