Math headache
 

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

Math headache
 

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
|

Math headache
 

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

Math headache
 

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

Math headache
 

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

Math headache
 

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

Math headache
 

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

Math headache
 

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

Math headache
 

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

Math headache
 

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

Math headache
 

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

Math headache
 

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, Im sorry Im not explaining the problem well enough.

"OldGuy" wrote:

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

Math headache
 

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, Im sorry Im 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
|| ||
|| |
|| |
||
||
||
|
|

Math headache
 

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

Math headache
 

BTW try Harlan's solution too. It works.
But if you want to check that with a table of data you'll have to put all his formulas on one line and adjust the references.
You'll probably gain speed then as well, if that is of any importance.

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

Math headache
 

This is way out of my league and I have been working with spreadsheets since
"Multiplan" (a long time). Anyway, as you said it worked on my test sheet,
but when I put in our spreadsheet (with many more columns) I get a #Value
error. The cell is formatted for currency. Normally I can solve this but
not today. Can you assist me one more time? Maybe it's hardening of the
Brain. :-) Thanks

"Niek Otten" wrote:

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

Math headache
 

<Can you assist me one more time?

No problem.
If you like, you can mail me your workbook and I'll have a look at it.

This not what we normally do in these newsgroups, because the idea is that others should benefit. But in this case I think we've
lost most of our co-readers anyway (although I know of one possible exception), so I don't think that argument is valid anymore.

So feel free to mail the book to me. Keep in mind that I'm in Europe, it's 23:17 now, so I probably won't answer in the next 10
hours or so.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"OldGuy" wrote in message ...
| This is way out of my league and I have been working with spreadsheets since
| "Multiplan" (a long time). Anyway, as you said it worked on my test sheet,
| but when I put in our spreadsheet (with many more columns) I get a #Value
| error. The cell is formatted for currency. Normally I can solve this but
| not today. Can you assist me one more time? Maybe it's hardening of the
| Brain. :-) Thanks
|
| "Niek Otten" wrote:
|
| 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
| | || ||
| | || |
| | || |
| | ||
| | ||
| | ||
| | |
| | |
| |
| |
| |
|
|
|

Math headache
 

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


Hi. Don't know if this would help.
If Cost Estimate (CE) is 45,000, then another variation of your equation for
overhead might be:

=MIN(CE/10,25+(9*CE)/100,125+(2*CE)/25,375+(7*CE)/100)

= 3525

We could do an semi-inverse on this, but it's a little tricky. We'll use an
intermediate step.
Let's use your example from above:

A1=5,000

=MATCH(A1,{0,2500,10000,25000})

This returns '2 for out inverse:

We use '2 for the inverse:

=CHOOSE(2,(10 *A1)/11, 100/109 *(-25 + A1), (25 *(125 + A1))/27, (100
*A1)/107)
..
= 4564.220183

Our Overhead is either :

5000 - 4564.22 = 435.78

or given CE above = 4564.22

=MIN(CE/10,25+(9*CE)/100,125+(2*CE)/25,375+(7*CE)/100)
=435.78

- -
HTH :)
Dana DeLouis



"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, Im sorry Im 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
|| ||
|| |
|| |
||
||
||
|
|

Math headache
 

Disregard using the Match function. The inverse of Min is Max
See if this works for you.

If A1 has 5000

=MAX((10*A1)/11,(100/109)*(A1-25),(25/27)*(A1-125),(100/107)*(A1-375))

= 4564.22

and if A1 has 48525, then 45000 is returned.

--
HTH
Dana DeLouis



"Dana DeLouis" wrote in message
...
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€�


Hi. Don't know if this would help.
If Cost Estimate (CE) is 45,000, then another variation of your equation
for overhead might be:

=MIN(CE/10,25+(9*CE)/100,125+(2*CE)/25,375+(7*CE)/100)

= 3525

We could do an semi-inverse on this, but it's a little tricky. We'll use
an intermediate step.
Let's use your example from above:

A1=5,000

=MATCH(A1,{0,2500,10000,25000})

This returns '2 for out inverse:

We use '2 for the inverse:

=CHOOSE(2,(10 *A1)/11, 100/109 *(-25 + A1), (25 *(125 + A1))/27, (100
*A1)/107)
.
= 4564.220183

Our Overhead is either :

5000 - 4564.22 = 435.78

or given CE above = 4564.22

=MIN(CE/10,25+(9*CE)/100,125+(2*CE)/25,375+(7*CE)/100)
=435.78

- -
HTH :)
Dana DeLouis



"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, Im sorry Im 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
|| ||
|| |
|| |
||
||
||
|
|

Math headache
 

Thank you, but it won't be necessary. It only took a good night's sleep. I
really appreciate your efforts. Thanks again I could never have done that
myself.

"Niek Otten" wrote:

<Can you assist me one more time?

No problem.
If you like, you can mail me your workbook and I'll have a look at it.

This not what we normally do in these newsgroups, because the idea is that others should benefit. But in this case I think we've
lost most of our co-readers anyway (although I know of one possible exception), so I don't think that argument is valid anymore.

So feel free to mail the book to me. Keep in mind that I'm in Europe, it's 23:17 now, so I probably won't answer in the next 10
hours or so.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


"OldGuy" wrote in message ...
| This is way out of my league and I have been working with spreadsheets since
| "Multiplan" (a long time). Anyway, as you said it worked on my test sheet,
| but when I put in our spreadsheet (with many more columns) I get a #Value
| error. The cell is formatted for currency. Normally I can solve this but
| not today. Can you assist me one more time? Maybe it's hardening of the
| Brain. :-) Thanks
|
| "Niek Otten" wrote:
|
| 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
| | || ||
| | || |
| | || |
| | ||
| | ||
| | ||
| | |
| | |
| |
| |
| |
|
|
|