+ Reply to Thread
Results 1 to 8 of 8

Calculation Efficiency

Hybrid View

  1. 10-01-2015, 07:45 AM #1
    cmore
    cmore is offline
    Forum Contributor
    Join Date
    07-30-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    476

    Calculation Efficiency

    Hi

    Please don't murder me, if this is worded poorly. So I have this project that you push a button and write named ranges create sheets and write formulas to said sheets. There are about 8 formulas that push to 23 columns that have arrays formed from MMULT, SUMIFS, and just regular Boolean logic.

    Outside of this the code runs really fast relatively and the workbook calculates "normally". The problem, I think, is that the array formulas have an inherent dependency so for instance, I have 4 or 5 formulas that feed the array IC1 and similarily for OC1 and then same thing for IC2 and OC2.

    IC1 affects values for OC1 and IC2

    OC1 affects OC2

    IC2 affects OC2

    I cannot figure out the proper configuration to allow the arrays to not destroy the calcs. I've tried to eliminate as much volatility as possible and the workbook itself is less than 1 MB after running the code and less than .5MB beforehand. Any thoughts?
    Reply With QuoteRegister To Reply
  2. 10-01-2015, 07:50 AM #2
    cmore
    cmore is offline
    Forum Contributor
    Join Date
    07-30-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    476

    Re: Calculation Efficiency

    Here are the formulas

    Feeder Formulas
        ThisWorkbook.Names.Add "SheetNameFormula", RefersTo:="=MID(CELL(""filename"",EVALUATE(ADDRESS(ROW()+1,COLUMN()+1))),FIND(""]"",CELL(""filename"",EVALUATE(ADDRESS(1,COLUMN()+1))))+1,255)"
    ThisWorkbook.Names.Add "Col.Header", RefersTo:="=EVALUATE(ADDRESS(ModelStartRow-1,COLUMN()))"
     ThisWorkbook.Names.Add "Col.Header.Filtered", RefersTo:="=TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(Col.Header),""I/C"",),""O/C"",),""(Interest Payment)"",),""(Principal Payment)"",),""(After Previous)"",),""Cure"",),""Test Pass?"",),""Numerator"",),""Denominator"",),"" Test PASS?"",),""(After Int.WF )"",),""Deferred"",),""Interest"",),""Test Result"",),""Par"",),""(INTEREST)"",),""(PRINCIPAL)"",),""Needed"",),""Accrued"",),""(After Previous s)"",),CHAR(10)&""Due"",), ""(until Sub Notes IRR)"",)," _
         & """(until Equity IRR)"",),CHAR(10)&""Paid"",),""Until Target IRR"",),""("",),"")"",),""Int."",),""INTEREST"",),""PRINCIPAL"",),""INT."",),""&"",),""Distributions"",),""Outstanding"",),""NPS Amounts"",),""COVERAGE TESTS"",),""ProceedsAvailable"",),""Prin."",),""Int."",),""Coverage Tests"",),""COVERAGE TEST"",),""Coverage Test"",),""Det. Date"",),""CURES"",),""Nominal"",),CHAR(10),))"
        ThisWorkbook.Names.Add "Col.Header.EYID", RefersTo:="=TRIM(SUBSTITUTE(SUBSTITUTE(EVALUATE(ADDRESS(ModelStartRow-2,SUM(COLUMN(),PRODUCT(ISNUMBER(SEARCH(""Proceeds"",Col.Header)),0),PRODUCT(ISNUMBER(SEARCH(CHAR(10)&""Due"",Col.Header)),-1),PRODUCT(ISNUMBER(SEARCH(CHAR(10)&""Unpaid"",Col.Header)),-3),PRODUCT(ISNUMBER(SEARCH(CHAR(10)&""Paid"",Col.Header)),NOT(ISNUMBER(SEARCH(CHAR(10)&""UnPaid"",Col.Header))),-2)))),""Prin.WF ID:"",),""Int.WF ID:"",))"
       ThisWorkbook.Names.Add "WF.NPS", RefersTo:="=TRANSPOSE(NotePmtSeq)"
    ThisWorkbook.Names.Add "WF.NPS.Order", RefersTo:="=TRANSPOSE(NotePmtSeq.Order)"
    ThisWorkbook.Names.Add "WF.NPS.Type", RefersTo:="=TRANSPOSE(NotePmtSeq.Type)"
    ThisWorkbook.Names.Add "WF.NPS.Pmt.Number", RefersTo:="=TRANSPOSE(NotePmtSeq.Number)"
    ThisWorkbook.Names.Add "WF.NPS.Coverage.Num", RefersTo:="=TRANSPOSE(NotePmtSeq.CoverageNumber)"
    
    ThisWorkbook.Names.Add "WF.NPS.OrderMatrix", RefersTo:="=--(TRANSPOSE(WF.NPS.Order)<WF.NPS.Order)"
    ThisWorkbook.Names.Add "WF.NPS.ItemNumber", RefersTo:="=TRANSPOSE(ROW(NotePmtSeq)-ROW(NPSStart))"
        ThisWorkbook.Names.Add "WF.PmtSequence.RANGE", RefersToR1C1:="=" & Application.ConvertFormula(ThisWorkbook.Sheets("Int.WF").Range(NPSBalancesRange).Offset(StartRow).Address(0, 1, 1, 1), xlA1, xlR1C1, , ThisWorkbook.Sheets("Int.WF").Range(IntWFStart))
    ThisWorkbook.Names.Add "WF.PmtSequence.HEADER", RefersTo:="=" & NPSBalancesHEADER
    ThisWorkbook.Names.Add "WF.PI.Ratio.RANGE", RefersToR1C1:="=" & Application.ConvertFormula(ThisWorkbook.Sheets("Int.WF").Range(PIRatioRange).Offset(StartRow).Address(0, 1, 1, 1), xlA1, xlR1C1, , ThisWorkbook.Sheets("Int.WF").Range(IntWFStart))
    ThisWorkbook.Names.Add "WF.ProRata.RANGE", RefersToR1C1:="=" & Application.ConvertFormula(ThisWorkbook.Sheets("Int.WF").Range(NPSProRataRange).Offset(StartRow).Address(0, 1, 1, 1), xlA1, xlR1C1, , ThisWorkbook.Sheets("Int.WF").Range(IntWFStart)): Application.Calculate
    Last edited by cmore; 10-01-2015 at 07:52 AM.
    Reply With QuoteRegister To Reply
  3. 10-01-2015, 07:54 AM #3
    cmore
    cmore is offline
    Forum Contributor
    Join Date
    07-30-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    476

    Re: Calculation Efficiency

    More Formulas: Here is where things get somewhat weird.

        ThisWorkbook.Names.Add "WF.Pmts.UpToItem.WF.INT.HEADER", RefersToR1C1:="=INDEX(EVALUATE(ADDRESS(ModelStartRow-1,COLUMN(" & WorksheetFunction.Substitute(Application.ConvertFormula(ThisWorkbook.Sheets("Int.WF").Range(IntWFStart).Address(1, 1, 1, 1), xlA1, xlR1C1), "[" & ThisWorkbook.Name & "]", "") & "),1,1,WF.TabName.Int)&"":""&ADDRESS(ModelStartRow-1,MAX(COLUMN(EVALUATE(INDEX(DetailIntWFProceedsRange,MATCH(IF(ISNUMBER(SEARCH(""Coverage Test"",Col.Header)),Col.Header.Filtered&"" Coverage Test"",Col.Header.EYID),IntWFItemsRange,0))))-2,COLUMN(" & WorksheetFunction.Substitute(Application.ConvertFormula(ThisWorkbook.Sheets("Int.WF").Range(IntWFStart).Address(1, 1, 1, 1), xlA1, xlR1C1), "[" & ThisWorkbook.Name & "]", "") & ")))),)"
    ThisWorkbook.Names.Add "WF.Pmts.UpToItem.WF.INT", RefersToR1C1:="=INDEX(EVALUATE(ADDRESS(ROW(),COLUMN(" & WorksheetFunction.Substitute(Application.ConvertFormula(ThisWorkbook.Sheets("Int.WF").Range(IntWFStart).Address(1, 1, 1, 1), xlA1, xlR1C1), "[" & ThisWorkbook.Name & "]", "") & "),1,1,WF.TabName.Int)&"":""&ADDRESS(ROW(),MAX(COLUMN(EVALUATE(INDEX(DetailIntWFProceedsRange,MATCH(IF(ISNUMBER(SEARCH(""Coverage Test"",Col.Header)),Col.Header.Filtered&"" Coverage Test"",Col.Header.EYID),IntWFItemsRange,0))))-2,COLUMN(" & WorksheetFunction.Substitute(Application.ConvertFormula(ThisWorkbook.Sheets("Int.WF").Range(IntWFStart).Address(1, 1, 1, 1), xlA1, xlR1C1), "[" & ThisWorkbook.Name & "]", "") & ")))),)"
    
    ThisWorkbook.Names.Add "WF.Pmts.UpToItem.WF.PRIN.HEADER", RefersToR1C1:="=INDEX(EVALUATE(ADDRESS(ModelStartRow-1,COLUMN(" & WorksheetFunction.Substitute(Application.ConvertFormula(ThisWorkbook.Sheets("Prin.WF").Range(PrinWFStart).Address(1, 1, 1, 1), xlA1, xlR1C1), "[" & ThisWorkbook.Name & "]", "") & "),1,1,WF.TabName.Prin)&"":""&ADDRESS(ModelStartRow-1,MAX(COLUMN(EVALUATE(INDEX(DetailPrinWFProceedsRange,MATCH(IF(ISNUMBER(SEARCH(""Coverage Test"",Col.Header)),Col.Header.Filtered&"" Coverage Test"",Col.Header.EYID),PrinWFItemsRange,0))))-2,COLUMN(" & WorksheetFunction.Substitute(Application.ConvertFormula(ThisWorkbook.Sheets("Prin.WF").Range(PrinWFStart).Address(1, 1, 1, 1), xlA1, xlR1C1), "[" & ThisWorkbook.Name & "]", "") & ")))),)"
    ThisWorkbook.Names.Add "WF.Pmts.UpToItem.WF.PRIN", RefersToR1C1:="=INDEX(EVALUATE(ADDRESS(ROW(),COLUMN(" & WorksheetFunction.Substitute(Application.ConvertFormula(ThisWorkbook.Sheets("Prin.WF").Range(PrinWFStart).Address(1, 1, 1, 1), xlA1, xlR1C1), "[" & ThisWorkbook.Name & "]", "") & "),1,1,WF.TabName.Prin)&"":""&ADDRESS(ROW(),MAX(COLUMN(EVALUATE(INDEX(DetailPrinWFProceedsRange,MATCH(IF(ISNUMBER(SEARCH(""Coverage Test"",Col.Header)),Col.Header.Filtered&"" Coverage Test"",Col.Header.EYID),PrinWFItemsRange,0))))-2,COLUMN(" & WorksheetFunction.Substitute(Application.ConvertFormula(ThisWorkbook.Sheets("Prin.WF").Range(PrinWFStart).Address(1, 1, 1, 1), xlA1, xlR1C1), "[" & ThisWorkbook.Name & "]", "") & ")))),)"
                
    ThisWorkbook.Names.Add "WF.ProceedsAvailable.INT", RefersToR1C1:="=INDEX(EVALUATE(ADDRESS(ROW(),MAX(COLUMN(EVALUATE(INDEX(DetailIntWFProceedsRange,MATCH(IF(ISNUMBER(SEARCH(""Coverage Test"",Col.Header)),Col.Header.Filtered&"" Coverage Test"",Col.Header.EYID),IntWFItemsRange,0)))),COLUMN(" & WorksheetFunction.Substitute(Application.ConvertFormula(ThisWorkbook.Sheets("Int.WF").Range(IntWFStart).Address(1, 1, 1, 1), xlA1, xlR1C1), "[" & ThisWorkbook.Name & "]", "") & ")),1,1,WF.TabName.Int)),1)"
    ThisWorkbook.Names.Add "WF.ProceedsAvailable.PRIN", RefersToR1C1:="=INDEX(EVALUATE(ADDRESS(ROW(),MAX(COLUMN(EVALUATE(INDEX(DetailPrinWFProceedsRange,MATCH(IF(ISNUMBER(SEARCH(""Coverage Test"",Col.Header)),Col.Header.Filtered&"" Coverage Test"",Col.Header.EYID),PrinWFItemsRange,0)))),COLUMN(" & WorksheetFunction.Substitute(Application.ConvertFormula(ThisWorkbook.Sheets("Prin.WF").Range(PrinWFStart).Address(1, 1, 1, 1), xlA1, xlR1C1), "[" & ThisWorkbook.Name & "]", "") & ")),1,1,WF.TabName.Prin)),1)"
                
                
    
    'Pmt Sequence After Previous WF Pmts
    ThisWorkbook.Names.Add "WF.PmtSequence.Outstdng.WF.INT", RefersTo:="=WF.PmtSequence.Range-SUMIFS(WF.Pmts.UpToItem.WF.INT,WF.Pmts.UpToItem.WF.INT.HEADER,WF.NPS&CHAR(10)&""Paid"")"
    Last edited by cmore; 10-01-2015 at 08:02 AM.
    Reply With QuoteRegister To Reply
  4. 10-01-2015, 07:56 AM #4
    cmore
    cmore is offline
    Forum Contributor
    Join Date
    07-30-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    476

    Re: Calculation Efficiency

    Main Formulas: IC1 and then OC1

    
    'INTEREST WF Cures
    'I/C
    ThisWorkbook.Names.Add "WF.PmtSequence.IC.Cure.NOMINAL.Pmts.WF.INT", RefersTo:="=--(MMULT(IFERROR(WF.PmtSequence.Outstdng.WF.INT/WF.PI.Ratio.RANGE,0),WF.NPS.OrderMatrix)<=WF.IC.Cure.NOMINAL)*WF.CoverageClasses.NPS*(WF.IC.Cure.NOMINAL-(MMULT(IFERROR(WF.PmtSequence.Outstdng.WF.INT/WF.PI.Ratio.RANGE,0),WF.NPS.OrderMatrix)))*--(WF.ProRata.RANGE>0)*WF.ProRata.RANGE*WF.PI.Ratio.RANGE"
    ThisWorkbook.Names.Add "WF.PmtSequence.IC.Cure.Bal.WF.INT", RefersTo:="=--(MMULT(IFERROR(WF.PmtSequence.Outstdng.WF.INT/WF.PI.Ratio.RANGE,0),WF.NPS.OrderMatrix)<=WF.IC.Cure.NOMINAL)*WF.CoverageClasses.NPS*WF.PmtSequence.Outstdng.WF.INT"
    ThisWorkbook.Names.Add "WF.PmtSequence.IC.Cure.WF.INT", RefersTo:="=IF(WF.PmtSequence.IC.Cure.Bal.WF.INT<WF.PmtSequence.IC.Cure.NOMINAL.Pmts.WF.INT,WF.PmtSequence.IC.Cure.Bal.WF.INT,WF.PmtSequence.IC.Cure.NOMINAL.Pmts.WF.INT)"
    ThisWorkbook.Names.Add "WF.IC.Cure.NPS.WF.INT", RefersTo:="=--(MMULT(WF.PmtSequence.IC.Cure.WF.INT, WF.NPS.OrderMatrix)<=MIN(SUM(WF.PmtSequence.IC.Cure.WF.INT),WF.ProceedsAvailable.INT))*--(WF.PmtSequence.IC.Cure.WF.INT>0)*(MIN(SUM(WF.PmtSequence.IC.Cure.WF.INT),WF.ProceedsAvailable.INT)-MMULT(WF.PmtSequence.IC.Cure.WF.INT,WF.NPS.OrderMatrix))*WF.ProRata.RANGE"
    ThisWorkbook.Names.Add "WF.IC.Cure.WF.INT", RefersTo:="=SUMPRODUCT(WF.IC.Cure.NPS.WF.INT)"
    ThisWorkbook.Names.Add "WF.IC.Cure.Pmts.IntDollars.WF.INT", RefersTo:="=SUMPRODUCT(WF.IC.Cure.NPS.WF.INT/WF.PI.Ratio.RANGE)"
    
    'O/C
    ThisWorkbook.Names.Add "WF.PmtSequenceOutstanding.UpTotOC.WF.INT", RefersTo:="=(WF.PmtSequence.Outstdng.WF.INT - WF.IC.Cure.NPS.WF.INT)"
    ThisWorkbook.Names.Add "WF.OCDenominator.WF.INT.UpToCov.NPS", RefersTo:="=(WF.PmtSequenceOutstanding.UpTotOC.WF.INT*--((WF.NPS.Type=""DEFERRED"")+(WF.NPS.Type=""PAR""))*WF.CoverageClasses.DENOM)"
    ThisWorkbook.Names.Add "WF.OCDenominator.WF.INT.UpToCov", RefersTo:="=SUM(WF.OCDenominator.WF.INT.UpToCov.NPS)"
    ThisWorkbook.Names.Add "WF.OC.Cure.NOMINAL.WF.INT", RefersTo:="=MIN(PRODUCT(SUM(WF.OCDenominator.WF.INT.UpToCov,-WF.OCNumerator/MAX(0.0000001%,WF.OCRatio)),N(NOT(OR(WF.OCNumerator/MAX(1, WF.OCDenominator.WF.INT.UpToCov)>=WF.OCRatio,WF.OCDenominator.WF.INT.UpToCov=0)))),SUM(WF.PmtSequenceOutstanding.UpTotOC.WF.INT))"
    ThisWorkbook.Names.Add "WF.PmtSequence.OC.Cure.NOMINAL.Pmts.WF.INT", RefersTo:="=--(MMULT(WF.PmtSequenceOutstanding.UpTotOC.WF.INT, WF.NPS.OrderMatrix) <= MIN(SUM(WF.ProceedsAvailable.INT, -WF.IC.Cure.WF.INT), WF.OC.Cure.NOMINAL.WF.INT)) * --(WF.PmtSequenceOutstanding.UpTotOC.WF.INT > 0) * (MIN(SUM(WF.ProceedsAvailable.INT, - WF.IC.Cure.WF.INT), WF.OC.Cure.NOMINAL.WF.INT) - MMULT(WF.PmtSequenceOutstanding.UpTotOC.WF.INT, WF.NPS.OrderMatrix)) * WF.ProRata.RANGE"
    
    ThisWorkbook.Names.Add "WF.OC.Cure.NPS.WF.INT", RefersTo:="=IF(WF.PmtSequenceOutstanding.UpTotOC.WF.INT<WF.PmtSequence.OC.Cure.NOMINAL.Pmts.WF.INT, WF.PmtSequenceOutstanding.UpTotOC.WF.INT, WF.PmtSequence.OC.Cure.NOMINAL.Pmts.WF.INT)"
    ThisWorkbook.Names.Add "WF.OC.Cure.WF.INT", RefersTo:="=SUMPRODUCT(WF.OC.Cure.NPS.WF.INT)"
    Main Formulas: IC2 and OC2

        ThisWorkbook.Names.Add "WF.PmtSequence.Outstdng.WF.PRIN", RefersTo:=WorksheetFunction.Substitute("=WF.PmtSequence.RANGE-SUMIFS(WF.Pmts.UpToItem.WF.PRIN,WF.Pmts.UpToItem.WF.PRIN.HEADER,WF.NPS&CHAR(10)&""Paid"")-SUMIFS(" & Application.ConvertFormula(InterestWaterfallRANGE, xlA1, xlR1C1, , ThisWorkbook.Sheets("Int.WF").Range(IntWFStart)) & "," & Application.ConvertFormula(InterestWaterfallHEADER, xlA1, xlR1C1, , ThisWorkbook.Sheets("Int.WF").Range(IntWFStart)) & ", WF.NPS&CHAR(10)&""Paid"")", "[" & ThisWorkbook.Name & "]", "")

    'PRINCIPAL WF Cures
    'I/C
    ThisWorkbook.Names.Add "WF.PmtSequence.IC.Cure.NOMINAL.Pmts.WF.PRIN", RefersTo:="=0" '--(MMULT(IFERROR(WF.PmtSequence.Outstdng.WF.PRIN/WF.PI.Ratio.RANGE,0),WF.NPS.OrderMatrix)<=SUM(WF.IC.Cure.NOMINAL,-WF.IC.Cure.Pmts.IntDollars.WF.INT))*WF.CoverageClasses.NPS*(SUM(WF.IC.Cure.NOMINAL,-WF.IC.Cure.Pmts.IntDollars.WF.INT)-(MMULT(IFERROR(WF.PmtSequence.Outstdng.WF.PRIN/WF.PI.Ratio.RANGE,0),WF.NPS.OrderMatrix)))*--(WF.ProRata.RANGE>0)*WF.ProRata.RANGE*WF.PI.Ratio.RANGE"
    ThisWorkbook.Names.Add "WF.PmtSequence.IC.Cure.Bal.WF.PRIN", RefersTo:="=0" '--(MMULT(IFERROR(WF.PmtSequence.Outstdng.WF.PRIN/WF.PI.Ratio.RANGE,0),WF.NPS.OrderMatrix)<=SUM(WF.IC.Cure.NOMINAL,-WF.IC.Cure.Pmts.IntDollars.WF.INT))*WF.CoverageClasses.NPS*WF.PmtSequence.Outstdng.WF.PRIN"
    ThisWorkbook.Names.Add "WF.PmtSequence.IC.Cure.WF.PRIN", RefersTo:="=0" 'IF(WF.PmtSequence.IC.Cure.Bal.WF.PRIN<WF.PmtSequence.IC.Cure.NOMINAL.Pmts.WF.PRIN,WF.PmtSequence.IC.Cure.Bal.WF.PRIN,WF.PmtSequence.IC.Cure.NOMINAL.Pmts.WF.PRIN)"
    ThisWorkbook.Names.Add "WF.IC.Cure.NPS.WF.PRIN", RefersTo:="=0" '--(MMULT(WF.PmtSequence.IC.Cure.WF.PRIN, WF.NPS.OrderMatrix)<=MIN(SUM(WF.PmtSequence.IC.Cure.WF.PRIN),WF.ProceedsAvailable.PRIN))*--(WF.PmtSequence.IC.Cure.WF.PRIN>0)*(MIN(SUM(WF.PmtSequence.IC.Cure.WF.PRIN),WF.ProceedsAvailable.PRIN)-MMULT(WF.PmtSequence.IC.Cure.WF.PRIN,WF.NPS.OrderMatrix))*WF.ProRata.RANGE"
    ThisWorkbook.Names.Add "WF.IC.Cure.WF.PRIN", RefersTo:="=SUM(WF.IC.Cure.NPS.WF.PRIN)"
    
    'O/C
    ThisWorkbook.Names.Add "WF.PmtSequenceOutstanding.UpTotOC.WF.PRIN", RefersTo:="=(WF.PmtSequence.Outstdng.WF.PRIN - WF.IC.Cure.NPS.WF.PRIN)"
    ThisWorkbook.Names.Add "WF.OCDenominator.WF.PRIN.UpToCov.NPS", RefersTo:="=(WF.PmtSequenceOutstanding.UpTotOC.WF.PRIN*--((WF.NPS.Type=""DEFERRED"")+(WF.NPS.Type=""PAR""))*WF.CoverageClasses.DENOM)"
    ThisWorkbook.Names.Add "WF.OCDenominator.WF.PRIN.UpToCov", RefersTo:="=SUM(WF.OCDenominator.WF.PRIN.UpToCov.NPS)"
    ThisWorkbook.Names.Add "WF.OCNumerator.WF.PRIN.UpToCov", RefersTo:="=SUM(WF.OCNumerator, -WF.IC.Cure.WF.PRIN)"
    ThisWorkbook.Names.Add "WF.OC.Cure.NOMINAL.WF.PRIN", RefersTo:="=MIN(PRODUCT((WF.OCDenominator.WF.PRIN.UpToCov*WF.OCRatio-WF.OCNumerator.WF.PRIN.UpToCov)/MAX(0.000001%,(WF.OCRatio-1)), N(NOT(OR(WF.OCNumerator.WF.PRIN.UpToCov/MAX(1, WF.OCDenominator.WF.PRIN.UpToCov)>=WF.OCRatio,WF.OCDenominator.WF.PRIN.UpToCov=0)))), SUM(WF.OCDenominator.WF.PRIN.UpToCov.NPS))"
    ThisWorkbook.Names.Add "WF.PmtSequence.OC.Cure.NOMINAL.Pmts.WF.PRIN", RefersTo:="=--(MMULT(WF.PmtSequenceOutstanding.UpTotOC.WF.PRIN, WF.NPS.OrderMatrix) <= MIN(SUM(WF.ProceedsAvailable.PRIN, -WF.IC.Cure.WF.PRIN), WF.OC.Cure.NOMINAL.WF.PRIN)) * --(WF.PmtSequenceOutstanding.UpTotOC.WF.PRIN > 0) * (MIN(SUM(WF.ProceedsAvailable.PRIN, - WF.IC.Cure.WF.PRIN), WF.OC.Cure.NOMINAL.WF.PRIN) - MMULT(WF.PmtSequenceOutstanding.UpTotOC.WF.PRIN, WF.NPS.OrderMatrix)) * WF.ProRata.RANGE"
    
    ThisWorkbook.Names.Add "WF.OC.Cure.NPS.WF.PRIN", RefersTo:="=IF(WF.PmtSequenceOutstanding.UpTotOC.WF.PRIN<WF.PmtSequence.OC.Cure.NOMINAL.Pmts.WF.PRIN, WF.PmtSequenceOutstanding.UpTotOC.WF.PRIN, WF.PmtSequence.OC.Cure.NOMINAL.Pmts.WF.PRIN)"
    ThisWorkbook.Names.Add "WF.OC.Cure.WF.PRIN", RefersTo:="=SUM(WF.OC.Cure.NPS.WF.PRIN)"
    Last edited by cmore; 10-01-2015 at 07:59 AM.
    Reply With QuoteRegister To Reply
  5. 10-01-2015, 08:00 AM #5
    cmore
    cmore is offline
    Forum Contributor
    Join Date
    07-30-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    476

    Re: Calculation Efficiency

    Formulas put into sheets:

    'COVERAGE TESTS
Dim TmpCovFormulas()
c = 0
ReDim TmpCovFormulas(CoverageColumns - 1)
vbitem = ThisWorkbook.Sheets("Int.WF").Range(CoverageDenominatorReference).Address(0, 1)
    For i = 1 To CoverageTestCount
        If DEALINFO.[CoverageTestsICCount] >= i Then
            TmpCovFormulas(c) = "=PRODUCT(PRODUCT(1/MAX(1,WF.ICDenominator),WF.ICNumerator),--(WF.ICDenominator>0))": c = c + 1
            TmpCovFormulas(c) = "=AND(OR($A" & StartRow + 1 & "<ICStartPeriod," & vbitem & ">=Col.Header)," & vbitem & ")": c = c + 1
            vbitem = ThisWorkbook.Sheets("Int.WF").Range(CoverageDenominatorReference).Offset(0, c).Address(0, 1)
        End If
        
        If DEALINFO.[CoverageTestsOCCount] >= i Then
            TmpCovFormulas(c) = "=PRODUCT(1/Max(1,WF.OCDenominator),WF.OCNumerator)": c = c + 1
            TmpCovFormulas(c) = "=" & vbitem & ">=Col.Header": c = c + 1
        End If
        
        If DEALINFO.[CoverageTestsICCount] >= i Then TmpCovFormulas(c) = "=WF.IC.Cure.NOMINAL": c = c + 1
        If DEALINFO.[CoverageTestsOCCount] >= i Then TmpCovFormulas(c) = "=MAX(WF.OC.Cure.NOMINAL.WF.INT,WF.OC.Cure.NOMINAL.WF.PRIN)": c = c + 1
        TmpCovFormulas(c) = "=SUMPRODUCT(WF.IC.Cure.NPS.WF.INT+WF.OC.Cure.NPS.WF.INT)": c = c + 1
        TmpCovFormulas(c) = "=SUMPRODUCT(WF.IC.Cure.NPS.WF.PRIN+WF.OC.Cure.NPS.WF.PRIN)": c = c + 1
        vbitem = ThisWorkbook.Sheets("Int.WF").Range(CoverageDenominatorReference).Offset(0, c).Address(0, 1)
    Next
    
    ThisWorkbook.Sheets("Int.WF").Range(CoverageRange) = TmpCovFormulas:     ThisWorkbook.Sheets("Int.WF").Range(CoverageRange).AutoFill Destination:=ThisWorkbook.Sheets("Int.WF").Range(CoverageRange).Resize(TotalPds - 1), Type:=xlFillDefault
    Reply With QuoteRegister To Reply
  6. 10-01-2015, 10:12 AM #6
    cmore
    cmore is offline
    Forum Contributor
    Join Date
    07-30-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    476

    Re: Calculation Efficiency

    So even worse,

    I've zeroed formulas out for IC2, as soon as I run those formulas and attempt to paste the formulas in everything goes ape to the point I cannot scroll over in non-excel applications. It's weird to me because there's still only about 2MB - 3MB of RAM that seems to be allocated. I'm at a loss
    Reply With QuoteRegister To Reply
  7. 10-01-2015, 02:19 PM #7
    cmore
    cmore is offline
    Forum Contributor
    Join Date
    07-30-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    476

    Re: Calculation Efficiency

    So now I'm realizing is that there's some hidden volatility somewhere that I'm unaware of. There's something wrong with these arrays I'm assuming but if it was just having too many array functions this would jack up the file size right?
    Reply With QuoteRegister To Reply
  8. 10-02-2015, 07:53 AM #8
    cmore
    cmore is offline
    Forum Contributor
    Join Date
    07-30-2013
    Location
    United States
    MS-Off Ver
    Excel 2007
    Posts
    476

    Re: Calculation Efficiency

    Please see attached if interested. Currently the workbook is populating data just at the pace of snail riding on a tortoise's back
    Last edited by cmore; 10-06-2015 at 12:10 AM.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Efficiency of calculation as a function of number of worksheets
    By GIS2013 in forum Excel General
    Replies: 8
    Last Post: 10-18-2015, 05:06 PM
  2. Optimization and Efficiency
    By zhensley in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-26-2014, 10:29 AM
  3. Search efficiency
    By Jordan Kidd in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-14-2010, 05:25 PM
  4. Efficiency
    By gtmeloney in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-06-2009, 11:10 AM
  5. Efficiency… Efficiency… Efficiency…
    By rakeshplb in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-30-2009, 08:37 AM
  6. For loop efficiency
    By excel09 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-23-2009, 02:25 PM
  7. Efficiency using IF and OR
    By davegugg in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 01-30-2009, 08:27 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1