Macro for normalization

any tips as to where to begin?

Is this what you're after?

Please Login or Register  to view this content.

Thanks for your help! How would i display the trendline and the y= mx+b equation, as well as the coefficient of determination?

Try recording those desired changes, and then pasting that code into the end of what I've given you.

I modified the code in another way. I want to find the 3 day "moving average" for the normalized column. I need help with syntax for SUM. In terms of displaying the coefficient of determination and the equation for the linear regression line, here is my attempt at coding this.

Please Login or Register  to view this content.

I'm not sure about Excel 2003, but in 2007 you can add multiple trendlines... there is an option for a Moving Average trendline as well, with an adjustable period.

Are you able to record the Moving Average trendline and add it the same way you added the linear one?

This is my final code. One question I have, however, is how do you determine the length of a column? If I want to find the number of entries in column A, how would I do this without a for loop. Also, whenever I save this as a macro excel workbook, whenever I open it back up I cannot access my module. Why is this?

Please Login or Register  to view this content.

Can anyone help?

Please Login or Register  to view this content.

will return the number of entries in column A

hth
Ajay

You can use one of the following lines of code:

Please Login or Register  to view this content.

The first one will count how many cells are in column A. If you have data in rows 1-20, but you have a blank cell in row 10, then it will return 19.

The second one tells you the lowest used row in column A. So in the above scenario it would still tell you 20. It would probably be better for your purposes.

I'm still not sure why you need to do the part with the sum and divide by 3; doesn't the moving average trendline do the same thing? Try this code:

Please Login or Register  to view this content.

Or, if you want both trendlines in one chart, that can be arranged as well.

As for not being able to access your module... what do you mean exactly? you push Alt+F11, and what do you see?

-Joe