Hi OldGuy,

Glad it works!
There are not that many chances to test this approach in a "real-life" situation, so your post was very welcome. I found some
glitches which I will correct.

The essence of what the function does is supposed to be in the comment lines in the top of the function. I'm instantaneously
prepared to admit I didn't explain very well.
Any comments are welcome, so others may benefit too.

Glad I could help, your question certainly helped me!

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"OldGuy" wrote in message ...
| WOW!! It works, but I haven't the slightest idea why. Thank you!!
|
| "Niek Otten" wrote:
|
| 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
| | ||
| | |
| | |
| |
| |
| |
|
|
|