Circular Reference Peculiarity
 

Hello,

I use circular references all the time without issues, however Im having
trouble when using user-defined functions in conjunction with circular
references.

Take the following simplified example:

Cell1: Revenue = 1000000 + Expenses * 3%

Cell2: Expenses = 700000 + Revenue * 2%

Where "Revenue" and "Expenses" are named ranges

The above formula works just fine assuming the calculation settings are
setup to allow circular references, but changing the formula to the following
creates problems:

Cell1: Revenue = RoundToNearest(1000000 + Expenses * 3%,5000)

Cell2: Expenses = 700000 + Revenue * 2%

Where RoundToNearest is a simple user-defined formula I created
to round numbers that's a bit more flexible than Excel's built-in Round
function (in the example above Revenue gets rounded to the
nearest $5,000). The error, however, occurs regardless of which
user-defined function I use.

When I open the worksheet containing the second example above, Cell1
(Revenue) and Cell2 (Expenses) contain #NAME? and #VALUE! respectively.
Oddly enough, if I delete Cell1 and press undo the error fixes itself.
However, I have to perform the delete and undo procedure every time I open
the file.

Does anyone have any thoughts on how to correct this problem? Thanks!



PS: The error occurs regardless of what user-defined function I use so I
don't think the error is specific to my RoundToNearest function.
Nevertheless, here's the function code:

Function RoundToNearest(x, Optional Threshold = 1) As Double

RoundToNearest = WorksheetFunction.Round(x / Threshold, 0) * Threshold

End Function

Circular Reference Peculiarity
 

Try:

Function RoundToNearest(x, Optional Threshold = 1) As Double
Dim dTmp As Double
If Not IsError(x) Then dTmp = x
RoundToNearest = Application.Round(dTmp / Threshold, 0) * Threshold
End Function



Note1:
escape from circulating errors

Note2:
iso worksheetfunction use application
(no intellisense, but different error handling
(see dick kusleika's blog)




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Brian wrote :

Hello,

I use circular references all the time without issues, however Im
having trouble when using user-defined functions in conjunction with
circular references.

Take the following simplified example:

Cell1: Revenue = 1000000 + Expenses * 3%

Cell2: Expenses = 700000 + Revenue * 2%

Where "Revenue" and "Expenses" are named ranges

The above formula works just fine assuming the calculation settings
are setup to allow circular references, but changing the formula to
the following creates problems:

Cell1: Revenue = RoundToNearest(1000000 + Expenses * 3%,5000)

Cell2: Expenses = 700000 + Revenue * 2%

Where RoundToNearest is a simple user-defined formula I created
to round numbers that's a bit more flexible than Excel's
built-in Round function (in the example above Revenue gets
rounded to the nearest $5,000). The error, however, occurs
regardless of which user-defined function I use.

When I open the worksheet containing the second example above, Cell1
(Revenue) and Cell2 (Expenses) contain #NAME? and #VALUE!
respectively. Oddly enough, if I delete Cell1 and press undo the
error fixes itself. However, I have to perform the delete and undo
procedure every time I open the file.

Does anyone have any thoughts on how to correct this problem? Thanks!



PS: The error occurs regardless of what user-defined function I use
so I don't think the error is specific to my RoundToNearest function.
Nevertheless, here's the function code:

Function RoundToNearest(x, Optional Threshold = 1) As Double

RoundToNearest = WorksheetFunction.Round(x / Threshold, 0) *
Threshold

End Function

Circular Reference Peculiarity
 

Thanks, KeepItCool. Unfortunately the error did not disappear. Any other
ideas?

"keepITcool" wrote:

Try:

Function RoundToNearest(x, Optional Threshold = 1) As Double
Dim dTmp As Double
If Not IsError(x) Then dTmp = x
RoundToNearest = Application.Round(dTmp / Threshold, 0) * Threshold
End Function



Note1:
escape from circulating errors

Note2:
iso worksheetfunction use application
(no intellisense, but different error handling
(see dick kusleika's blog)




--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Brian wrote :

Hello,

I use circular references all the time without issues, however Im
having trouble when using user-defined functions in conjunction with
circular references.

Take the following simplified example:

Cell1: Revenue = 1000000 + Expenses * 3%

Cell2: Expenses = 700000 + Revenue * 2%

Where "Revenue" and "Expenses" are named ranges

The above formula works just fine assuming the calculation settings
are setup to allow circular references, but changing the formula to
the following creates problems:

Cell1: Revenue = RoundToNearest(1000000 + Expenses * 3%,5000)

Cell2: Expenses = 700000 + Revenue * 2%

Where RoundToNearest is a simple user-defined formula I created
to round numbers that's a bit more flexible than Excel's
built-in Round function (in the example above Revenue gets
rounded to the nearest $5,000). The error, however, occurs
regardless of which user-defined function I use.

When I open the worksheet containing the second example above, Cell1
(Revenue) and Cell2 (Expenses) contain #NAME? and #VALUE!
respectively. Oddly enough, if I delete Cell1 and press undo the
error fixes itself. However, I have to perform the delete and undo
procedure every time I open the file.

Does anyone have any thoughts on how to correct this problem? Thanks!



PS: The error occurs regardless of what user-defined function I use
so I don't think the error is specific to my RoundToNearest function.
Nevertheless, here's the function code:

Function RoundToNearest(x, Optional Threshold = 1) As Double

RoundToNearest = WorksheetFunction.Round(x / Threshold, 0) *
Threshold

End Function

Circular Reference Peculiarity
 

I DID test my code on your scenario.

With Iteration ON, MaxCount 100,MaxChange .001
This works for me in me in xl97,xlXP and xl2003.
Both while edited and saved/reopened.(code in same workbook)


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Brian wrote :

Thanks, KeepItCool. Unfortunately the error did not disappear. Any
other ideas?

"keepITcool" wrote:

Try:

Function RoundToNearest(x, Optional Threshold = 1) As Double
Dim dTmp As Double
If Not IsError(x) Then dTmp = x
RoundToNearest = Application.Round(dTmp / Threshold, 0) * Threshold
End Function



Note1:
escape from circulating errors

Note2:
iso worksheetfunction use application
(no intellisense, but different error handling
(see dick kusleika's blog)




--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


Brian wrote :

Hello,

I use circular references all the time without issues, however
Im having trouble when using user-defined functions in
conjunction with circular references.

Take the following simplified example:

Cell1: Revenue = 1000000 + Expenses * 3%

Cell2: Expenses = 700000 + Revenue * 2%

Where "Revenue" and "Expenses" are named ranges

The above formula works just fine assuming the calculation
settings are setup to allow circular references, but changing the
formula to the following creates problems:

Cell1: Revenue = RoundToNearest(1000000 + Expenses *
3%,5000)
Cell2: Expenses = 700000 + Revenue * 2%

Where RoundToNearest is a simple user-defined formula I
created to round numbers that's a bit more flexible than
Excel's built-in Round function (in the example above
Revenue gets rounded to the nearest $5,000). The error,
however, occurs regardless of which user-defined function I
use.

When I open the worksheet containing the second example above,
Cell1 (Revenue) and Cell2 (Expenses) contain #NAME? and #VALUE!
respectively. Oddly enough, if I delete Cell1 and press undo the
error fixes itself. However, I have to perform the delete and
undo procedure every time I open the file.

Does anyone have any thoughts on how to correct this problem?
Thanks!



PS: The error occurs regardless of what user-defined function I
use so I don't think the error is specific to my RoundToNearest
function. Nevertheless, here's the function code:

Function RoundToNearest(x, Optional Threshold = 1) As Double

RoundToNearest = WorksheetFunction.Round(x / Threshold, 0) *
Threshold

End Function