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

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
|

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

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

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

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

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

Hi,
You lost me in the calculation. 3525/45000= 7.83%, which is not in the
table. A table lookup should not be difficult to do. Take a look at vlookup
and use "true" not false on the largest amount of the range, the column with
the rate should be returned. If this is not what you are trying to achieve,
maybe it is a circular reference? This you can allow in the work sheet,
Tools/Options/Iterations.


--
David


"OldGuy" wrote:

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

<You lost me in the calculation. 3525/45000= 7.83%, which is not in the table.

Yes, that worries me too. If the scale is actually not sliding, but has "jumps" or "gaps" then iterating towards an input value is
rather obscure.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"David" wrote in message ...
| Hi,
| You lost me in the calculation. 3525/45000= 7.83%, which is not in the
| table. A table lookup should not be difficult to do. Take a look at vlookup
| and use "true" not false on the largest amount of the range, the column with
| the rate should be returned. If this is not what you are trying to achieve,
| maybe it is a circular reference? This you can allow in the work sheet,
| Tools/Options/Iterations.
|
|
| --
| David
|
|
| "OldGuy" wrote:
|
| 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
|

"Niek Otten" wrote...
<You lost me in the calculation. 3525/45000= 7.83%, which is not in
the table.

Standard newsgroup quoting would be nice. In-line quoting would be
nicer still.

Yes, that worries me too. If the scale is actually not sliding, but
has "jumps" or "gaps" then iterating towards an input value is
rather obscure.
....

|"OldGuy" wrote...
|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

[reformatted]
|COSTS ESTIMATE____45,000.00
|OVERHEAD___________3,525.00
|SUBTOTAL__________48,525.00
|
|
|RATE______RANGE_____SUBTOTAL__MAX/RNG
|0.10______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

The MAX/RNG column is the maximum overhead coming from that particular
range, so, e.g., 675 is given by =(9999+1-2500)*0.09. If the table
(including headings) were in A6:E10, the MAX/RNG column would be given
by the formulas

E7:
=(C7-B7+1)*A7

E8:
=(C8-B8+1)*A8

E9:
=(C9-B9+1)*A9

E10:
=(C10-B10+1)*A10

Unfortunately, the SUBTOTAL range appears to be screwed up. To be
useful, it should contain a top-down running sum of the MAX/RNG
column, so given by the formulas

D7:
=E7

D8:
=D7+E8

D9:
=D8+E9

D10:
=D9+E10

Then overhead as a function of the cost estimate would be given by

=LOOKUP(CostEstimate,$B$7:$B$10,$D$7:$D$10)
-(LOOKUP(CostEstimate,$B$7:$B$10,$C$7:$C$10)-CostEstimate)
*LOOKUP(CostEstimate,$B$7:$B$10,$A$7:$A$10)

while overhead as a function of the subtotal would be given by

=LOOKUP(Subtotal,$B$7:$B$10+$D$7:$D$10,$D$7:$D$10)
-(LOOKUP(Subtotal,$B$7:$B$10+$D$7:$D$10,$C$7:$C$10+ $D$7:$D$10)
-Subtotal)/(1+1/LOOKUP(Subtotal,$B$7:$B$10+$D$7:$D$10,$A$7:$A$10))

No need for Solver or VBA.