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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
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 |