Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with speeding this up...
this is long, and I apologise if my meaning isn't immediately clear
I have a template used by our group to do bulk calculations - this is an ongoing project/learning tool for me and so far it's been quite successful (on both counts), but... Previous incarnations used a series of (nested) IF functions (up to about 10 separate calculations per row), but over time these were becoming increasingly complex and unwieldy. Another disadvantage was that the resulting filesize was considerably larger (40-60M workbooks are common). In an attempt to reduce a) bloat, b) complexity, c) duplication I have converted most of the formula's to VBA functions - while this has effectively reduced the number of formula's used (instead of using the same formula with different variables each cell in each row, each cell now calls the vba function and passes the relevant var) it has introduced a substantial deficit in speed (sheets routinely have 50K+ records and recalculation now takes 5-10 minutes) what I'm looking for now (finally, I hear you say) is some way to speed things up - this is a tool that is used daily by about 20 users and that sort of response time is way too long (if this is not possible I'll be forced to go back to doing things to 'old' way...) any/all ideas gratefully received many thanks (in advance) S -- Instead of building bigger and better weapons of mass destruction, we should be trying to get better use out of the ones we've already got.... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with speeding this up...
Hi,
Really don't know exactly what you are doing, but another constuct often used where an If is used is the Case. I don't knw if this will help or not? Thanks, "Simon" wrote: this is long, and I apologise if my meaning isn't immediately clear I have a template used by our group to do bulk calculations - this is an ongoing project/learning tool for me and so far it's been quite successful (on both counts), but... Previous incarnations used a series of (nested) IF functions (up to about 10 separate calculations per row), but over time these were becoming increasingly complex and unwieldy. Another disadvantage was that the resulting filesize was considerably larger (40-60M workbooks are common). In an attempt to reduce a) bloat, b) complexity, c) duplication I have converted most of the formula's to VBA functions - while this has effectively reduced the number of formula's used (instead of using the same formula with different variables each cell in each row, each cell now calls the vba function and passes the relevant var) it has introduced a substantial deficit in speed (sheets routinely have 50K+ records and recalculation now takes 5-10 minutes) what I'm looking for now (finally, I hear you say) is some way to speed things up - this is a tool that is used daily by about 20 users and that sort of response time is way too long (if this is not possible I'll be forced to go back to doing things to 'old' way...) any/all ideas gratefully received many thanks (in advance) S -- Instead of building bigger and better weapons of mass destruction, we should be trying to get better use out of the ones we've already got.... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with speeding this up...
Simon,
Without seeing exactly what kind of calculations you're performing and how your code is structured it's going to be difficult to offer any suggestions. Tim "Simon" wrote in message ... this is long, and I apologise if my meaning isn't immediately clear I have a template used by our group to do bulk calculations - this is an ongoing project/learning tool for me and so far it's been quite successful (on both counts), but... Previous incarnations used a series of (nested) IF functions (up to about 10 separate calculations per row), but over time these were becoming increasingly complex and unwieldy. Another disadvantage was that the resulting filesize was considerably larger (40-60M workbooks are common). In an attempt to reduce a) bloat, b) complexity, c) duplication I have converted most of the formula's to VBA functions - while this has effectively reduced the number of formula's used (instead of using the same formula with different variables each cell in each row, each cell now calls the vba function and passes the relevant var) it has introduced a substantial deficit in speed (sheets routinely have 50K+ records and recalculation now takes 5-10 minutes) what I'm looking for now (finally, I hear you say) is some way to speed things up - this is a tool that is used daily by about 20 users and that sort of response time is way too long (if this is not possible I'll be forced to go back to doing things to 'old' way...) any/all ideas gratefully received many thanks (in advance) S -- Instead of building bigger and better weapons of mass destruction, we should be trying to get better use out of the ones we've already got.... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with speeding this up...
In our last episode Tim Williams wrote:
Simon, Without seeing exactly what kind of calculations you're performing and how your code is structured it's going to be difficult to offer any suggestions. Tim Tim apologies, it's always difficult to know just how much info to provide... hope this is sufficient <quote 'old' formula looked like this (mind the wrap)... =IF(OR($A4="",$L$1=0),"",IF(OR(LEFT(F4,6)="XXXXXX" ,E4="YYYY"),"",IF(((($L$1/6 0)*$K4)*1.1)<($L$2*1.1),($L$2*1.1),(($L$1/60)*$K4)*1.1))) new formula looks like this... =IF(OR(A7="",Rate=0),"",Cost($L7,Rate,Min,Flag,Cap Per,CapVal)) which calls this function ... Function cost(dur, rate, min, Flag, CapPer, CapVal) If CapPer = 0 And ((rate / 60) * dur) <= min Then cost = min * 1.1 ElseIf CapPer = 0 And ((rate / 60) * dur) min Then cost = ((rate / 60) * dur) * 1.1 ElseIf CapPer 0 And dur < CapPer And ((rate / 60) * dur) <= min Then cost = min * 1.1 ElseIf CapPer 0 And dur < CapPer And ((rate / 60) * dur) min And ((rate / 60) * dur) <= CapVal Then cost = ((rate / 60) * dur) * 1.1 ElseIf CapPer 0 And dur < CapPer And ((rate / 60) * dur) CapVal Then cost = CapVal * 1.1 ElseIf CapPer 0 And dur CapPer Then cost = (((rate / 60) * (dur - CapPer)) + CapVal) * 1.1 Else cost = 0 End If End Function </quote the given example is in a sheet where this is calculated once per row, but is indicative of the type of calculations we're doing (another sheet calls this function twice per row, and a third calls a similar function 10 times per row) many thanks S -- Instead of trying to build bigger and better weapons of mass destruction, shouldn't we be trying to get better use out of the ones we've already got.... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with speeding this up...
I think we will only be able to offer generalisations, as the real thing is
too big and too complex to discuss here, but a couple of ways is to turn screenupdating and automatic calculation off Application.ScreenUpdating = False Application.Calculation= xlCalculationManual and reset at the end Application.Calculation= xlCalculationAutomatic Application.ScreenUpdating = True Other than that, it is a case of identifying the bottlenecks and looking at re-designing. You may be best to employ a professional to help you. RP (remove nothere from the email address if mailing direct) "Simon" wrote in message ... In our last episode Tim Williams wrote: Simon, Without seeing exactly what kind of calculations you're performing and how your code is structured it's going to be difficult to offer any suggestions. Tim Tim apologies, it's always difficult to know just how much info to provide... hope this is sufficient <quote 'old' formula looked like this (mind the wrap)... =IF(OR($A4="",$L$1=0),"",IF(OR(LEFT(F4,6)="XXXXXX" ,E4="YYYY"),"",IF(((($L$1/ 6 0)*$K4)*1.1)<($L$2*1.1),($L$2*1.1),(($L$1/60)*$K4)*1.1))) new formula looks like this... =IF(OR(A7="",Rate=0),"",Cost($L7,Rate,Min,Flag,Cap Per,CapVal)) which calls this function ... Function cost(dur, rate, min, Flag, CapPer, CapVal) If CapPer = 0 And ((rate / 60) * dur) <= min Then cost = min * 1.1 ElseIf CapPer = 0 And ((rate / 60) * dur) min Then cost = ((rate / 60) * dur) * 1.1 ElseIf CapPer 0 And dur < CapPer And ((rate / 60) * dur) <= min Then cost = min * 1.1 ElseIf CapPer 0 And dur < CapPer And ((rate / 60) * dur) min And ((rate / 60) * dur) <= CapVal Then cost = ((rate / 60) * dur) * 1.1 ElseIf CapPer 0 And dur < CapPer And ((rate / 60) * dur) CapVal Then cost = CapVal * 1.1 ElseIf CapPer 0 And dur CapPer Then cost = (((rate / 60) * (dur - CapPer)) + CapVal) * 1.1 Else cost = 0 End If End Function </quote the given example is in a sheet where this is calculated once per row, but is indicative of the type of calculations we're doing (another sheet calls this function twice per row, and a third calls a similar function 10 times per row) many thanks S -- Instead of trying to build bigger and better weapons of mass destruction, shouldn't we be trying to get better use out of the ones we've already got.... |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with speeding this up...
40 Megs per workbook is an awful lot and CAN/MUST be streamlined. does it need to be recalculating formulas? or is it just to calculate invoice lines, which when calculated can be converted to Values? How many different functions have you defined? What does your workbook look like # of sheets # of rows Generally speaking using VBA functions will NOT speed things up, although with complex calculations it make things easier to read.. you could also use Named formulas or Array formulas to bring down calculation times and size. I'm fairly certain than many of us could bring this baby back to size and speed (< 1 minute) ... though most will not do it for free, as the formulas need to be analysed and rewritten, which takes time. A very simple tip might help your functions sepped up tremendously TYPE your arguments as Long or DOUBLE iso as variant. your function becomes 5 times faster when defined as: Function cost(dur#, rate#, min#, Flag&, CapPer#, CapVal#) As Double Just rewriting the VBA functions might help. Following is far more efficient (10 to 15) than yours: please be sure to check the if's and ands.. ADDING capval in the last statement brings same results as yours, but it doesnt make business-sense) Function costX(dur#, rate#, min#, Flag, CapPer#, CapVal#) As Double Dim dPrice# dPrice = ((rate / 60) * dur) If CapPer <= 0 Then If dPrice <= min Then costX = min * 1.1 Else costX = dPrice * 1.1 End If Else If dur <= CapPer Then If dPrice <= min Then costX = min * 1.1 ElseIf dPrice <= CapVal Then costX = CapVal * 1.1 Else costX = dPrice * 1.1 End If Else costX = (dPrice - (rate / 60 * CapPer) + CapVal) * 1.1 End If End If End Function -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Simon wrote : In our last episode Tim Williams wrote: Simon, Without seeing exactly what kind of calculations you're performing and how your code is structured it's going to be difficult to offer any suggestions. Tim Tim apologies, it's always difficult to know just how much info to provide... hope this is sufficient <quote 'old' formula looked like this (mind the wrap)... =IF(OR($A4="",$L$1=0),"",IF(OR(LEFT(F4,6)="XXXXXX" ,E4="YYYY"),"",IF((( ($L$1/6 0)*$K4)*1.1)<($L$2*1.1),($L$2*1.1),(($L$1/60)*$K4)*1.1))) new formula looks like this... =IF(OR(A7="",Rate=0),"",Cost($L7,Rate,Min,Flag,Cap Per,CapVal)) which calls this function ... Function cost(dur, rate, min, Flag, CapPer, CapVal) If CapPer = 0 And ((rate / 60) * dur) <= min Then cost = min * 1.1 ElseIf CapPer = 0 And ((rate / 60) * dur) min Then cost = ((rate / 60) * dur) * 1.1 ElseIf CapPer 0 And dur < CapPer And ((rate / 60) * dur) <= min Then cost = min * 1.1 ElseIf CapPer 0 And dur < CapPer And ((rate / 60) * dur) min And ((rate / 60) * dur) <= CapVal Then cost = ((rate / 60) * dur) * 1.1 ElseIf CapPer 0 And dur < CapPer And ((rate / 60) * dur) CapVal Then cost = CapVal * 1.1 ElseIf CapPer 0 And dur CapPer Then cost = (((rate / 60) * (dur - CapPer)) + CapVal) * 1.1 Else cost = 0 End If End Function </quote the given example is in a sheet where this is calculated once per row, but is indicative of the type of calculations we're doing (another sheet calls this function twice per row, and a third calls a similar function 10 times per row) many thanks S |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with speeding this up...
Simon,
To speed up and slim down your formulae you need to remove all the duplicate stuff and put it in a single place somewhere else: for example $L$1/60*1.1 "XXXXXX" and "YYYY" If you have a lot (several 1000) of occurrences of your function you can speed up calculation a lot if you make sure that all the windows in the VBE are closed, that the VBE itself is closed, and trap F9 so that it does the calculation with an Application.Calculate (assuming you are in manual mode). You can probably also speed up the execution of the function itself by using VB6, but it does not look very slow: I think its just the overhead of calling a VBA function which is slowing you down. For ultimate speed rewrite the function in C. see http://www.decisionModels.com/calcsecretsj.htm and the rest of the site for further ideas. regards Charles ______________________ Decision Models FastExcel 2.1 now available www.DecisionModels.com "Simon" wrote in message ... In our last episode Tim Williams wrote: Simon, Without seeing exactly what kind of calculations you're performing and how your code is structured it's going to be difficult to offer any suggestions. Tim Tim apologies, it's always difficult to know just how much info to provide... hope this is sufficient <quote 'old' formula looked like this (mind the wrap)... =IF(OR($A4="",$L$1=0),"",IF(OR(LEFT(F4,6)="XXXXXX" ,E4="YYYY"),"",IF(((($L$1/6 0)*$K4)*1.1)<($L$2*1.1),($L$2*1.1),(($L$1/60)*$K4)*1.1))) new formula looks like this... =IF(OR(A7="",Rate=0),"",Cost($L7,Rate,Min,Flag,Cap Per,CapVal)) which calls this function ... Function cost(dur, rate, min, Flag, CapPer, CapVal) If CapPer = 0 And ((rate / 60) * dur) <= min Then cost = min * 1.1 ElseIf CapPer = 0 And ((rate / 60) * dur) min Then cost = ((rate / 60) * dur) * 1.1 ElseIf CapPer 0 And dur < CapPer And ((rate / 60) * dur) <= min Then cost = min * 1.1 ElseIf CapPer 0 And dur < CapPer And ((rate / 60) * dur) min And ((rate / 60) * dur) <= CapVal Then cost = ((rate / 60) * dur) * 1.1 ElseIf CapPer 0 And dur < CapPer And ((rate / 60) * dur) CapVal Then cost = CapVal * 1.1 ElseIf CapPer 0 And dur CapPer Then cost = (((rate / 60) * (dur - CapPer)) + CapVal) * 1.1 Else cost = 0 End If End Function </quote the given example is in a sheet where this is calculated once per row, but is indicative of the type of calculations we're doing (another sheet calls this function twice per row, and a third calls a similar function 10 times per row) many thanks S -- Instead of trying to build bigger and better weapons of mass destruction, shouldn't we be trying to get better use out of the ones we've already got.... |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with speeding this up...
Can you post your testing/timing code, as I (surprisingly) don't get speed
improvements of anything like 10-15 times, in fact it is repeatedly slower? Thanks Bob "keepITcool" wrote in message ft.com... 40 Megs per workbook is an awful lot and CAN/MUST be streamlined. does it need to be recalculating formulas? or is it just to calculate invoice lines, which when calculated can be converted to Values? How many different functions have you defined? What does your workbook look like # of sheets # of rows Generally speaking using VBA functions will NOT speed things up, although with complex calculations it make things easier to read.. you could also use Named formulas or Array formulas to bring down calculation times and size. I'm fairly certain than many of us could bring this baby back to size and speed (< 1 minute) ... though most will not do it for free, as the formulas need to be analysed and rewritten, which takes time. A very simple tip might help your functions sepped up tremendously TYPE your arguments as Long or DOUBLE iso as variant. your function becomes 5 times faster when defined as: Function cost(dur#, rate#, min#, Flag&, CapPer#, CapVal#) As Double Just rewriting the VBA functions might help. Following is far more efficient (10 to 15) than yours: please be sure to check the if's and ands.. ADDING capval in the last statement brings same results as yours, but it doesnt make business-sense) Function costX(dur#, rate#, min#, Flag, CapPer#, CapVal#) As Double Dim dPrice# dPrice = ((rate / 60) * dur) If CapPer <= 0 Then If dPrice <= min Then costX = min * 1.1 Else costX = dPrice * 1.1 End If Else If dur <= CapPer Then If dPrice <= min Then costX = min * 1.1 ElseIf dPrice <= CapVal Then costX = CapVal * 1.1 Else costX = dPrice * 1.1 End If Else costX = (dPrice - (rate / 60 * CapPer) + CapVal) * 1.1 End If End If End Function -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Simon wrote : In our last episode Tim Williams wrote: Simon, Without seeing exactly what kind of calculations you're performing and how your code is structured it's going to be difficult to offer any suggestions. Tim Tim apologies, it's always difficult to know just how much info to provide... hope this is sufficient <quote 'old' formula looked like this (mind the wrap)... =IF(OR($A4="",$L$1=0),"",IF(OR(LEFT(F4,6)="XXXXXX" ,E4="YYYY"),"",IF((( ($L$1/6 0)*$K4)*1.1)<($L$2*1.1),($L$2*1.1),(($L$1/60)*$K4)*1.1))) new formula looks like this... =IF(OR(A7="",Rate=0),"",Cost($L7,Rate,Min,Flag,Cap Per,CapVal)) which calls this function ... Function cost(dur, rate, min, Flag, CapPer, CapVal) If CapPer = 0 And ((rate / 60) * dur) <= min Then cost = min * 1.1 ElseIf CapPer = 0 And ((rate / 60) * dur) min Then cost = ((rate / 60) * dur) * 1.1 ElseIf CapPer 0 And dur < CapPer And ((rate / 60) * dur) <= min Then cost = min * 1.1 ElseIf CapPer 0 And dur < CapPer And ((rate / 60) * dur) min And ((rate / 60) * dur) <= CapVal Then cost = ((rate / 60) * dur) * 1.1 ElseIf CapPer 0 And dur < CapPer And ((rate / 60) * dur) CapVal Then cost = CapVal * 1.1 ElseIf CapPer 0 And dur CapPer Then cost = (((rate / 60) * (dur - CapPer)) + CapVal) * 1.1 Else cost = 0 End If End Function </quote the given example is in a sheet where this is calculated once per row, but is indicative of the type of calculations we're doing (another sheet calls this function twice per row, and a third calls a similar function 10 times per row) many thanks S |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with speeding this up...
Function cost(dur, rate, min, Flag, CapPer, CapVal)
If CapPer = 0 And ((rate / 60) * dur) <= min Then cost = min * 1.1 ElseIf CapPer = 0 And ((rate / 60) * dur) min Then cost = ((rate / 60) * dur) * 1.1 ElseIf CapPer 0 And dur < CapPer And ((rate / 60) * dur) <= min Then cost = min * 1.1 ElseIf CapPer 0 And dur < CapPer And ((rate / 60) * dur) min And ((rate / 60) * dur) <= CapVal Then cost = ((rate / 60) * dur) * 1.1 ElseIf CapPer 0 And dur < CapPer And ((rate / 60) * dur) CapVal Then cost = CapVal * 1.1 ElseIf CapPer 0 And dur CapPer Then cost = (((rate / 60) * (dur - CapPer)) + CapVal) * 1.1 Else cost = 0 End If End Function Function costTyped(dur#, rate#, min#, Flag&, CapPer#, CapVal#) As Double If CapPer = 0 And ((rate / 60) * dur) <= min Then costTyped = min * 1.1 ElseIf CapPer = 0 And ((rate / 60) * dur) min Then costTyped = ((rate / 60) * dur) * 1.1 ElseIf CapPer 0 And dur < CapPer And ((rate / 60) * dur) <= min Then costTyped = min * 1.1 ElseIf CapPer 0 And dur < CapPer And ((rate / 60) * dur) min And ((rate / 60) * dur) <= CapVal Then costTyped = ((rate / 60) * dur) * 1.1 ElseIf CapPer 0 And dur < CapPer And ((rate / 60) * dur) CapVal Then costTyped = CapVal * 1.1 ElseIf CapPer 0 And dur CapPer Then costTyped = (((rate / 60) * (dur - CapPer)) + CapVal) * 1.1 Else costTyped = 0 End If End Function Function costX(dur#, rate#, min#, Flag, CapPer#, CapVal#) As Double Dim dPrice# dPrice = ((rate / 60) * dur) If CapPer <= 0 Then If dPrice <= min Then costX = min * 1.1 Else costX = dPrice * 1.1 End If Else If dur <= CapPer Then If dPrice <= min Then costX = min * 1.1 ElseIf dPrice <= CapVal Then costX = CapVal * 1.1 Else costX = dPrice * 1.1 End If Else costX = (dPrice - (rate / 60 * CapPer) + CapVal) * 1.1 End If End If End Function Sub Test() Dim dur#, rate#, min#, Flag, CapPer#, CapVal# Dim t!(2), r#(2) Dim n, m& dur = 1.1 rate = 0.04 min = 0.8 CapPer = 1 CapVal = 5 m = 2 ^ 16 t(0) = Timer For n = 1 To m r(0) = cost(dur, rate, min, 0, CapPer, CapVal) Next t(0) = Timer - t(0) t(1) = Timer For n = 1 To m r(1) = costTyped(dur, rate, min, 0, CapPer, CapVal) Next t(1) = Timer - t(1) t(2) = Timer For n = 1 To m r(2) = costX(dur, rate, min, 0, CapPer, CapVal) Next t(2) = Timer - t(2) End Sub -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Bob Phillips wrote : Can you post your testing/timing code, as I (surprisingly) don't get speed improvements of anything like 10-15 times, in fact it is repeatedly slower? Thanks Bob "keepITcool" wrote in message ft.com... 40 Megs per workbook is an awful lot and CAN/MUST be streamlined. does it need to be recalculating formulas? or is it just to calculate invoice lines, which when calculated can be converted to Values? How many different functions have you defined? What does your workbook look like # of sheets # of rows Generally speaking using VBA functions will NOT speed things up, although with complex calculations it make things easier to read.. you could also use Named formulas or Array formulas to bring down calculation times and size. I'm fairly certain than many of us could bring this baby back to size and speed (< 1 minute) ... though most will not do it for free, as the formulas need to be analysed and rewritten, which takes time. A very simple tip might help your functions sepped up tremendously TYPE your arguments as Long or DOUBLE iso as variant. your function becomes 5 times faster when defined as: Function cost(dur#, rate#, min#, Flag&, CapPer#, CapVal#) As Double Just rewriting the VBA functions might help. Following is far more efficient (10 to 15) than yours: please be sure to check the if's and ands.. ADDING capval in the last statement brings same results as yours, but it doesnt make business-sense) Function costX(dur#, rate#, min#, Flag, CapPer#, CapVal#) As Double Dim dPrice# dPrice = ((rate / 60) * dur) If CapPer <= 0 Then If dPrice <= min Then costX = min * 1.1 Else costX = dPrice * 1.1 End If Else If dur <= CapPer Then If dPrice <= min Then costX = min * 1.1 ElseIf dPrice <= CapVal Then costX = CapVal * 1.1 Else costX = dPrice * 1.1 End If Else costX = (dPrice - (rate / 60 * CapPer) + CapVal) * 1.1 End If End If End Function -- keepITcool www.XLsupport.com | keepITcool chello nl | amsterdam Simon wrote : In our last episode Tim Williams wrote: Simon, Without seeing exactly what kind of calculations you're performing and how your code is structured it's going to be difficult to offer any suggestions. Tim Tim apologies, it's always difficult to know just how much info to provide... hope this is sufficient <quote 'old' formula looked like this (mind the wrap)... =IF(OR($A4="",$L$1=0),"",IF(OR(LEFT(F4,6)="XXXXXX" ,E4="YYYY"),"",I F((( ($L$1/6 0)*$K4)*1.1)<($L$2*1.1),($L$2*1.1),(($L$1/60)*$K4)*1.1))) new formula looks like this... =IF(OR(A7="",Rate=0),"",Cost($L7,Rate,Min,Flag,Cap Per,CapVal)) which calls this function ... Function cost(dur, rate, min, Flag, CapPer, CapVal) If CapPer = 0 And ((rate / 60) * dur) <= min Then cost = min * 1.1 ElseIf CapPer = 0 And ((rate / 60) * dur) min Then cost = ((rate / 60) * dur) * 1.1 ElseIf CapPer 0 And dur < CapPer And ((rate / 60) * dur) <= min Then cost = min * 1.1 ElseIf CapPer 0 And dur < CapPer And ((rate / 60) * dur) min And ((rate / 60) * dur) <= CapVal Then cost = ((rate / 60) * dur) * 1.1 ElseIf CapPer 0 And dur < CapPer And ((rate / 60) * dur) CapVal Then cost = CapVal * 1.1 ElseIf CapPer 0 And dur CapPer Then cost = (((rate / 60) * (dur - CapPer)) + CapVal) * 1.1 Else cost = 0 End If End Function </quote the given example is in a sheet where this is calculated once per row, but is indicative of the type of calculations we're doing (another sheet calls this function twice per row, and a third calls a similar function 10 times per row) many thanks S |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with speeding this up...
i normally put a put a stop or breakpoint at the end
and check the locals window. or a msgbox .. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam keepITcool wrote : Next t(2) = Timer - t(2) msgbox format(t(0),"0.000") & vblf _ format(t(1),"0.000") & vblf _ format(t(2),"0.000") End Sub |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with speeding this up...
your code calculates the same thing over and over. do it once and it will imprive performance. DIM variables too. if there are several tests, then nest them... Function cost(dur As Double, rate As Double, min As Double, Flag As Double, CapPer As Double, CapVal As Double) As Double Dim pay As Double pay = (rate / 60) * dur cost = 0 ' default answer If CapPer = 0 Then If (pay) <= min Then cost = min * 1.1 Else cost = (pay) * 1.1 End If ElseIf CapPer 0 Then If dur < CapPer Then If (pay) <= min Then cost = min * 1.1 Else If ((rate / 60) * dur) <= CapVal Then cost = (pay) * 1.1 Else cost = CapVal * 1.1 End If End If Else cost = ((pay - (rate / 60) * CapPer) + CapVal) * 1.1 End If End If End Function "Simon" wrote in message ... In our last episode Tim Williams wrote: Simon, Without seeing exactly what kind of calculations you're performing and how your code is structured it's going to be difficult to offer any suggestions. Tim Tim apologies, it's always difficult to know just how much info to provide... hope this is sufficient <quote 'old' formula looked like this (mind the wrap)... =IF(OR($A4="",$L$1=0),"",IF(OR(LEFT(F4,6)="XXXXXX" ,E4="YYYY"),"",IF(((($L$1/6 0)*$K4)*1.1)<($L$2*1.1),($L$2*1.1),(($L$1/60)*$K4)*1.1))) new formula looks like this... =IF(OR(A7="",Rate=0),"",Cost($L7,Rate,Min,Flag,Cap Per,CapVal)) which calls this function ... Function cost(dur, rate, min, Flag, CapPer, CapVal) If CapPer = 0 And ((rate / 60) * dur) <= min Then cost = min * 1.1 ElseIf CapPer = 0 And ((rate / 60) * dur) min Then cost = ((rate / 60) * dur) * 1.1 ElseIf CapPer 0 And dur < CapPer And ((rate / 60) * dur) <= min Then cost = min * 1.1 ElseIf CapPer 0 And dur < CapPer And ((rate / 60) * dur) min And ((rate / 60) * dur) <= CapVal Then cost = ((rate / 60) * dur) * 1.1 ElseIf CapPer 0 And dur < CapPer And ((rate / 60) * dur) CapVal Then cost = CapVal * 1.1 ElseIf CapPer 0 And dur CapPer Then cost = (((rate / 60) * (dur - CapPer)) + CapVal) * 1.1 Else cost = 0 End If End Function </quote the given example is in a sheet where this is calculated once per row, but is indicative of the type of calculations we're doing (another sheet calls this function twice per row, and a third calls a similar function 10 times per row) many thanks S -- Instead of trying to build bigger and better weapons of mass destruction, shouldn't we be trying to get better use out of the ones we've already got.... |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with speeding this up...
thanks to all for the advice (I'll have to work my way through it though...) FYI, users have already been advised to set autocalculate off, and converting to VB is not really an option at this time (I'm teaching myself as I go) - C is even less likely... Once again, many thanks!! S -- Instead of building bigger and better weapons of mass destruction, we should be trying to get better use out of the ones we've already got.... |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with speeding this up...
I'm puzzled by our "quotation": imo the world should fear 'any' use of WMD. I honestly can't think of 'better' use. All I know is that if we have better use then they will too, whoever we and they may be. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Simon wrote : S -- Instead of building bigger and better weapons of mass destruction, we should be trying to get better use out of the ones we've already got.... |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with speeding this up...
It might help us if you posted your code for comments in speeding "this" up.
-- Don Guillett SalesAid Software "Simon" wrote in message ... thanks to all for the advice (I'll have to work my way through it though...) FYI, users have already been advised to set autocalculate off, and converting to VB is not really an option at this time (I'm teaching myself as I go) - C is even less likely... Once again, many thanks!! S -- Instead of building bigger and better weapons of mass destruction, we should be trying to get better use out of the ones we've already got.... |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with speeding this up...
In our last episode keepITcool wrote:
I'm puzzled by our "quotation": imo the world should fear 'any' use of WMD. I honestly can't think of 'better' use. All I know is that if we have better use then they will too, whoever we and they may be. -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam forget where I found this, but it just appealed to me (I liked the obvious sarcasm) Like you I can't think of a good use for these things, but think we should be making an effort to come up with something (other than the original intended use, of course). Likewise, no use building more of the things if you're not using the ones you've already got... S |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with speeding this up...
In our last episode Don Guillett wrote:
It might help us if you posted your code for comments in speeding "this" up. Don not sure what you're looking for here (example code was posted at the top of the thread...) S |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
help with speeding this up...
In our last episode keepITcool wrote:
40 Megs per workbook is an awful lot and CAN/MUST be streamlined. does it need to be recalculating formulas? or is it just to calculate invoice lines, which when calculated can be converted to Values? How many different functions have you defined? What does your workbook look like # of sheets # of rows I appreciate that it's big (hence my attempt to reduce the number of formulas in the sheets by using functions) but that's a function of the volume of data more than anything - FYI we are calculating costs and comparing them with the invoiced charge. Average number of records per sheet varies per cost type, but it's safe to say that the bigger sheets (not books) would be around 40K records each (more is not uncommon). the object of the excercise is to confirm the accuracy of the charges and to provide some visibility of the calculation process not sure what you mean by recalculating formulas - the formulas are consistent within the context they are called - and the expected outcome will always be a value there are at present 3 sheets referencing functions (note that there are a number of conditional formula's to satisfied before the functions are called, so while there are 10 opportunities to call the cost function per row in sheet 3, only 1 or 2 will pass the conditional statements and call the function) - 1st sheet does 1 function call per row - calls the cost function (see earlier post for details) - there are other calculations, but they are done by formula's at the moment 2nd sheet does 1 function call per row - calls the cost function for 1 of 2 opportunities 3rd sheet does a maximum of 3 function calls per row - calls a function similar to cost (but much simpler - for any given record there will be up to 2 results from 5 opportunities) and another once (more complex - calculates the variance between the calculated and invoiced amounts - calculated amount would be the output from the aforementioned cost function) at present there are 3 'main' functions - these replaced the use of the equivalent formulas in each cell to be calculated, ie 11 times per row for sheet 3 by XXXX rows... hope this helps (it would probably be a lot easier to see the spreadsheet than to try and work it out from my descriptions) once again, many thanks for you're assistance (still working thru the previous posts...) S |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Speeding up calculations | Excel Discussion (Misc queries) | |||
Speeding Up A Spreadsheet | Excel Discussion (Misc queries) | |||
speeding up vlookup | Excel Programming | |||
Speeding up writes to cells? | Excel Programming | |||
speeding up a macro | Excel Programming |