Thanks for your time. The steps are not incremental. They jump. The table
was provided to all to calc overhead. I wrote the UDF below to use in my
spreadsheets. However, I am now receiving sums from clients and need to
determine the overhead from the subtotal rather than the estimate. In order
to show the available amount.

Here is UDF (there were a few more steps):

Function Overhead(EST)

If EST < 0 Then
Overhead = 0
ElseIf EST 0 And EST <= 2499 Then
Overhead = EST * 0.1
ElseIf EST = 2500 And EST <= 9999 Then
Overhead = ((EST - 2500) * 0.09) + 250
ElseIf EST = 10000 And EST <= 24999 Then
Overhead = ((EST - 10000) * 0.08) + 925
ElseIf EST = 25000 And EST <= 49999 Then
Overhead = ((EST - 25000) * 0.07) + 2125
ElseIf EST = 50000 And EST <= 99999 Then
Overhead = ((EST - 50000) * 0.05) + 3875
ElseIf EST = 100000 And EST <= 299999 Then
Overhead = ((EST - 100000) * 0.03) + 6375
ElseIf EST = 300000 And EST <= 999999 Then
Overhead = ((EST - 300000) * 0.015) + 12375
ElseIf EST = 1000000 And EST <= 2424999 Then
Overhead = ((EST - 1000000) * 0.005) + 22875
ElseIf EST = 2455000 Then
Overhead = 30000
Else
Overhead = 0
End If
End Function


"Niek Otten" wrote:

Be careful; there seem to be a few line continuations disrupted
A Compile shows them easily

I also noted I have some more work to do to handle errors better. In your case, having "valid" results, it should work.........

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Niek Otten" wrote in message ...
| Maybe this UDF is of some use to you
|
| Please let us know
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
| ' ================================================== =================
| Function Backward(ValueToBeFound As Double, MoreArguments As Double, _
| Optional ReasonableGuess, Optional MaxNumberIters, _
| Optional MaxDiffPerc) As Double
| '
| ' Niek Otten, March 22 2006
| '
| ' This EXAMPLE function goalseeks another function,
| ' called Forward. It works for almost any continuous function,
| ' although if that function has several maximum and/or minimum
| ' values, the value of the ReasonableGuess argument becomes
| ' important.
| ' It calculates the value for ReasonableGuess and for
| ' 1.2 * ReasonableGuess.
| ' It assumes that the function's graph is a straight line and
| ' extrapolates that line from these two values to find the value
| ' for the argument required to achieve ValueToBeFound.
| ' Of course that doesn't come out right, so it does it again for
| ' this new result and one of the other two results, depending on
| ' the required direction (greater or smaller).
| ' This process is repeated until the maximum number of calculations
| ' has been reached, in which case an errorvalue is returned,
| ' or until the value found is close enough, in which case
| ' the value of the most recently used argument is returned
|
| Dim LowVar As Double, HighVar As Double, NowVar As Double
| Dim LowResult As Double, HighResult As Double, NowResult As Double
| Dim MaxDiff As Double
| Dim NotReadyYet As Boolean
| Dim IterCount As Long
|
| If IsMissing(ReasonableGuess) Then ReasonableGuess = 1.5 ' use default Values
| If IsMissing(MaxNumberIters) Then MaxNumberIters = 20 ' that make sense in the
| If IsMissing(MaxDiffPerc) Then MaxDiffPerc = 0.001 ' context of the function
|
| MaxDiff = ValueToBeFound * MaxDiffPerc
| NotReadyYet = True
| IterCount = 1
| LowVar = ReasonableGuess
| LowResult = Forward(LowVar, MoreArguments)
| HighVar = LowVar * 1.2
| HighResult = Forward(HighVar, MoreArguments)
|
| While NotReadyYet
| IterCount = IterCount + 1
| If IterCount MaxNumberIters Then
| Backward = CVErr(xlErrValue) 'or some other errorvalue
| Exit Function
| End If
|
| NowVar = ((ValueToBeFound - LowResult) * (HighVar - LowVar) + LowVar _
| * (HighResult - LowResult)) / (HighResult - LowResult)
| NowResult = Forward(NowVar, MoreArguments)
| If NowResult ValueToBeFound Then
| HighVar = NowVar
| HighResult = NowResult
| Else
| LowVar = NowVar
| LowResult = NowResult
| End If
| If Abs(NowResult - ValueToBeFound) < MaxDiff Then NotReadyYet = False
| Wend
|
| Backward = NowVar
|
| End Function
| ' ================================================== =================
|
| Function Forward(a As Double, b As Double) As Double
| ' This is just an example function;
| ' almost any continous function will work
| Forward = 3 * a ^ (1.5) + b
| End Function
| ' ================================================== =================
|
| "OldGuy" wrote in message ...
|| We have a sliding scale overhead rate based on the following table. I have
|| created a function which takes its place. However, now I need to back into
|| the overhead from the subtotal. I could use "Solver" and solve for subtotal,
|| but have over 800 lines. Is there code I could write and employ the solver
|| add-in or something like that? I would appreciate any help I can get. Thanks
||
|| COSTS ESTIMATE $45,000.00
|| OVERHEAD 3,525.00
|| SUBTOTAL 48,525.00
||
||
|| RATE RANGE SUBTOTAL MAX/RNG
|| 0.1 0 2499 250.00 250
|| 0.09 2500 9999 675.00 675
|| 0.08 10000 24999 1200.00 1200
|| 0.07 25000 49999 1400.00 1750
||
|
|