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