473,327 Members | 2,065 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Adding nth integer in an array

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

Expand|Select|Wrap|Line Numbers
  1. Sub stats()
  2.  
  3. periods = Range("periods")
  4. variables = Range("variables")
  5.  
  6. ReDim X(periods, variables) As Double
  7. ReDim uX(variables) As Double
  8. ReDim sdX(variables) As Double
  9. ReDim tran(variables, periods) As Double
  10.  
  11. Sheet1.Activate
  12.  
  13. 'Load Returns
  14. For i = 1 To variables
  15. For t = 1 To periods
  16.   X(t, i) = Cells(4 + t, 2 + i)
  17. Next
  18. Next
  19.  
  20. 'Calculate Mean (run 'mean' function')
  21. uX() = mean(X)
  22.  
  23. 'Calculate Standard Deviation of Returns (run 'sdev' function)
  24. sdX() = Sdev(X)   '***Needs work***
  25.  
  26.  
  27. End Sub
  28.  
  29.  
  30. Function mean(X() As Double) As Variant
  31. 'Function is used to calculate the mean of each variable
  32.  
  33. periods = Range("periods")
  34. variables = Range("variables")
  35.  
  36. ReDim u(variables) As Double
  37.  
  38. For i = 1 To variables
  39.   For t = 1 To periods
  40.     u(i) = sum + X(t, i)
  41.     sum = u(i)
  42.   Next
  43.   u(i) = sum / periods
  44.   sum = 0
  45. Next
  46.  
  47. mean = u()
  48.  
  49. End Function
  50.  
  51. Function Sdev(X() As Double) As Variant
  52. 'Function is used to calculate the standard deviation of each variable
  53.  
  54. periods = Range("periods")
  55. variables = Range("variables")
  56.  
  57. ReDim sd(variables) As Double
  58.  
  59. For i = 1 To variables
  60.   For t = 1 To periods
  61.     sd(i) = sum + (X(t, i) - mean(X(i))) ^ 2
  62.     sum = sd(i)
  63.   Next
  64.   sd(i) = Sqr(sum / (periods - 1))
  65.   sum = 0
  66. Next
  67.  
  68. Sdev = sd(i)
  69.  
  70. End Function
Mar 3 '08 #1
2 1476
kadghar
1,295 Expert 1GB
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):

Expand|Select|Wrap|Line Numbers
  1. Sub something()
  2. Dim MainArray
  3. Dim Means
  4. Dim SDevs
  5. 'The code to read in the MainArray your values, i defined MainArray as a variant, so you can do something like:
  6.  
  7. With Worksheets("sheet1")
  8.     MainArray = Range(.Cells(1, 1), .Cells(50, 3)) 'i'll use [value, period] because i suppose the periods are in columns.
  9. End With
  10. Means = Mean(MainArray)
  11. SDevs = SDev(MainArray)
  12. End Sub
  13.  
  14. 'These are the functions
  15. Public Function Mean(ByVal a)
  16. Dim i As Long: Dim j As Long
  17. Dim Arr1() As Double
  18. ReDim Arr1(1 To UBound(a, 2))
  19. For i = 1 To UBound(a, 2)
  20.     For j = 1 To UBound(a)
  21.         Arr1(i) = Arr1(i) + a(j, i)
  22.     Next
  23.     Arr1(i) = Arr1(i) / (UBound(a))
  24. Next
  25. Mean = Arr1
  26. End Function
  27.  
  28. Public Function SDev(ByVal a)
  29. Dim i As Long: Dim j As Long
  30. Dim Arr1() As Double
  31. Dim Aux
  32. ReDim Arr1(1 To UBound(a, 2))
  33. Aux = Mean(a)
  34. For i = 1 To UBound(a, 2)
  35.     For j = 1 To UBound(a)
  36.         Arr1(i) = Arr1(i) + (a(j, i) - Aux(i)) ^ 2
  37.     Next
  38.     Arr1(i) = (Arr1(i) / (UBound(a) - 1)) ^ (0.5)
  39. Next
  40. SDev = Arr1
  41. End Function
  42.  
  43.  
^.^ happy coding
Mar 3 '08 #2
Killer42
8,435 Expert 8TB
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.
Mar 4 '08 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

7
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...
2
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 ...
4
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...
6
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...
28
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...
5
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...
6
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...
0
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...
23
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.
6
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.
0
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...
0
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...
0
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...
1
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)...
1
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...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
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....
0
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
0
isladogs
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.