I figured out how to get basic statistical data out of a 1-dimensional Array in VBA. It is actually pretty simple for 1-dimensional arrays just using the built-in worksheet functions.
For example, if you have 1000 results in a 1-dimensional array called Results_Array [Dim Results_Array (1 to 1000)] The average all 1000 numbers in Results_Array is: Application.WorksheetFunction.Average(Results_Array) This works fine for built-in functions such as Average, Sum, StDevP, Min, Max, etc. What I cannot get to work are the "IFS" functions, such as COUNTIFS, SUMIFS, etc. This would be very useful to obtain a distribution of the results. For example, how may in Results_Arrary are >=500? I tried Application.WorksheetFunction.CountIfs(Results_Array, ">=500"), just like it would be done outside of VBA in Excel 2007, but this only returns an error. Can't figure out what I'm doing wrong. Also I cannot figure out how to get any built in functions to work on multi-dimensional arrays. For example if the Results_Array now is setup to capture results, say price and quantity for 1000 scenarios (Dim Results_Array (1 to 1000, 1,1). 1 to 1000 being the number of each scenario analyzed and 1,1 being price and quantity results respectively. How do I get the average or price only or the quantity only, or average of price for results 1 to 500 and so on? Thanks. On Mar 23, 2:26 pm, excelCPA <[email protected]> wrote: > I have just begun to work with arrays in Excel VBA and am struggling > how to obtain basic statistics about the array. > > For example > Dim Results_Array(1 to 10) As Double > > I run a loop that calculates 10 numbers and places the results in > places 1 - 10 in the array. > > How can I compute the average, sum, min, max, and standard deviation > of the completed array? Is there a way to compute the same statistics > for only a portion, say results 5-10? > > Something like Average(Results_Array(1 to 10)) > > Thanks. -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to [email protected] <><><><><><><><><><><><><><><><><><><><><><> HELP US GROW !! We reach over 6,800 subscribers worldwide and receive many nice notes about the learning and support from the group.Let friends and co-workers know they can subscribe to group at http://groups.google.com/group/excel-macros/subscribe To unsubscribe from this group, send email to excel-macros+unsubscribegooglegroups.com or reply to this email with the words "REMOVE ME" as the subject.
