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....