G
Guest
Hello,
I use circular references all the time without issues, however I’m 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
I use circular references all the time without issues, however I’m 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