Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Average
Hi Im trying to figure out why:
Dim j As Variant, x As Variant, k as variant Dim arr(15) As Single For x = 1 To 15 arr(j) = 5 j = j + 1 Next x j = Application.WorksheetFunction.Average(arr) k = Application.WorksheetFunction.StDev(arr), does not return the expected results of 5 and 0. I get 4.68 and 1.25. Any Ideas. Regards Robert |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Average
Rob,
By default, arrays are 0-based, and your variable j is initially zero, so you are actually working with 15 5's and 1 zero. Either use Dim arr(1 to 15) As Single or put Option Base 1 at the top of your module. Below is a fixed version... HTH, Bernie MS Excel MVP Sub NewSub() Dim j As Integer Dim x As Variant Dim k As Variant Dim arr(1 To 15) As Single For x = 1 To 15 arr(x) = 5 Next x j = Application.WorksheetFunction.Average(arr) k = Application.WorksheetFunction.StDev(arr) MsgBox j & " " & k End Sub "RobcPettit" wrote in message oups.com... Hi Im trying to figure out why: Dim j As Variant, x As Variant, k as variant Dim arr(15) As Single For x = 1 To 15 arr(j) = 5 j = j + 1 Next x j = Application.WorksheetFunction.Average(arr) k = Application.WorksheetFunction.StDev(arr), does not return the expected results of 5 and 0. I get 4.68 and 1.25. Any Ideas. Regards Robert |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Average
Because the array has 16 elements not 15.
For x = 0 To 15 works. An array starts on zero unless you have set it to 1 in an Option Base Statement. See help for more info on this. Regards, Ossiemac "RobcPettit" wrote: Hi Im trying to figure out why: Dim j As Variant, x As Variant, k as variant Dim arr(15) As Single For x = 1 To 15 arr(j) = 5 j = j + 1 Next x j = Application.WorksheetFunction.Average(arr) k = Application.WorksheetFunction.StDev(arr), does not return the expected results of 5 and 0. I get 4.68 and 1.25. Any Ideas. Regards Robert |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Average
Thankyou both for your replys. Once I added the option base that
solved my problem. Thankyou for the explanation, this is something I can be aware of in the future. Regards Robert |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
A formula to AVERAGE IF but only average a set number of values | Excel Worksheet Functions | |||
Find monthly average but have average automatically configured | Excel Discussion (Misc queries) | |||
Error Handling #N/A with AVERAGE Function - Average of values in Row | Excel Worksheet Functions | |||
Weighed Average of a weiged average when there are blanks | Excel Discussion (Misc queries) | |||
how does one convert text to a formula "average(A:A)" to =average( | Excel Worksheet Functions |