< just changed the range to include all three,

Clever!

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

wrote in message oups.com...
| 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
| |
| |
| |
|