|
Highline Excel Class 39: Forcing Functions To Become Arrays
Video | Similar Helpful Excel Resources
If an array or range is put into a function argument that is expecting a single value, the function becomes an array and delivers an array of values instead of a single value. See these amazing tricks:
1)Count Unique values with SUMPRODUCT & COUNTIF functions array formula
2)Lookup Adding with SUMPRODUCT &SUMIF functions array formula
3)MEDIAN IF functions in array formula (calculating Median with conditions (criteria))
4)MAX IF functions in array formula (calculating maximum value with conditions (criteria))
5)MIN IF functions in array formula (calculating minimum value with conditions (criteria))
6)Adding (summing sum) top three values using SUM & LARGE functions with an array in array sytax
7)Conditional (criteria) adding & counting for dates with the TEXT & SUMPRODUCT functions
This is a beginning to advanced Excel class taught at Highline Community College by Mike Gel excelisfun Girvin Busn 214 BTech 109
Got a Question? Ask it Here in the Forum.
Similar Helpful Excel Resources
I have populated a multidimensional array, part of which is exampled as
follows:
JAN05 Q105 45.3 12.5 16.6 88.9 etc
FEB05 Q105 55.6 23.6 58.4 65.2
MAR05 Q105 25.7 36.5 98.5 45.7
APR05 Q205 98.4 25.7 23.9 59.7
MAY05 Q205 39.5 56.8 89.2 25.7
etc
I want to be able find and average all numbers in a particular column that
match a designation in Column 2, eg Q105, in the array. This array is not
populated in a worksheet, and nor do I want it to be.
I can do this by looping through every row in the array for a specific
column, but the array is 150 rows by 20 columns but it seems a cumbersome
approach.
Can I do this using the Dbase Worksheet functions that can be used in VBA ?
Advice appreciated.
Does anyone know the best way to input multiple data arrays into Excel functions which have more than one argument?
For example, I have a data set of 1700 rows and 450 columns which I have had to split over two worksheets. I want to calculate the upper quartile of each of the 1700 rows but the QUARTILE function only allows one array argument so you cannont enter array_sheet1,array_sheet2 as it will read array_sheet2 as the quartile argument.
Many thanks
I can't figure this one out. I have a subroutine that calls several UDF's and most of them return array values. Everything works so far.
However, in one of the functions that is called from the subroutine I call another function that in turn is supposed to return an array. However, VBA won't compile saying that I can't assign to array when I attempt to call it (like every other function call) with a statement such as this:
Myarray=function_that_should_return_an_array_value(param1, param2)
Is there something limited this attempt that I can't figure out or am I just too tired to think straight?
The only error that is occurring is the one instance of when I attempt to call another user defined function within another user defined function.
I made my own class so that I could have objects with a Name and Rating attribute. I then made an array of 5 of these objects because I thought this would be a convenient and flexible way of grouping them together. However, I'm getting stuck early, and I think it has something to do with how I'm defining the array. Here's the code:
Code:
Sub Update()
Dim Player(4) As Class1
Player(0).Name = "Player1"
Player(0).Rating = 1000
Player(1).Name = "Player2"
Player(1).Rating = 1000
Player(2).Name = "Player3"
Player(2).Rating = 1000
Player(3).Name = "Player4"
Player(3).Rating = 1000
Player(4).Name = "Player5"
Player(4).Rating = 1000
Range("F1").Value = Player(1).Rating
End Sub
It hangs up immediately, and the error message says something about the object or with block not being set.
If this helps, here's the class definition:
Code:
Private pName As String
Private pRating As Double
Public Property Get Name() As String
Name = pName
End Property
Public Property Let Name(Value As String)
pName = Value
End Property
Public Property Get Rating() As Double
Rating = pRating
End Property
Public Property Let Rating(Value As Double)
pRating = Value
End Property
Hi there,
There is a method in the class I have created that populates the element of an array.
Is it possible to invoke this method from an instance of the object by passing an array byref to it.
For instance the code below illustrates the point:
VB:
'// in a module:
Sub AAATest()
Dim N As Long
StaticArray(1) = 1
StaticArray(2) = 2
StaticArray(3) = 3
aClass.PopulatePassedArray Arr:=StaticArray
For N = LBound(StaticArray) To UBound(StaticArray)
Debug.Print StaticArray(N)
Next N
End Sub
'// in the class module named 'Class'
Public Sub PopulatePassedArray(ByRef Arr() As Long)
''''''''''''''''''''''''''''''''''''
' PopulatePassedArray
' This puts some values in Arr.
''''''''''''''''''''''''''''''''''''
Dim N As Long
For N = LBound(Arr) To UBound(Arr)
Arr(N) = N * 10
Next N
End Sub
If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines
Thanks.
Hi everyone!
I'm trying to make a VBA function that would have two inparameters, one array(double) and one variable(double) and I would like to have an array(double) as output as well. It seems though, that you can only have Variant type output from functions. Does anyone know how to call such a function and how to define it?
Is there something fundamentally wrong with this code or do you think it should work? Prognos is the array (23*6 elements) and MAPE is the variable.
Function utfall(ByVal prognos, ByVal MAPE As Double)
How do you run the function? Should this work:
utfall(pforevert, MAPE) = prealvert
or is it the other way
prealvert = utfall(pforevert, MAPE)
Thanks for any help!
Hi, i've been trying to get the right formula for finding a percentage for the last few days and have simply got nowhere (novice at all this).
What i want to show is,
in the H column the percentage of times the number 2 or < shows in the G column, adding as it go's down the list, and obviously the percentage will need to come down when the number in the G column is > then 2.
Any help would be greatly appreciated. Thank You.
Graham
Literally just started on excel and am trying improve rapidly, so all help is greatly noted. Thanks Again.
Hi,
What I'm trying to do is be able to use excels built in stats functions (specifically 90th percentile) on values stored in an array without having to use any work-arounds. I currently have the program populate a sheet with the values in the array and run the stats on that, but it's a slow work-around.
Any help would be greatly appreciated.
Thanks!
Hello,
I am trying to run a loop, where everytime the cell is blank (value = ""), the array rowValue records the row number (numRow), and then it outputs to the spreadsheet as shown below.
I get an error message saying Run Time Error '9', Subscript Out of Range. Is it possible to set an array equal to the numerical value of a variable at a given moment?
Best regards,
Mercer
*********************************************************
Sub Tester()
Dim numRow As Long
Dim i As Integer
Dim rowValue() As Long
numRow = 0
i = 0
Do Until i = 3
numRow = numRow + 1
If Cells(numRow, 1) = "" Then
i = i + 1
rowValue(i) = i ' Gave me error message here
Else
' Leave Blank
End If
Loop
Range("B1").Value = rowValue(1)
Range("B2").Value = rowValue(2)
Range("B3").Value = rowValue(3)
End Sub
Hello All,
I'm very green with VBA and trying to produce a function that will pass an array between a number of operations and output a single result. Reading through old questions, I think my problem has something to do with loading the initial array, but I can't quite pin it down...
Can anyone point out the errors in the following code?
Code:
Function retvol(val, mult, rets() As Variant) As Double
Dim x As Integer
Dim N As Integer
Dim retsM() As Variant
Dim cSum() As Variant
Dim lnSum() As Variant
Dim sdSum() As Variant
Range(rets).Select
N = Selection.Count
ReDim Preserve rets(1 To N)
ReDim retsM(1 To N)
For x = 1 To N
retsM(x) = rets(x) * mult
Next x
ReDim cSum(0 To N)
cSum(0) = val
For x = 1 To N
cSum(x) = retsM(x) + cSum(x - 1)
Next x
ReDim lnSum(1 To N)
For x = 0 To N - 1
lnSum(x + 1) = cSum(x) / cSum(x + 1)
Next x
ReDim sdSum(1 To N)
For x = 1 To N
sdSum(x) = Log(lnSum(x))
Next x
retvol = WorksheetFunction.StDev(sdSum)
End Function
...my learning curve appreciates any and all advice...
thanks,
J
|
|
