Calculate Slope and average via VBA in Excel

[mcm1978]

Hey Guys,

I am somehow stuck in my vba macro . I am using Excel 2010. I have an excel List with measures for different days/month in different years (see attachment).

What I need is the following calculated in excel VBA in a cell the average and the slope for the year 2000, 2001, 2002, ...:

Year Average Slope
2000
2001
2002
...

Since my original table is pretty long it would be nice to solve that problem via vba.

Thanks a lot for any help.

Cheers,

Michael

[joseph4tw]

Hello,

I don't think you need VBA for this solution. A couple of formulas should do the trick. I'm assuming the following:
Y2 = Measurement 4
Y1 = Measurement 2
X2 = Measurement 3
X1 = Measurement 1

I added a column to calculate the slope for each date's measurements. Then I added a single formula that took the average of all of the dates.

Now, if you want to filter for a particular date range, you should consider making a pivot table for this. Please take a look at the new sheet that I created. Please let me know if you need instructions on how I made the pivot table. It's very powerful and it would be my preferred solution to this problem.

Measurement.xlsx