Code not working

[johnball96]

Hello,

My names John and I'm completely new to VBA but I'm using due to my dissertation which required extensive data analysis and sorting.

My data is extensive (over 450,000 rows) covering years 1997-2009. Please find specimen of data below which can be copied and pasted into excel.

Date Year Month Time Latitude Longitude Altitude (m) Pressure (Pa) Pressure (Pa) Temperature (°C) Wind Direction H2O (g/kg) Ozone (pbbv)
20040920 2004 920 173703 33.65 -84.43 204 98898 Group 1 0 250 98898 19.79 71.87 6.769 44
20070918 2007 918 182721 33.65 -84.42 204.5 98888 Group 1 0 250 98888 24.25 100.51 9.309
20040920 2004 920 173714 33.65 -84.42 205.7 98878 Group 1 0 250 98878 20.25 71.04 6.78 43
19980821 1998 821 183244 33.65 -84.42 207.8 98853 Group 1 0 250 98853 27.75 90 12.061 77
19980821 1998 821 183244 33.65 -84.42 208.2 98853 Group 1 0 250 98853 27.75 90 12.061 77
20000823 2000 823 182325 33.65 -84.43 209.8 98829 Group 1 0 250 98829 28.92 4.67 14.715 59
20000823 2000 823 182329 33.65 -84.44 212.7 98794 Group 1 0 250 98794 28.75 4.67 14.647 58
19990718 1999 718 182840 33.65 -84.42 218.4 98729 Group 1 0 250 98729 29.9 147.18 10.963 77
20070627 2007 627 185145 33.65 -84.42 218.8 98722 Group 1 0 250 98722 32.25 251.8 13.165 40
20070627 2007 627 185145 33.65 -84.42 219 98722 Group 1 0 250 98722 32.25 251.8 13.165 40

The aim of the macro im trying to write is to a) create groups based on altitude of 250 meter intervals ie 0-250m, 250-500, 500-750 etc which group "Ozone" values. So effectivly each group will contain however ozone values are taken at each interval stage.

For this I used the following macro;

Sub GroupData()

Dim ThresValueStart As Single
Dim ThresValueEnd As Single
Dim GroupValue As Integer

ThresValueStart = 0
ThresValueEnd = 250
GroupValue = 1

Range("i2").Select
Do Until ActiveCell.Value = ""
If ActiveCell > ThresValueStart And ActiveCell <= ThresValueEnd Then
Cells(ActiveCell.Row, 11).Value = "Group " & GroupValue
Cells(ActiveCell.Row, 12).Value = ThresValueStart
Cells(ActiveCell.Row, 13).Value = ThresValueEnd
ActiveCell.Offset(1, 0).Select
Else
ThresValueStart = ThresValueStart + 250
ThresValueEnd = ThresValueEnd + 250
GroupValue = GroupValue + 1
End If
Loop


End Sub

This worked and created groups within my work sheet.

b) Next I wanted to create a macro which found out the mean average of the "Ozone" values within each separate group.

for this i used the following macro;

Sub GetAverages()

Dim AvCount As Integer
Dim AvGroup As Integer
Dim AvSum As Single
Dim AvAverage As Single


AvGroup = 1
AvCount = 0
AvSum = 0
AvAverage = 0

Range("k2").Select
Do Until ActiveCell.Value = ""
If ActiveCell = "Group " & AvGroup Then
'And
If Cells(ActiveCell.Row, 10).Value <> 0 Then
AvSum = AvSum + Cells(ActiveCell.Row, 10).Value
AvCount = AvCount + 1
End If

ActiveCell.Offset(1, 0).Select
Else

'write group to new workbook
'Sheets.Add
'ActiveSheet.Name = "Avereages"
Sheets("Sheet1").Range("A" & AvGroup).Value = "Group " & AvGroup

'calculate and write average
If AvSum > 0 Then
AvAverage = AvSum / AvCount
Sheets("Sheet1").Range("B" & AvGroup).Value = AvAverage

'Cells(ActiveCell.Row, 14).Value = AvSum
'Cells(ActiveCell.Row, 15).Value = AvCount

End If

'reset values
AvGroup = AvGroup + 1
AvCount = 0
AvSum = 0
AvAverage = 0


End If

Loop




End Sub

This once again was successful.

However, for the final part of this I wanted to find out the standard deviation (SD) of each interval group. I have yet to find a way to get a macro to do this. All attempts have cause excel to crash!

Due to my basic grasp of VBA I feel I am not able to write code this complex to facilitate the Standard deviation function. As such i would be very thankful for any sort of input.

Regards
John

 

[seboseszuk]

Hello John

I have entered 2 columns of your data of interest in cols. A and B.
A has Altitude, B has ozone data. All data start in row 2.
In column D row 2 I have entered the formula: (Altitude<250)
=if (A2<250,b2,0)
in column E row 2 I have entered the formula( Altitude 250-499.99999)
=if(and(A2>250,A2<500),B2,0)
then I have pulled down the formulas to the last row of data.

Result: Ozone values in the 250 regions.
STDVEP formulae can be used on the bottom of these columns.

HTH.

Gabor

 

[johnball96]

unfortunately i cannot get this to work seboseszuk . Column D is reproducing column B and column D all =0

would you be able to email me the excel workbook? johnball96@hotmail.com?