Yes, you can do that. In my example I computed A (in column D) from C. Overhead is C-A.
The present A, B and C columns were just there to check that the answer in D is right.

Try a new sheet. Enter whatever number you like in A1 (this means the Subtotal). In B1, enter =backward(A1,0). This should give
you the CostEstimate.
Check the answer with your Overhead function

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"OldGuy" wrote in message ...
| Niek, I was amazed but a little premature. What I need to be able to do is
| compute the values for A and B from a known C. i.e., If I know the subtotal,
| column C, is 5,000 how can I determine that A is 4,564.22 (estimate) and B
| (overhead) is 435.78? I can do it one at a time using the "solver" addin.
| Thanks again, I'm sorry I'm not explaining the problem well enough.
|
| "Niek Otten" wrote:
|
| 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
| || ||
| || |
| || |
| ||
| ||
| ||
| |
| |
|
|
|