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

 

[TheGallows32]

Bump! I really need help on this, guys. I'm willing to upload a sample workbook to illustrate my problem if necessary

 

[offthelip]

Is there some reason you can't use the the excel STDEV() function and the Average() function in an additional column? EXcel should recalculate these automatically when you make any changes to the cells they are looking at whether you do it with a drop list or any of the other functions.

 

[TheGallows32]

Is there some reason you can't use the the excel STDEV() function and the Average() function in an additional column? EXcel should recalculate these automatically when you make any changes to the cells they are looking at whether you do it with a drop list or any of the other functions.

The workbook is meant to be easy to use for an executive. Here's how it works:

Sheet1 is the data. When the user opens the workbook, they add a new record to this data

Sheet2 is a dashboard where they can look at analytics. They can tweak drop-down lists to configure their data view. For example "show how today's ad performed after 3 hours of being live, compared to how ads from the last 7 days performed in their first 3 hours"

I've got distribution charts that update according to the user-selection so the user can easily see how today's ad compares to previous ads. Is it average? Way above average? This uses formulas like OFFSET and VLOOKUP

So I don't think I can make an additional column since I don't know which cells in my datasheet the user will be interested in. It has to be dynamic.

 

[offthelip]

If you can't use an extra column ( which could be hidden) you can use exactly the same functions in the worksheet calculate and/or the worksheet change event to calculate the stdev and average over whatever range they have changed or selected

 

[TheGallows32]

If you can't use an extra column ( which could be hidden) you can use exactly the same functions in the worksheet calculate and/or the worksheet change event to calculate the stdev and average over whatever range they have changed or selected

I'm sorry, I don't understand. Can you say that a different way?

 

[TheGallows32]

Here's an illustration:

https://imgur.com/a/NEfzy

Keep in mind that there could also be more data (in the 5 hours column or beyond) that the user isn't interested in (i.e not selected).

 

[offthelip]

I have just written this code to show you what I mean, I put random numbers in the range A2 to H9. This code works out the standard deviation of the numbers in each row and then works out the average of them and put the answer in A13.If you want it to output the stdeviation in column J just get rid of the apostrophe befroe hte range statement at the end

Sub test()
Dim stdevarr(1 To 9, 1 To 1) As Variant
Dim tempar(1 To 8) As Variant


inarr = Range(Cells(1, 1), Cells(9, 8))
For i = 2 To 9
 For j = 1 To 8
  tempar(j) = inarr(i, j)
 Next j
 stdevarr(i, 1) = Application.WorksheetFunction.StDev(tempar)
Next i
'Range(Cells(1, 10), Cells(9, 10)) = stdevarr
averagev = Application.WorksheetFunction.Average(stdevarr)
Cells(13, 1) = averagev


End Sub

 

[TheGallows32]

I have just written this code to show you what I mean, I put random numbers in the range A2 to H9. This code works out the standard deviation of the numbers in each row and then works out the average of them and put the answer in A13.If you want it to output the stdeviation in column J just get rid of the apostrophe befroe hte range statement at the end

Thank you! You've just introduced me to the exciting world of VB scripts in Excel, wish I'd gotten into this years ago.

Question:
-Can I use a Dim statement to declare variables for the relevent cells? I would put these variables inside
stdevarr(1 To 9, 1 To 1) as stdevarr(X To Y, 1 To 1)

Is that right?

 

[TheGallows32]

I have just written this code to show you what I mean, I put random numbers in the range A2 to H9. This code works out the standard deviation of the numbers in each row and then works out the average of them and put the answer in A13.If you want it to output the stdeviation in column J just get rid of the apostrophe befroe hte range statement at the end

Sub test()
Dim stdevarr(1 To 9, 1 To 1) As Variant
Dim tempar(1 To 8) As Variant


inarr = Range(Cells(1, 1), Cells(9, 8))
For i = 2 To 9
 For j = 1 To 8
  tempar(j) = inarr(i, j)
 Next j
 stdevarr(i, 1) = Application.WorksheetFunction.StDev(tempar)
Next i
'Range(Cells(1, 10), Cells(9, 10)) = stdevarr
averagev = Application.WorksheetFunction.Average(stdevarr)
Cells(13, 1) = averagev


End Sub

How would I re-write this given so that my range values (example A2 to H9) were saved as variables?

The values I need are stored in these cells:
https://imgur.com/a/YemHd

The worksheet they're on is called 'backend'
The data they reference is on a worksheet called 'Data'