< just changed the range to include all three, >
Clever!
--
Kind regards,
Niek Otten
Microsoft MVP - Excel
| 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
| >
| > | 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
| > | >
| > | >
| > | > "(e-mail address removed)" 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
| > | > >
| > | > >
| > |
|
