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

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

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

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

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