Get the Average Standard Deviation from a Dynamic Dataset

[TheGallows32]

Hi everyone! Is this possible in Excel??

Let's say I have 10 rows of numerical data across 10 columns, and I'd like to find the Standard Deviation of the numbers across each row.
In the end I would have 10 standard deviations (one for each row) that I could then average.

Simple enough if you're dealing with a static worksheet, but in my workbook I'm dynamically grabbing different samples of data using drop-down list input fields and the OFFSET, INDIRECT, and VLOOKUP functions.

Could I get the job done with a CSE function?

Thanks!
-J

 

[offthelip]

I am not quite sure what you are asking, but looking at you image it would appear that you want to iterate from row 942 to 948 across columns C to E ( 3 to 5) on the worksheet “Data”. Also it would appear that the values you want to iterate across are held in cells I16 to I19 on the sheet “Backend”. This code does this which I hope is what you want, it does show you how to cope with a "dynamic" range to be calculated using the cells I 16 to 19 to determine the start and end point.
You didn't say where you wanted the results, so I am still writing it out to cells(13,1) on the active sheet.

Sub test()
Dim stdev As Variant
Dim temparr As Variant
With Worksheets("Backend")
 rowcol = Range(.Cells(16, 9), .Cells(19, 9))
End With
numberofrows = rowcol(3, 1) - rowcol(1, 1)
numberofcols = rowcol(4, 1) - rowcol(2, 1)
ReDim stdevarr(0 To numberofrows, 1 To 1) As Variant
ReDim tempar(0 To numberofcols) As Variant


With Worksheets("Data")
inarr = .UsedRange.Value
End With
For i = rowcol(1, 1) To rowcol(3, 1)
 For j = rowcol(2, 1) To rowcol(4, 1)
  tempar(j - rowcol(2, 1)) = inarr(i, j)
 Next j
 stdevarr(i - rowcol(1, 1), 1) = Application.WorksheetFunction.stdev(tempar)
Next i
averagev = Application.WorksheetFunction.Average(stdevarr)
Cells(13, 1) = averagev
End Sub