Apply formulas to the end of data of varying length in columns(please see description)

Hello !
I have data of the storm climate in a region which includes season(year) ,the number of the storm , interval , direction and speed.
I need to find out the resultant wind speed and directions of each storm.Only the last two columns will be used. Because there are aprox. 1100 storms to which i should apply 3 formulas , this is a very time consuming process .

The procedure is like this :
1) At the end of the last two columns at every storm, I must first calculate the average of the values above (which represent the rectangular coordinates)
Average.jpg
2) Then i must apply a formula =180/3.1415926*ATAN2(''Average Column 1'',''Average Column 2'') to receive the wind vector
Direction.jpg
3) In the end =SQRT(''Average Column 1''^''Average Column 2''^2)
Speed.jpg

To better understand the situation i attached some images and of course , the excel file.
Storms 73-96.xlsxStorms 96-2012.xlsx(i split it in two because of the size)

This must be done for each individual storm . I really wish there is a way to do this and I appreciate if i will recieve some solution.

P.S. It's my first post , hope I have some good luck !

Try something like this...

Sub Storms()
    Dim a As Range
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    For Each a In Range("C:C").SpecialCells(xlCellTypeConstants, xlNumbers).Areas
        With a(a.Count)
            .Offset(1, 4).Formula = "=Average(" & a.Offset(, 4).Address & ")"
            .Offset(1, 5).Formula = "=Average(" & a.Offset(, 5).Address & ")"
            .Offset(2, 4).FormulaR1C1 = "=180/PI()*ATAN2(R[-1]C,R[-1]C[1])"
            .Offset(3, 4).FormulaR1C1 = "=SQRT(R[-2]C^2+R[-2]C[1]^2)"
        End With
    Next a
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

YES ! It works like a charm ! you saved me tens of hours of work ! I am really excited because i can now apply this for other databases also.
one thing , if the value is negative (like -55) I need to add 360 (so that it equals 305), what shoud i put in the code to do this?
THANK YOU!

Originally Posted by Exorbit

YES ! It works like a charm ! you saved me tens of hours of work ! I am really excited because i can now apply this for other databases also.
one thing , if the value is negative (like -55) I need to add 360 (so that it equals 305), what shoud i put in the code to do this?
THANK YOU!

You're welcome. Why is it that charms are never said to work like a macro? Doesn't seem fair.

You could change the formula (whichever one) to something like...
=formula + IF(formula<0,360,0)

.Offset(2, 4).FormulaR1C1 = "=180/PI()*ATAN2(R[-1]C,R[-1]C[1])
+ IF(180/PI()*ATAN2(R[-1]C,R[-1]C[1])<0,360,0)"

Hello , sorry to disturb you again.
Now I have to calculate the number of hours for each storm .
1)First ,I need a macro to calculate the difference between the last and the first in the Date.time column for each storm.And please , I want it to be one cell after last value in the column.
I attached an image to explain this.Attachment 191267
2)Second , I need to calculate an index(and to do so I need another value for the Date.hour column) , so in the cell that is left blank i need to put this formula :Last cell + Last cell - The cell before the last cell. Again i attached an image .Attachment 191268

How did the last formula change work out for you?

Your recent images did not attach.

The last formula worked perfectly !
I didn't noticed that the images didn't display . Now i changed the method a bit.
1)I need to calculate the difference between the last Cell and the first in the column Date.time , at the end of it , for each storm.
Total hours.jpg
2)At the bottom of each column i need this : Last cell+Last cell - the cell before the last cell (F22+F22-F21), again for each storm.
Last value.jpg(i made a mistake , i should have renamed the column other than Date.time , but doesn't matter really. (this is for knowing the time step , that is used for an index - SIF (Storm impact factor))


Thank you , and I really need to learn to do macros in excel to not have to take your time.

The data in your recent images isn't the same configuration as you original data file. Can you provide a new data file?

Storms2.xlsx
Oh , sorry that I forgot about it . Here it is!

Sub Storms2()
    Dim a As Range
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    For Each a In Range("C:C").SpecialCells(xlCellTypeConstants, xlNumbers).Areas
        With a(a.Count)
            .Offset(1, 2).FormulaR1C1 = "=R[-1]C[]-R[-" & a.Count & "]C[]"
            .Offset(1, 2).NumberFormat = "[h]:mm:ss;@"
            .Offset(1, 3).FormulaR1C1 = "=R[-1]C[]*2 - R[-2]C[]"
        End With
    Next a
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

Yes it's perfect! Thank you a lot ! , One other question(and I hope it will be my last) how can I select every last cell with values before a blank cell in a column?

Sub Every_Last_Cell()
    Dim a As Range, rng As Range
    If Selection.Columns.Count > 1 Then MsgBox "Select only one column. ", , _
                                               "Select 1 Column": Exit Sub
    For Each a In Selection(1).EntireColumn.SpecialCells(xlCellTypeBlanks).Areas
        If a(1).Row > 1 Then
            If rng Is Nothing Then Set rng = a(1).Offset(-1) Else Set rng = Union(rng, a(1).Offset(-1))
        End If
    Next a
    rng.Select
End Sub