Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Circular Reference | Excel Worksheet Functions | |||
Circular reference help! | Excel Discussion (Misc queries) | |||
'circular reference' | Excel Discussion (Misc queries) | |||
Circular reference | Excel Discussion (Misc queries) | |||
Circular Reference | Excel Programming |