calculating std deviation with blank cells

[Abdullah]

Hi,

I am looking for a macro to calculate standard deviation for multiple columns of data; each column has a varying length of data. Please see that attached sample worksheet. I want to calculate the standard deviation of column A (year) but first, the macro needs to determine which years to use in the calculation. Columns B through H are streamflows for various rivers. Some years have streamflow; some years don't. If a year does not have streamflow, then it is not inlcuded in the standard deviation calculation.

In row 111, I inserted the calculations that I am looking for.

Any help is greatly appreciated.

[tpoynton]

The stdev function excludes blanks already, so there is no need to do something to pull them out if some years are missing.

I looked at your file, and the standard deviations listed there were way off, I'm not sure why. When I put in the formula below and autofilled to the last column, all appears well.
[vba]
=STDEV(B2:B110)
[/vba]
I also ran a macro I already have to create standard deviations and that's in the EZA1 sheet in the attached, and numbers match up to the 6th decimal place. I have verified my macro against SPSS, so you can trust the Excel calculations for standard deviation.

SO, if this data only changes once per year, I'm not sure if a macro is worth the trouble

EDIT - perhaps I'm misunderstanding the question...I see that the formula you provided returned the standard deviation of the year; that doesnt make sense.

[Abdullah]

Thanks very much for your help. I am looking for the standard deviations of the years. For example, on River 1, I am looking for the std dev of 1950-2007. For River 2, I am looking for the std dev of 1984-2007.

[Bob Phillips]

You were taking the standard dev of the years themselves, not the values associated with those years. Just do as Tim suggests, and you will be sorted.

[Abdullah]

I think you are misunderstanding me. I really do want a macro to calculate the standard deviations of the years, not the values associated with the years. I can easily calculate the std dev of the river flows (y values) without a macro.

I need the standard deviation of the years (x values) for a statistical analysis of the residuals.

Thanks again.

[tpoynton]

I still dont get it, but this does what you ask using a UDF:
[vba]
Public Function yearStdDev(riverRange As Range) As Double
Dim yearRange As Range
Dim cell As Range
For Each cell In riverRange
If cell.Value <> "" Then
If yearRange Is Nothing Then
Set yearRange = Cells(cell.Row, 1)
Else
Set yearRange = Union(yearRange, Cells(cell.Row, 1))
End If
End If
Next cell
yearStdDev = WorksheetFunction.StDev(yearRange)
End Function
[/vba]