Problem with defining arr for calculating Average and STDEV

[dschmitt]

I found an Excel VBA online that can calculate the average and the STDEV of numbers in Cells A1 to A10. See script below. This works. I tested it.
What I would like to do now is change this script so that it can be used for 1D data arrays with varying amount of data.
Assume I have data in column B. The first row is always row 2 but the last row varies.
How would the script look like assuming that the size of the array is variable and the last row is variable?

Please Login or Register  to view this content.

[dschmitt]

I found two other functions for average and STDEV. These functions seem better suited for what I want to do.
Question: How does the Sub "Compute" need to be modified so that I can used it for a 1D array with varying last row?

Please Login or Register  to view this content.

[karedog]

You can use Excel builtin formulas for this, something like :

Please Login or Register  to view this content.

However if you want to use your existing UDF, the sub should be changed to something like this :

Please Login or Register  to view this content.

[bakerman2]

For use of formulea in arrays take a look at this.
http://www.snb-vba.eu/VBA_Arrays_en.html#L_6.16

[dschmitt]

Dear karedog, thank you for the solutions. I personally like the Compute_Range solution the best. This one I will use. But I also got your Sub Compute() script to work. I will keep this one in mind.
bakerman2, thanks for the link.

[karedog]

You are welcome, glad this can help you. Thanks for marking the thread as solved.


Regards

[dschmitt]

I wanted to add to your reputation but I was blocked from doing that. I have been given too many to you ...

[karedog]

Don't worry about that, thank you very much