Hi all,
I have created an array that stores the mean for a set of variables. Then, I want to reference the position in that array for a second function that is supposed to calculate the standard deviation of the same set of data. It seems to work ok for the mean, but when I try to reference the nth position of the mean array for the standard deviation calculation, I am getting an error. I am looking for any suggestions on how this should work. Code is below.
Thanks in advance,
Steve - Sub stats()
-
-
periods = Range("periods")
-
variables = Range("variables")
-
-
ReDim X(periods, variables) As Double
-
ReDim uX(variables) As Double
-
ReDim sdX(variables) As Double
-
ReDim tran(variables, periods) As Double
-
-
Sheet1.Activate
-
-
'Load Returns
-
For i = 1 To variables
-
For t = 1 To periods
-
X(t, i) = Cells(4 + t, 2 + i)
-
Next
-
Next
-
-
'Calculate Mean (run 'mean' function')
-
uX() = mean(X)
-
-
'Calculate Standard Deviation of Returns (run 'sdev' function)
-
sdX() = Sdev(X) '***Needs work***
-
-
-
End Sub
-
-
-
Function mean(X() As Double) As Variant
-
'Function is used to calculate the mean of each variable
-
-
periods = Range("periods")
-
variables = Range("variables")
-
-
ReDim u(variables) As Double
-
-
For i = 1 To variables
-
For t = 1 To periods
-
u(i) = sum + X(t, i)
-
sum = u(i)
-
Next
-
u(i) = sum / periods
-
sum = 0
-
Next
-
-
mean = u()
-
-
End Function
-
-
Function Sdev(X() As Double) As Variant
-
'Function is used to calculate the standard deviation of each variable
-
-
periods = Range("periods")
-
variables = Range("variables")
-
-
ReDim sd(variables) As Double
-
-
For i = 1 To variables
-
For t = 1 To periods
-
sd(i) = sum + (X(t, i) - mean(X(i))) ^ 2
-
sum = sd(i)
-
Next
-
sd(i) = Sqr(sum / (periods - 1))
-
sum = 0
-
Next
-
-
Sdev = sd(i)
-
-
End Function
2 1476
Hi all,
I have created an array that stores the mean for a set of variables. Then, I want to reference the position in that array for a second function that is supposed to calculate the standard deviation of the same set of data. It seems to work ok for the mean, but when I try to reference the nth position of the mean array for the standard deviation calculation, I am getting an error. I am looking for any suggestions on how this should work. Code is below.
Thanks in advance,
Steve
If i understood, you have a matrix of (periods, values) and you want the mean and the StdDv for each period, am i right?
so you have MainArray(values, periods)
then you should have an array of means, and an array of SD.
First of all, you should work the means function with a variant as its parameter, since a variant can be an array. That's the key to do what you want, Otherwise, you'll have to use ParamArray, and write each value of the array as an input.
I'll write here a small code that might give you an idea of how to use Variants to move arrays from one to other sub or function (and hopefuly, will help you with this task): - Sub something()
-
Dim MainArray
-
Dim Means
-
Dim SDevs
-
'The code to read in the MainArray your values, i defined MainArray as a variant, so you can do something like:
-
-
With Worksheets("sheet1")
-
MainArray = Range(.Cells(1, 1), .Cells(50, 3)) 'i'll use [value, period] because i suppose the periods are in columns.
-
End With
-
Means = Mean(MainArray)
-
SDevs = SDev(MainArray)
-
End Sub
-
-
'These are the functions
-
Public Function Mean(ByVal a)
-
Dim i As Long: Dim j As Long
-
Dim Arr1() As Double
-
ReDim Arr1(1 To UBound(a, 2))
-
For i = 1 To UBound(a, 2)
-
For j = 1 To UBound(a)
-
Arr1(i) = Arr1(i) + a(j, i)
-
Next
-
Arr1(i) = Arr1(i) / (UBound(a))
-
Next
-
Mean = Arr1
-
End Function
-
-
Public Function SDev(ByVal a)
-
Dim i As Long: Dim j As Long
-
Dim Arr1() As Double
-
Dim Aux
-
ReDim Arr1(1 To UBound(a, 2))
-
Aux = Mean(a)
-
For i = 1 To UBound(a, 2)
-
For j = 1 To UBound(a)
-
Arr1(i) = Arr1(i) + (a(j, i) - Aux(i)) ^ 2
-
Next
-
Arr1(i) = (Arr1(i) / (UBound(a) - 1)) ^ (0.5)
-
Next
-
SDev = Arr1
-
End Function
-
-
^.^ happy coding
I haven't read Kadghar's code, but I believe I can give you a simpler answer to why you're getting an error. In line 68 (I've added a CODE=vb tag so the code is formatted and shows line numbers) I think you'll find that the value of variable i is 1 higher than you expect.
It's a common mistake people make with the FOR loop. Each time around the loop, VB first changes the value of the loop counter, then checks it to see whether to continue looping. The result is that if for example you execute For K = 1 To 10, then K will end up with the value 11 once the loop is completed.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: David |
last post by:
I have an array that contains numbers in string elements , i want to convert
this to integers, i have made a for loop that uses the number of elements in
the array and then adds 0, thereby...
|
by: YFS DBA |
last post by:
Hello again;
I've got a small invoicing database I'm trying to create.
One of the tables is set up as follows:
Client# Date Claim# Amount
1001 10/10/03 ...
|
by: MattB |
last post by:
I have an one dimensional array being created from a delimited list using
string.split. Now I'd like to take that array and add another dimension and
manually put a value in there based on the...
|
by: Dennis |
last post by:
I am trying to use ZLIB.Dll in a VB.Net project but keep getting an error message that says it can't load the DLL. I tried to add a reference using "Project-Add Reference" but get the error message...
|
by: anonymous |
last post by:
I have couple of questions related to array addresses. As they belong
to the same block, I
am putting them here in one single post. I hope nobody minds:
char array;
int address;
Questions...
|
by: Water Cooler v2 |
last post by:
I know that we can add a single name value entry in app.config or
web.config in the configuration/configSettings/appSettings section like
so:
<add key="key" value="value" />
Question: I want...
|
by: Paulers |
last post by:
Hello,
I have a string that I am trying to add each char to a datatable row.
for example if I have a string that looks like "abcdefg", I would like
to break it up into an array of characters...
|
by: JonJacobs |
last post by:
When I add a series of byte arrays to an array list, then I read them back, all the arraylist byte array elements are identical to the last byte array entry. What is wrong? The following code will...
|
by: cmdolcet69 |
last post by:
How can i add a 5ms delay between bytes i send over to a controller?
I have to send 6 bytes in total however in between each byte i need to
have a 5ms delay.
|
by: santiago |
last post by:
I guess one cannot do this:
arraytot = arraytot + arraydet;
So, what's the trick to adding arrays like this?
Thanks.
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |