Help to define a Function. Easy for those who know how to do

[Actuariojf]

Hello everybody,

I would like to build a "simple" function, something like MAT(x,y,z), where the values of x and y are single cells addresses and z is an integer, so if I calculate for example MAT(N12,L12,z) the function does the following:

SUMPRODUCT($N$12:INDIRECT(ADDRESS(ROW($N$12)+Z-1,COLUMN($N$12))),N(OFFSET($L$12,ROW($L$12)+ROWS($L$12:INDIRECT(ADDRESS(ROW($L$12)+Z-1,COLUMN($L$12))))-ROW($L$12:INDIRECT(ADDRESS(ROW($L$12)+V12-1,COLUMN($L$12))))-1,0)))

I do not know how to define or dim or setup the variables to do that. Can somebody help me?

Thanks!

Actuariojf
This message was edited by Actuariojf on 2002-09-05 10:05

 

[WillR]

The one thing that I will say is that if you're going to use ALOT of these formulas, and you already have a combination of standard functions that work fine (albeit look messy), then i'd stick with what you've got. You will clean up you're formula bar alright BUT possibly at the expense of SLOOOOWING down your worksheet recalculation times...

Just a thought.

Regards,

 

[Actuariojf]

WillR,

You are right in your comments, but I plan to use this formula in a most dificult taks that actually will save time and room in the excel workbook. So, please help me to set up the function. Besides, I always think that the best way to learn is trial and error.

Thanks!

 

[Aladin Akyurek]

On 2002-09-05 10:25, Actuariojf wrote:
WillR,

You are right in your comments, but I plan to use this formula in a most dificult taks that actually will save time and room in the excel workbook. So, please help me to set up the function. Besides, I always think that the best way to learn is trial and error.

Thanks!

A UDF, called REVERSE, would be more effective than a chain of volatile built-in functions:

REVERSE({1,2,3}) ==> {3,2,1}

REVERSE({1;2;3}) ==> {3;2;1}

REVERSE({"bob"}) ==> {"bob"}

It should work of course with array constants like {"a";"b") as well as ranges like A1:A3. I hope someone from the VBA army around here would want to take the challenge.

 

[Jay Petrulis]

Hi,

Will any of Alan Beban's array functions be of use? I just checked and there is no ArrayReverse or such, but Alan B could probably supply a terrific answer.

Here is one way to do the REVERSE function, although...

REVERSE({1;2;3}) is not working properly. With a comma separator, it works fine.

Note that if you send an array to the function, the result must be input as a worksheet array.

This is also the case if passing a range to the function.

Somehow, I don't think that is what is desired, so I will keep trying.

ACTUARIOJF -- I am also trying to solve your question, but can you tell me what your formula is trying to do?<pre>Function REVERSE(InputArray)
Dim RowCount As Long, ColCount As Integer
Dim i As Long, j As Long
Dim RangeArray


If TypeName(InputArray) = "Double" Or _
TypeName(InputArray) = "String" Then
For i = Len(InputArray) To 1 Step -1
RangeArray = RangeArray & Mid(InputArray, i, 1)
REVERSE = RangeArray + IIf(TypeName(InputArray) = "Double", 0, "")
Next i
End If

If IsArray(InputArray) Then
ReDim RangeArray(1 To UBound(InputArray) - LBound(InputArray) + 1)
For i = LBound(InputArray) To UBound(InputArray)
RangeArray(i) = InputArray(UBound(InputArray) - i + 1)
Next i
End If

If TypeName(InputArray) = "Range" Then
With InputArray
RowCount = .Rows.Count
ColCount = .Columns.Count
If .Cells.Count = 1 Then
REVERSE = InputArray
Exit Function
Else
ReDim RangeArray(1 To RowCount, 1 To ColCount)
For j = 1 To ColCount
For i = 1 To RowCount
RangeArray(i, j) = InputArray(RowCount - i + 1, ColCount - j + 1)
Next i
Next j
End If
End With


End If
REVERSE = RangeArray
End Function</pre>

_________________
Bye,
Jay
This message was edited by Jay Petrulis on 2002-09-05 14:08

 

[Actuariojf]

Thanks a lot for your help!

This current question is actually a continuation of a earlier posting. Jay you already developed a function for me on 08/30/02 under the title:

"A challenge for excel gurus - Sumproduct does not work!"
http://www.mrexcel.com/board/viewtopic.php?topic=20718&forum=2

If you see the formula in this (current)posting is Aladin's formula suggested in the original posting but "enhanced" by me. Now, what I actually have is 180 lines in a two-column matrix that represent 15 years in months.

In conclusion, What I would like to do is apply the Aladin's "enhanced" formula to add up 12 rows (months) at a time, it means apply 12 times at once Aladin's "enhanced" formula using a UDF function. So, by developing this new function I will be able to have at once the value of 12 months projected production for an insurance portfolio in one cell using just one "easy" function.

This "enhanced" formula has only three variables. In my opinion, the z variable will do the trick for the 12-month addition using a For.. Next loop. The result of this formula represent the projected production of one whole year and in my situation by using this formula a 15x15 matrix will substitute a larger matrix of 180x180. Even if this "enhanced" formula looks messy, I will be saving room and time by using this formula. However, you are the experts and I am always willing to follow the advice of the experts.

Finally, I liked a lot your solution of the original posting, so I decided to try Aladin's "enhanced" formula combined with your idea of using a UDF. I think it will do the work for me, but I do not know how to set up the fuction and its input variables of this new "enhanced" formula to work as an UDF.

Sorry for my English! English is not my mother tongue!

Thanks a lot!

Actuariojf
This message was edited by Actuariojf on 2002-09-05 15:17
This message was edited by Actuariojf on 2002-09-05 15:19

 

[Jay Petrulis]

Hi,

Will the following work? It calls the SUMPRODUCT_INVERT function from the other thread in the loop.

<pre>Option Explicit

Function MAT(x As Range, y As Range, z As Integer) As Double
Dim fn As WorksheetFunction
Dim LoopCount As Long, i As Long
Dim Rng1 As Range, Rng2 As Range

Set fn = Application.WorksheetFunction
LoopCount = fn.Max(1, x.Rows.Count z)

For i = 1 To LoopCount
With Application.Caller.Worksheet

Set Rng1 = .Range(.Cells(x.Row + (i - 1) * z, x.Column), _
.Cells(x.Row + i * z - 1, x.Column))

Set Rng2 = .Range(.Cells(y.Row + (i - 1) * z, y.Column), _
.Cells(y.Row + i * z - 1, y.Column))

End With
MAT = MAT + SUMPRODUCT_INVERT(Rng1, Rng2)
Next i
End Function




Function SUMPRODUCT_INVERT(Rng1 As Range, Rng2 As Range) As Double
Dim fn As WorksheetFunction
Dim i As Long, Arr1, Arr2
Dim TotalSum As Double

Set fn = Application.WorksheetFunction

If Rng1.Cells.Count <> Rng2.Cells.Count Then
SUMPRODUCT_INVERT = CVErr(xlErrValue)
Exit Function
End If

If Rng1.Cells.Count = 1 Then
SUMPRODUCT_INVERT = Rng1 * Rng2
Exit Function
End If

Arr1 = Rng1.Value
Arr2 = Rng2.Value

For i = LBound(Arr1, 1) To UBound(Arr1, 1)
If IsNumeric(Arr1(i, 1)) And IsNumeric(Arr2(UBound(Arr2, 1) - i + 1, 1)) Then
TotalSum = TotalSum + Arr1(i, 1) * Arr2(UBound(Arr2, 1) - i + 1, 1)
End If
Next i

SUMPRODUCT_INVERT = TotalSum

End Function</pre>

The LoopCount variable uses the Integer division operator, but the board sometimes duplicates it. There should only be one backslash.

Call this as
=MAT(A1:A180,B1:B180,12) for instance.

This requires that there are no blanks in the data. A full 12 months (or z) must be filled in for each loop or this will return erroneous results, as the last few items will be multipled by blanks = 0. That can be fixed as needed, but let me know if this works.

I will leave the worksheet solutions to Aladin and the crowd, as I will bludgeon any attempt to get it working.

BTW, you may be able to use a named formula as an Excel solution. That will give you the speed of Excel and the "easy" function to type that you want.

 

[Actuariojf]

Thank a lot for your help! I have learned a lot about how to set up functions. The formula that you provided did not work, but it was not your fault, I did not give all the facts or I did not explain my situation very well. Anyway, I was able to develop my solution. The restrains are that it only works for arrays with 12xn long and that's is fine for me.

This is my solution, if you have any suggestion to improve it, let me know it.

Option Base 1

Function MAT(Rng1 As Range, Rng2 As Range, Y As Variant, Z As Variant) As Double

Dim fn As WorksheetFunction

Dim i As Long, Array1, Array2

Dim TotalSum As Double

Set fn = Application.WorksheetFunction

If Rng1.Cells.Count <> Rng2.Cells.Count Then

MAT = CVErr(xlErrValue)
Exit Function

End If

Array1 = Rng1.Value
Array2 = Rng2.Value

LB1 = 12 * (Y - 1) + 1
UB1 = 12 * Y

LB2 = 12 * (Z - 1) + 1
UB2 = 12 * Z

For J = LB2 To UB2

If Y <> Z Then UB = UB1 Else UB = J

For i = LB1 To UB

TotalSum = TotalSum + Array1(i, 1) * Array2(J - i + 1, 1)
Next i

Next J

MAT = TotalSum

End Function

Thanks again for your help, without the examples that you provided to me I was not be able to develop this solution. I took your function as my starting point.

On the other hand, Aladin solution to the first posting is oustanding and I will be using that formula a lot from now on with other purposes.

Is it posible to develop a regular excel formula similar to the one at the beginning of this posting, I mean just a combination of built-in excel functions, that would do the same as the last function MAT? I think this is really tough to do.

Thanks!

Actuariojf

 

[davers5]

I wasn't quite sure what you wanted at first because I don't know the sumproduct function that well. Anyway, I read your original post and wrote a function that does what you asked. It's fairly simply, the lines that start with an apostrophe (') have comments in them to help understand what's happening. The loop is a for each... next loop which is similar to a for next loop but works with objects. This function should be faster because it does not read all the values into an array (so it saves memory). I also took out the integer because I wasn't sure what you needed to use that for. It works for all sizes of ranges.

Function InverseMultiplyColumns(ByVal Range1 As Range, _
ByVal Range2 As Range) As Double

Dim intCells_Range1 As Integer
Dim intCells_Range2 As Integer
Dim c As Range
Dim n As Integer

'count cells in range
intCells_Range1 = Range1.Cells.Count
intCells_Range2 = Range2.Cells.Count

'check to see ranges have equal amounts of cells
If intCells_Range1 = intCells_Range2 Then
For Each c In Range1
'multiply values in first range by values in second range and assign to function
MultiplyColumns = MultiplyColumns + c.Value * Range2.Cells(intCells_Range2 - n).Value
n = n + 1
Next c
Else
'set cell to -1 if ranges do not have same amount of cell
MultiplyColumns = -1
'give error message in message box
MsgBox "Error in Range Selection, Cell value will be set to -1", , "ERROR"
End If
End Function

Hope this helps. Let me know if you have questions (or if the integer actually did something and you need to add it back).

Dave S.
This message was edited by davers5 on 2002-09-06 09:56

 

[davers5]

Sorry, I was very complacent. I changed the name of the function and then forgot to change its assignment names. Here's the correct function.

Function InverseMultiplyColumns(ByVal Range1 As Range, _
ByVal Range2 As Range) As Double

Dim intCells_Range1 As Integer
Dim intCells_Range2 As Integer
Dim c As Range
Dim n As Integer

'count cells in range
intCells_Range1 = Range1.Cells.Count
intCells_Range2 = Range2.Cells.Count

'check to see ranges have equal amounts of cells
If intCells_Range1 = intCells_Range2 Then
For Each c In Range1
'multiply value in first range by value in last range and assign to function
InverseMultiplyColumns = InverseMultiplyColumns + c.Value * Range2.Cells(intCells_Range2 - n).Value
n = n + 1
Next c
Else
'set cell to -1 if ranges do not have same amount of cell
InverseMultiplyColumns = -1
'give error message in message box
MsgBox "Error in Range Selection, Cell value will be set to -1", , "ERROR"
End If
End Function

I apologize for the confusion.

Dave S.