I tested quite a few more, with very small increments. Some of them didn't get the specified accuracy within the default number of
iterations. But then again, the accuracy was ridiculously high for dollar amounts.
You can always increase the max number of iterations and/or decrease the fractional accuracy to get valid results.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"Niek Otten" wrote in message ...
|I tested my example function on your data with increments of 100 and it seems to work fine.
|
| Here's what I did
|
| In a worksheet, cell
| A1: 100
| A2: =A1+100
| Copy down as far as you need.
| In B1:
| =overhead(A1)
| In C1:
| =A1+B1
| Copy both down as far as column A
|
| In the Module where your Overhead function resides, paste my Backward function
| In the Forward function, replace the formula
|
| Forward = 3 * a ^ (1.5) + b
|
| with
|
| Forward = a + Overhead(a)
|
| In Cell D1, enter
|
| =backward(C1,0,,,0.0000000000000001)
|
| Copy down as far as Column A goes
|
| You'll see that it reproduces your original column A exactly, no matter what you choose as a start value in A1
|
| --
| Kind regards,
|
| Niek Otten
| Microsoft MVP - Excel
|
|
|
|
| "OldGuy" wrote in message ...
|| 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
|| ||
|| |
|| |
||
||
||
|
|