A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

How to Sum infinite formula



 
 
Thread Tools Display Modes
  #1  
Old April 23rd, 2010, 04:34 AM posted to microsoft.public.excel.worksheet.functions
minyeh
external usenet poster
 
Posts: 29
Default How to Sum infinite formula

I'm doing a transition matrix (hazard rate method) using formula
(trying to avoid using VBA under some company policy), but then, i'm
stuck when it comes to summing infinite formula.

the formula i come out with looks like this
{=L34:T42+L14:T22+MMULT(L14:T22,L14:T22)/
2+MMULT(MMULT(L14:T22,L14:T22),L14:T22)/
6+MMULT(MMULT(MMULT(L14:T22,L14:T22),L14:T22),L14: T22)/24}

where
L34:T42 is something like a {1,0,0; 0,1,0; 0,0,1} matrix (not sure
what this called, i'll just refer to matrix A) but in 9x9
L14:T22 is my Generator Matrix, also in 9x9

the formula should be:
Transition Matrix = Matrix A + T * Generator + ( T^2 / 2! ) *
Generator ^ 2 + ... +(T^infinity / infinity!) * Generator ^ infinity

Where T is # of years, i'm using T = 1 for one-year transition matrix.

for now, i'm doing up to k =4, but there's still a small gap between
what i get with the supposed-to-be actual transition matrix.

need experts' help in this summing infinite formula issue. thanks a
lot.


minyeh
  #2  
Old April 23rd, 2010, 05:01 AM posted to microsoft.public.excel.worksheet.functions
Dana DeLouis[_4_]
external usenet poster
 
Posts: 11
Default How to Sum infinite formula

L34:T42 is something like a {1,0,0; 0,1,0; 0,0,1} matrix (not sure
what this called,


A Matrix with diagonal elements of 1 is called an "Identity Matrix"

http://en.wikipedia.org/wiki/Identity_matrix

I can't quite follow. I would suggest using Range Names to refer to
your ranges.

Where T is # of years
...+ ( T^2 / 2! )


Looks like T is a matrix, and not a number to me.

= = = = = =
Dana DeLouis

On 4/22/10 11:34 PM, minyeh wrote:
I'm doing a transition matrix (hazard rate method) using formula
(trying to avoid using VBA under some company policy), but then, i'm
stuck when it comes to summing infinite formula.

the formula i come out with looks like this
{=L34:T42+L14:T22+MMULT(L14:T22,L14:T22)/
2+MMULT(MMULT(L14:T22,L14:T22),L14:T22)/
6+MMULT(MMULT(MMULT(L14:T22,L14:T22),L14:T22),L14: T22)/24}

where
L34:T42 is something like a {1,0,0; 0,1,0; 0,0,1} matrix (not sure
what this called, i'll just refer to matrix A) but in 9x9
L14:T22 is my Generator Matrix, also in 9x9

the formula should be:
Transition Matrix = Matrix A + T * Generator + ( T^2 / 2! ) *
Generator ^ 2 + ... +(T^infinity / infinity!) * Generator ^ infinity

Where T is # of years, i'm using T = 1 for one-year transition matrix.

for now, i'm doing up to k =4, but there's still a small gap between
what i get with the supposed-to-be actual transition matrix.

need experts' help in this summing infinite formula issue. thanks a
lot.


minyeh

  #3  
Old April 23rd, 2010, 05:13 AM posted to microsoft.public.excel.worksheet.functions
minyeh
external usenet poster
 
Posts: 29
Default How to Sum infinite formula

T is not a matrix, it's a number, when u multiply a number to a
matrix, it multiply every single element in that matrix with the
number, for example, 3 * {1,2,1;,2,3,1;0,1,1} = {3,6,3;6,9,3;0,3,3}.

minyeh

On Apr 23, 12:01*pm, Dana DeLouis wrote:
* L34:T42 is something like a {1,0,0; 0,1,0; 0,0,1} matrix (not sure
* what this called,

A Matrix with diagonal elements of 1 is called an "Identity Matrix"

http://en.wikipedia.org/wiki/Identity_matrix

I can't quite follow. *I would suggest using Range Names to refer to
your ranges.

* Where T is # of years
* ...+ ( T^2 / 2! )

Looks like T is a matrix, and not a number to me.

= = = = = =
Dana DeLouis

On 4/22/10 11:34 PM, minyeh wrote:



I'm doing a transition matrix (hazard rate method) using formula
(trying to avoid using VBA under some company policy), but then, i'm
stuck when it comes to summing infinite formula.


the formula i come out with looks like this
{=L34:T42+L14:T22+MMULT(L14:T22,L14:T22)/
2+MMULT(MMULT(L14:T22,L14:T22),L14:T22)/
6+MMULT(MMULT(MMULT(L14:T22,L14:T22),L14:T22),L14: T22)/24}


where
L34:T42 is something like a {1,0,0; 0,1,0; 0,0,1} matrix (not sure
what this called, i'll just refer to matrix A) but in 9x9
L14:T22 is my Generator Matrix, also in 9x9


the formula should be:
Transition Matrix = Matrix A + T * Generator + ( T^2 / 2! ) *
Generator ^ 2 + ... +(T^infinity / infinity!) * Generator ^ infinity


Where T is # of years, i'm using T = 1 for one-year transition matrix..


for now, i'm doing up to k =4, but there's still a small gap between
what i get with the supposed-to-be actual transition matrix.


need experts' help in this summing infinite formula issue. thanks a
lot.


minyeh- Hide quoted text -


- Show quoted text -

  #4  
Old April 23rd, 2010, 05:46 AM posted to microsoft.public.excel.worksheet.functions
Dana DeLouis[_4_]
external usenet poster
 
Posts: 11
Default How to Sum infinite formula

Oh. Ok. It looks like an interesting problem. :)

When you wrote...
( T^2 / 2! )

and used

MMULT(L14:T22,L14:T22) / 2

It looked to me like you were squaring the Matrix (T: L14:T22), and
dividing by 2!

As a side note:

MMULT(MMULT(MMULT(L14:T22,L14:T22),L14:T22),L14:T2 2)

When you raise a matrix to the 4th power, (or larger) it usually is
better to do it via vba. I know that's not what you want though.
The reason I mention it is that if you wanted to take a matrix to the
32nd power, you have to make a very large worksheet function (32 times)
However, in vba, you can square the results in a loop just 5 times. The
efficiency gets better as the power gets larger.

= = = = = =
HTH :)
Dana DeLouis

On 4/23/10 12:13 AM, minyeh wrote:
T is not a matrix, it's a number, when u multiply a number to a
matrix, it multiply every single element in that matrix with the
number, for example, 3 * {1,2,1;,2,3,1;0,1,1} = {3,6,3;6,9,3;0,3,3}.

minyeh

On Apr 23, 12:01 pm, Dana wrote:
L34:T42 is something like a {1,0,0; 0,1,0; 0,0,1} matrix (not sure
what this called,


A Matrix with diagonal elements of 1 is called an "Identity Matrix"

http://en.wikipedia.org/wiki/Identity_matrix

I can't quite follow. I would suggest using Range Names to refer to
your ranges.

Where T is # of years
...+ ( T^2 / 2! )


Looks like T is a matrix, and not a number to me.

= = = = = =
Dana DeLouis

On 4/22/10 11:34 PM, minyeh wrote:



I'm doing a transition matrix (hazard rate method) using formula
(trying to avoid using VBA under some company policy), but then, i'm
stuck when it comes to summing infinite formula.


the formula i come out with looks like this
{=L34:T42+L14:T22+MMULT(L14:T22,L14:T22)/
2+MMULT(MMULT(L14:T22,L14:T22),L14:T22)/
6+MMULT(MMULT(MMULT(L14:T22,L14:T22),L14:T22),L14: T22)/24}


where
L34:T42 is something like a {1,0,0; 0,1,0; 0,0,1} matrix (not sure
what this called, i'll just refer to matrix A) but in 9x9
L14:T22 is my Generator Matrix, also in 9x9


the formula should be:
Transition Matrix = Matrix A + T * Generator + ( T^2 / 2! ) *
Generator ^ 2 + ... +(T^infinity / infinity!) * Generator ^ infinity


Where T is # of years, i'm using T = 1 for one-year transition matrix.


for now, i'm doing up to k =4, but there's still a small gap between
what i get with the supposed-to-be actual transition matrix.


need experts' help in this summing infinite formula issue. thanks a
lot.


minyeh- Hide quoted text -


- Show quoted text -

  #5  
Old April 23rd, 2010, 08:09 AM posted to microsoft.public.excel.worksheet.functions
minyeh
external usenet poster
 
Posts: 29
Default How to Sum infinite formula

On Apr 23, 12:46*pm, Dana DeLouis wrote:
Oh. *Ok. *It looks like an interesting problem. *:)

When you wrote...

( T^2 / 2! )

and used

MMULT(L14:T22,L14:T22) / 2

It looked to me like you were squaring the Matrix (T: L14:T22), and
dividing by 2!

As a side note:

MMULT(MMULT(MMULT(L14:T22,L14:T22),L14:T22),L14:T2 2)

When you raise a matrix to the 4th power, (or larger) it usually is
better to do it via vba. *I know that's not what you want though.
The reason I mention it is that if you wanted to take a matrix to the
32nd power, you have to make a very large worksheet function (32 times)
However, in vba, you can square the results in a loop just 5 times. *The
efficiency gets better as the power gets larger.

= = = = = =
HTH *:)
Dana DeLouis

On 4/23/10 12:13 AM, minyeh wrote:



T is not a matrix, it's a number, when u multiply a number to a
matrix, it multiply every single element in that matrix with the
number, for example, 3 * {1,2,1;,2,3,1;0,1,1} = {3,6,3;6,9,3;0,3,3}.


minyeh


On Apr 23, 12:01 pm, Dana *wrote:
* *L34:T42 is something like a {1,0,0; 0,1,0; 0,0,1} matrix (not sure
* *what this called,


A Matrix with diagonal elements of 1 is called an "Identity Matrix"


http://en.wikipedia.org/wiki/Identity_matrix


I can't quite follow. *I would suggest using Range Names to refer to
your ranges.


* *Where T is # of years
* *...+ ( T^2 / 2! )


Looks like T is a matrix, and not a number to me.


= = = = = =
Dana DeLouis


On 4/22/10 11:34 PM, minyeh wrote:


I'm doing a transition matrix (hazard rate method) using formula
(trying to avoid using VBA under some company policy), but then, i'm
stuck when it comes to summing infinite formula.


the formula i come out with looks like this
{=L34:T42+L14:T22+MMULT(L14:T22,L14:T22)/
2+MMULT(MMULT(L14:T22,L14:T22),L14:T22)/
6+MMULT(MMULT(MMULT(L14:T22,L14:T22),L14:T22),L14: T22)/24}


where
L34:T42 is something like a {1,0,0; 0,1,0; 0,0,1} matrix (not sure
what this called, i'll just refer to matrix A) but in 9x9
L14:T22 is my Generator Matrix, also in 9x9


the formula should be:
Transition Matrix = Matrix A + T * Generator + ( T^2 / 2! ) *
Generator ^ 2 + ... +(T^infinity / infinity!) * Generator ^ infinity


Where T is # of years, i'm using T = 1 for one-year transition matrix.


for now, i'm doing up to k =4, but there's still a small gap between
what i get with the supposed-to-be actual transition matrix.


need experts' help in this summing infinite formula issue. thanks a
lot.


minyeh- Hide quoted text -


- Show quoted text -- Hide quoted text -


- Show quoted text -


Ya, i know that limitation of using only the formula, am still
convincing IT dept to change their policy on that, but the approval
would take a while. if you can help with VBA, it'll be welcome as
well : )
  #6  
Old April 25th, 2010, 12:35 AM posted to microsoft.public.excel.worksheet.functions
Dana DeLouis[_3_]
external usenet poster
 
Posts: 184
Default How to Sum infinite formula

Hi. You sent me a message with a 9 * 9 Input matrix, and the 9 * 9
solution.
This vba solution inputs your data from A1:I9, and outputs the data
below that beginning in A12.

The matrix converges quickly for a 1-Year. I only looped 20 times (not
infinity) and got the same solution.

I normally would call functions in my library. Therefore, this is a
quick and dirty way to do it. Because the solution was in percent, I
multiplied the solution by 100.

This is just one of a few ways to show how a vba solution is, what I
think, is much easier to handle.

Sub Convergence()
Dim Y As Long
Dim J As Long
Dim Fx
Set Fx = WorksheetFunction

Y = 1 '1 Year

With ActiveWorkbook.Names
.Add "Orig", [A1].Resize(9, 9).Value
.Add "M", [A1].Resize(9, 9).Value
.Add "Ans", IdentityMatrix(9)
.Add "Ans", [Ans+M]

For J = 2 To 20
.Add "k", Y / Fx.Fact(J) ' T/j!
.Add "M", [MMult(M, Orig)] 'Matrix to the next power
.Add "Ans", [Ans + k*M] 'Sum
Next J
.Add "Ans", [Ans*100]
[A12].Resize(9, 9) = [Ans]
End With

End Sub

Function IdentityMatrix(n)
Dim s
s = "0+(Row(#)=Column(#))"
s = Replace(s, "#", [A1].Resize(n, n).Address)
IdentityMatrix = Evaluate(s)
End Function

= = = = = = = = = =
HTH
Dana DeLouis



On 4/23/2010 3:09 AM, minyeh wrote:
On Apr 23, 12:46 pm, Dana wrote:
Oh. Ok. It looks like an interesting problem. :)

When you wrote...

( T^2 / 2! )

and used

MMULT(L14:T22,L14:T22) / 2

It looked to me like you were squaring the Matrix (T: L14:T22), and
dividing by 2!

As a side note:

MMULT(MMULT(MMULT(L14:T22,L14:T22),L14:T22),L14:T2 2)

When you raise a matrix to the 4th power, (or larger) it usually is
better to do it via vba. I know that's not what you want though.
The reason I mention it is that if you wanted to take a matrix to the
32nd power, you have to make a very large worksheet function (32 times)
However, in vba, you can square the results in a loop just 5 times. The
efficiency gets better as the power gets larger.

= = = = = =
HTH :)
Dana DeLouis

On 4/23/10 12:13 AM, minyeh wrote:



T is not a matrix, it's a number, when u multiply a number to a
matrix, it multiply every single element in that matrix with the
number, for example, 3 * {1,2,1;,2,3,1;0,1,1} = {3,6,3;6,9,3;0,3,3}.


minyeh


On Apr 23, 12:01 pm, Dana wrote:
L34:T42 is something like a {1,0,0; 0,1,0; 0,0,1} matrix (not sure
what this called,


A Matrix with diagonal elements of 1 is called an "Identity Matrix"


http://en.wikipedia.org/wiki/Identity_matrix


I can't quite follow. I would suggest using Range Names to refer to
your ranges.


Where T is # of years
...+ ( T^2 / 2! )


Looks like T is a matrix, and not a number to me.


= = = = = =
Dana DeLouis


On 4/22/10 11:34 PM, minyeh wrote:


I'm doing a transition matrix (hazard rate method) using formula
(trying to avoid using VBA under some company policy), but then, i'm
stuck when it comes to summing infinite formula.


the formula i come out with looks like this
{=L34:T42+L14:T22+MMULT(L14:T22,L14:T22)/
2+MMULT(MMULT(L14:T22,L14:T22),L14:T22)/
6+MMULT(MMULT(MMULT(L14:T22,L14:T22),L14:T22),L14: T22)/24}


where
L34:T42 is something like a {1,0,0; 0,1,0; 0,0,1} matrix (not sure
what this called, i'll just refer to matrix A) but in 9x9
L14:T22 is my Generator Matrix, also in 9x9


the formula should be:
Transition Matrix = Matrix A + T * Generator + ( T^2 / 2! ) *
Generator ^ 2 + ... +(T^infinity / infinity!) * Generator ^ infinity


Where T is # of years, i'm using T = 1 for one-year transition matrix.


for now, i'm doing up to k =4, but there's still a small gap between
what i get with the supposed-to-be actual transition matrix.


need experts' help in this summing infinite formula issue. thanks a
lot.


minyeh- Hide quoted text -


- Show quoted text -- Hide quoted text -


- Show quoted text -


Ya, i know that limitation of using only the formula, am still
convincing IT dept to change their policy on that, but the approval
would take a while. if you can help with VBA, it'll be welcome as
well : )



--
= = = = = = =
HTH :)
Dana DeLouis
  #7  
Old April 25th, 2010, 09:43 PM posted to microsoft.public.excel.worksheet.functions
Dana DeLouis[_3_]
external usenet poster
 
Posts: 184
Default How to Sum infinite formula

Hi. Here is a slight addition that you can play with if you wish.
I just modified this portion so we can watch the convergence.
I also eliminated multiplying the answer by 100.
Just format the output as percentage to 3 decimal places does the same
thing!

For J = 2 To 20
.Add "k", Y / Fx.Fact(J) ' T/j!
.Add "M", [MMult(M, Orig)] 'Matrix to the next power

.Add "Old", [Ans]
.Add "Ans", [Ans + k*M] 'Sum
Debug.Print J; Fx.SumXMY2([Old], [Ans])
Next J
[A12].Resize(9, 9) = [Ans]
End With
End Sub

The output is:
2 0.03359136055325
3 9.2432006386492E-04
4 1.54269946074966E-05
5 1.72307843099531E-07
6 1.35605071782519E-09
7 7.91983983820038E-12
8 3.54955567034313E-14
9 1.25992350330859E-16
10 3.62323283207642E-19
11 8.61641664106313E-22
12 1.72167126878345E-24
13 2.93590834028579E-27
14 4.50356058499773E-30
15 1.36904585529777E-32
16 4.48415508583941E-44
17 0
18 0
19 0
20 0

We can see that around 8-9 loops is all that's needed.
One can modify the code to exit once the difference gets below a certain
level.

= = = = =
HTH
Dana DeLouis

snip
  #8  
Old April 26th, 2010, 04:39 AM posted to microsoft.public.excel.worksheet.functions
minyeh
external usenet poster
 
Posts: 29
Default How to Sum infinite formula

On Apr 26, 4:43*am, Dana DeLouis wrote:
Hi. *Here is a slight addition that you can play with if you wish.
I just modified this portion so we can watch the convergence.
I also eliminated multiplying the answer by 100.
Just format the output as percentage to 3 decimal places does the same
thing!

* * * * *For J = 2 To 20
* * * * * * *.Add "k", Y / Fx.Fact(J) * *' T/j!
* * * * * * *.Add "M", [MMult(M, Orig)] *'Matrix to the next power

* * * * * * *.Add "Old", [Ans]
* * * * * * *.Add "Ans", [Ans + k*M] * * 'Sum
* * * * * * *Debug.Print J; Fx.SumXMY2([Old], [Ans])
* * * * *Next J
* * * * *[A12].Resize(9, 9) = [Ans]
* * *End With
End Sub

The output is:
* 2 *0.03359136055325
* 3 *9.2432006386492E-04
* 4 *1.54269946074966E-05
* 5 *1.72307843099531E-07
* 6 *1.35605071782519E-09
* 7 *7.91983983820038E-12
* 8 *3.54955567034313E-14
* 9 *1.25992350330859E-16
* 10 *3.62323283207642E-19
* 11 *8.61641664106313E-22
* 12 *1.72167126878345E-24
* 13 *2.93590834028579E-27
* 14 *4.50356058499773E-30
* 15 *1.36904585529777E-32
* 16 *4.48415508583941E-44
* 17 *0
* 18 *0
* 19 *0
* 20 *0

We can see that around 8-9 loops is all that's needed.
One can modify the code to exit once the difference gets below a certain
level.

= = = = =
HTH
Dana DeLouis

snip


checking the convergence is a good idea, would be very helpful in
determining how many loops required as a cut-off point. : ) Thanks a
lot really, you have been very helpful Dana.


Regards,
Min Yeh
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:09 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.