View Full Version : calculating std deviation with blank cells

Abdullah

02-19-2009, 05:29 PM

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

02-19-2009, 06:49 PM

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.

=STDEV(B2:B110)

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

02-20-2009, 03:58 PM

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

02-20-2009, 04:45 PM

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

02-21-2009, 07:20 AM

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

02-21-2009, 11:27 AM

I still dont get it, but this does what you ask using a UDF:

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

Powered by vBulletin® Version 4.2.5 Copyright © 2020 vBulletin Solutions Inc. All rights reserved.