Thanks,

that did the trick. since the other two cells I was referencing were
adjacent to the original reference, I just changed the range to include
all three, and then parsed out the values from the range within the
function.


Niek Otten wrote:
You refer to cells in the worksheet directly from within the function (that is, not via the argument list).
The only way Excel can know in which order to calculate cells is by including all precedents in the argument list.
Even Application.Volatile is suspect; it will always recalculate, but whether that is done in the right sequence is not part of
the specifications of Excel.

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

wrote in message ups.com...
| Function NetPayment(currentYearCashPayment As Range, netTerms As
| Integer, firstDeliveryDaysToGo As Integer, lastDeliveryDaystoGo As
| Integer)
|
| Application.Volatile
|
| Dim previousYearCashPayment As Double
| Dim nextYearCashPayment As Double
| previousYearCashPayment = Cells(currentYearCashPayment.Row,
| currentYearCashPayment.Column - 1)
| nextYearCashPayment = Cells(currentYearCashPayment.Row,
| currentYearCashPayment.Column + 1)
| 'Calculates the annual net cash payment
| 'Assumptions: 1) Cash flows are in chronological order - in a
| continuous cell sequence
|
| 'Step one is to deduce whether it is a first year payment, _
| a second year payment, a final year payment, or a final year + 1
| payment
|
| If currentYearCashPayment.Value = 0 And previousYearCashPayment < 0
| Then 'This means current year is the year after last payment, _
| and need to check to see if any payments fall into this range
|
| If lastDeliveryDaystoGo < netTerms Then 'Means payments
| received in year after last sale
| NetPayment = previousYearCashPayment / (365 -
| lastDeliveryDaystoGo) * (netTerms - lastDeliveryDaystoGo)
| Else
| NetPayment = 0
| End If
|
| ElseIf currentYearCashPayment.Value < 0 And nextYearCashPayment = 0
| Then
| 'this is the last sales year
|
| If lastDeliveryDaystoGo < netTerms Then 'Means payments received in
| year after last sale
| NetPayment = currentYearCashPayment -
| (currentYearCashPayment / (365 - lastDeliveryDaystoGo) * (netTerms -
| lastDeliveryDaystoGo)) + netTerms / 365 * previousYearCashPayment
| Else
| NetPayment = currentYearCashPayment + netTerms / 365 *
| previousYearCashPayment
| End If
|
|
|
| ElseIf currentYearCashPayment.Value < 0 And previousYearCashPayment =
| 0 Then
| ' this is the first year of sales, check to see if any net
| receivables are due in this year
| NetPayment = currentYearCashPayment.Value *
| (firstDeliveryDaysToGo - netTerms) / firstDeliveryDaysToGo
|
| ElseIf currentYearCashPayment.Value < 0 And previousYearCashPayment <
| 0 Then
| 'this is a payment in the middle of the series
| NetPayment = (currentYearCashPayment.Value * (365 - netTerms) /
| 365) + (netTerms / 365 * previousYearCashPayment)
| Else
| NetPayment = 0
|
| End If
|
| End Function
|
|
| Jim Thomlinson wrote:
| Post you code...
| --
| HTH...
|
| Jim Thomlinson
|
|
| " wrote:
|
| I have a function which is having some issues recalculating on a cells
| value change.
|
| The function is dependent on a few variables, functionName(a,b,c)
|
| When I first use the function, it works fine.
|
| When I change variable a, the value of the function changes, but it
| does not change to the proper value (it changes to some value that I
| really do not know where it is coming from, still numeric). however, if
| I press F9, then the formula will recalculate to the appropriate value.
| I have tried adding application.Volatile to my function, but that
| hasn't done anything to remedy the solution
|
| Any Advice?
|
| Thanks,
|
| L
|
|
|