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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|