Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simple Circular Reference Problem
This is the first time i am trying to play around with circular reference.
What I am trying to do is..... I am entering a formula in cell A1, The formula is =A1+B1 (very simple) I have already checked the Iteration box and set the maximum iterations to 1. (tools/options/calculation) the formula is working but.....whenever i save it or do anythingin in the sheet..like..delete something, enter a figure in some other cell etc. etc., it adds the last value again. for example - I enter 2 in B1, A1 shows 2, then I enter 3 in B1, A1 shows 5. Now, if do anything in the sheet it will add 3 again and A1 would show 8...if I keep on pressing CTRL+S, it keeps on adding 3 to A1. Can anyone give me the solution to this problem please? Thanks Gary |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simple Circular Reference Problem
This is one of the reasons that they warn about circular references and why
you have to specifically permit them. What is happening is that as you do other things on the worksheet, the Recalculate event is firing and with your formula Excel says to itself "I must recalculate the formula in A1, so take the current value in A1 and add whatever is in B1 to it" and so you have the ever increasing value in A1 every time Recalculate fires off. You have to be very cautious with circular references although there are places where they will work for you, although there's usually another way to achieve the same end. Consider this - you run a business and have costs for an item in column A, the amount of profit to add to it to get a selling price in B and that total in C. But then you realize you have to pay a fee back on items purchased via credit card and you realize that's going to eat into your profit, so you want to reprice so you get to keep your profit. This can be solved with a circular reference like this: A1: = 99 (your cost) B1: = 15 (your desired profit) C1: = A1+B1 (basic sell price) D1: = C1+E1 (about to become a circular reference - your final sell price) E1: = C1 * .04 (presumes 4% kickback to the credit card company) This creates a circular reference between D1 and E1. But you won't see changes during the recalculate event because the value in C1 is fixed at the sum of A1 and B1. But if I'd just put a value into C1 as you did with A1, then I'd have the same problem. I hope this helps you understand circular references a little more and gives you an insight into when you may be able to get away with using them. "Gary" wrote: This is the first time i am trying to play around with circular reference. What I am trying to do is..... I am entering a formula in cell A1, The formula is =A1+B1 (very simple) I have already checked the Iteration box and set the maximum iterations to 1. (tools/options/calculation) the formula is working but.....whenever i save it or do anythingin in the sheet..like..delete something, enter a figure in some other cell etc. etc., it adds the last value again. for example - I enter 2 in B1, A1 shows 2, then I enter 3 in B1, A1 shows 5. Now, if do anything in the sheet it will add 3 again and A1 would show 8...if I keep on pressing CTRL+S, it keeps on adding 3 to A1. Can anyone give me the solution to this problem please? Thanks Gary |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simple Circular Reference Problem
Forgot - the non-circular method to solve the same problem:
A1: = 99 B1: = 15 C1: = A1+B1 D1: =C1*1+(0.04 / (1-0.04)) "JLatham" wrote: This is one of the reasons that they warn about circular references and why you have to specifically permit them. What is happening is that as you do other things on the worksheet, the Recalculate event is firing and with your formula Excel says to itself "I must recalculate the formula in A1, so take the current value in A1 and add whatever is in B1 to it" and so you have the ever increasing value in A1 every time Recalculate fires off. You have to be very cautious with circular references although there are places where they will work for you, although there's usually another way to achieve the same end. Consider this - you run a business and have costs for an item in column A, the amount of profit to add to it to get a selling price in B and that total in C. But then you realize you have to pay a fee back on items purchased via credit card and you realize that's going to eat into your profit, so you want to reprice so you get to keep your profit. This can be solved with a circular reference like this: A1: = 99 (your cost) B1: = 15 (your desired profit) C1: = A1+B1 (basic sell price) D1: = C1+E1 (about to become a circular reference - your final sell price) E1: = C1 * .04 (presumes 4% kickback to the credit card company) This creates a circular reference between D1 and E1. But you won't see changes during the recalculate event because the value in C1 is fixed at the sum of A1 and B1. But if I'd just put a value into C1 as you did with A1, then I'd have the same problem. I hope this helps you understand circular references a little more and gives you an insight into when you may be able to get away with using them. "Gary" wrote: This is the first time i am trying to play around with circular reference. What I am trying to do is..... I am entering a formula in cell A1, The formula is =A1+B1 (very simple) I have already checked the Iteration box and set the maximum iterations to 1. (tools/options/calculation) the formula is working but.....whenever i save it or do anythingin in the sheet..like..delete something, enter a figure in some other cell etc. etc., it adds the last value again. for example - I enter 2 in B1, A1 shows 2, then I enter 3 in B1, A1 shows 5. Now, if do anything in the sheet it will add 3 again and A1 would show 8...if I keep on pressing CTRL+S, it keeps on adding 3 to A1. Can anyone give me the solution to this problem please? Thanks Gary |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Circular reference mistake | Excel Worksheet Functions | |||
Circular reference help please! | Excel Discussion (Misc queries) | |||
circular reference (Excel 2000) | Excel Worksheet Functions | |||
Force circular reference | Excel Discussion (Misc queries) | |||
Help solve a Circular Reference | Excel Worksheet Functions |