I using the dependent precedent to track it down and there's nothing. How do I stop this? Delete the cell? Marc

0 |

4/2/2006 9:34:52 PM

Hi . How can I over come the problem of a circular reference on this typicaly example; in cell C1 I type the following formula =a1/b1 and in cell D1. I type =b1*c1, why this create a circular reference? Are there formulas in A1 or B1 that somehow reference back to C1 or D1? HTH Jason Atlanta, GA >-----Original Message----- >Hi . >How can I over come the problem of a circular reference on this typicaly >example; >in cell C1 I type the following formula =a1/b1 and in cell D1. I type >=b1*c1, why this create a circular reference? > >. > ...

I have a worksheet with a number of circular references. I have iteration turned on, and it seems to do what I want. But, if I make some particular mistakes (eg: entering really unreasonable data), I get into a situation where most of the cells are #VALUE. The only way I've found to clear this is to re-load a good copy of the sheet. 'Undo' has no effect. Are there better ways to recover from this? More generally: my knowledge of Excel is kind of spotty. I don't have the time to become expert in Excel, but sometimes I want to use some particular more advanced feature. The...

Is there a way to stop the Circular Reference Help to keep on popping up when I create a formula that is indeed circular reference? I have a whole bunch of formulas every now and then that I need to correct and it creates a circular reference. Now I am aware of where its happening and I can correct them. But the help box is annoying me. Help!!! This is a multi-part message in MIME format. ------=_NextPart_000_03B6_01C67B7E.5837DE70 Content-Type: text/plain; charset="iso-8859-9" Content-Transfer-Encoding: quoted-printable You can set the Calculation Mod to manual instead of ...

Hi, There is a formula called retail ratio which is Product Sales divided by the sum of Product Sales and Service Sales. or RR = P/(S+P). I am trying to create a "What If" chart which would allow someone to plug in values for the Retail Ratio. The Service Sales are also known. What I am trying to do is to solve for what the Product Sales would be. So RR and S are know but P is not. Since it is both in the numerator and denomiator, I am trying to come up with a formula that solves for this. Here is what my chart looks like. Can someone help? Thanks, Chuck Person ...

Thanks heaps for that macro Ken Wright . I have run it and it works. However it loops forever. Can I set it up so that it could save dat from a certain number of iterations? Thanks again Michae -- Message posted from http://www.ExcelForum.com Hi Michael, If you stayed within your thread and did not change the subject Ken would see your reply as a reply to his reply and would see it a lot faster than when you start a new thread. Ken would probably see your post highlighted in RED. Also someone other than Ken might have been able to help you. (not me I avoid anything that hints of circul...

Sometimes I accidentally type a formula which sets up a circular reference. Excel brings up the Circular reference toolbar, and thats fine. However, Excel always wants to open Explorer and give me some help tips, which takes some time. I know what a circular reference is and how to correct it. I like Excel to tell me when I have one (hence the toolbar), however, I don't need the office "help" to load (this takes 20-30 seconds to load this "help"). Is there a setting to stop this "help" from loading? ... Help! Sorry, I am running Excel 200 ----- Jo...

Can anyone see what would be causing me to get a circular reference error??? Thanks! SELECT [BartS1Report].Customer_ID, [BartS1Report].Customer, [BartS1Report].Service_Address, [BartS1Report].Employee, [BartS1Report].Manager, [BartS1Report].Type_of_System, [BartS1Report].Raw_Water, [BartS1Report].Treated_Water, [BartS1Report].Cycles, [BartS1Report].Inhibitor_Level, [BartS1Report].Range_1, [BartS1Report].Range_2, [BartS1Report].Range_3, [BartS1Report].Range_4, [BartS1Report].Range_5, ((SELECT Sum([XX].[ZZZ]) FROM [BartS1Report] AS [XX] WHERE [BartS1Report].Customer_ID & ...

I was asked to look over a workbook that, when opened on a machine running Win2K / XL2000, had the #VALUE! error in a vast amount of cells. I had the wb emailed to myself and opened it on one of my machines (XP Pro / XL2002) and the wb opened just fine. I opened it on another machine (Win98SE / XL2000) and there were the errors again. I can turn Calculation to manual and the workbook will open without the errors. I compared the Calc settings between versions, they were set up the same. Apparently there are circular references in this wb which XL2002 handles differently than does p...

Hi I am constructing a spreadsheet to calculate pays. Problem is I want to round the pays to the nearest $5 and put the balance from the net pay as extra tax. I am using the tax figure to calculate the net pay... which results in a circular reference error. Going to Tools, Options and turning Iteration is not an option and does not suit my purpose. I am trying to find a formula workaround. Gross $1,155.75 Tax $276.00 Union $6.92 Add. Super $10.00 Allowances $60.00 Net $922.83 I would like to round the net pay to $920 in this instance and add the remainder (2.83) as extra tax. Problem is t...

When I open my exel file .. this pop-up open .. circular reference warning one or more formulas contain a circular reference and may not calculate correctly. circular references are any references within a formula that depend upon the results of that same formula. for example, a cell that refers to its own value or a cell that refers to another cell which depends on the original cell's value both contain circular references. for more information about understanding, finding, and removing circular references, click ok. if you wnat to create a circular reference, click cencel to cont...

Hi, I havea financial model for a project that has a 'managment cost' that is a 3.0% function of the total project cost, which itself is a function if the 'management cost' - hence this creates a circular reference. Can anyone please explain how a can create a marco to interpolate (sp?) to arrive at the correct 'managemnt cost' which is 3.0% of the total cost of the project. Hope this make sense. Thanks, JB ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.Ex...

Hi, If I setup a data table as follows: A1=0 A2=0 A3=A1+A2 Then in B3 place the formulae A3+1 and copy it along to K3 And in A4 place the formulae A3+1 and copy it down to A13 Finishing off by selecting the range A3:K13 and choosing Data->Table, Row input cell = A1, Column Input cell = A2 The results in the intersection start from 2 and end at 1.7E+11 Now if I Edit->Copy, PasteValues the formulae in cells B3:K3, and likewise value the formulae in A4:A13 the results change twice over, once for each Copy-PasteValues sequence. What's going on ? Regards Steve Think the ...

The following will not solve (or start to solve?): H4=I4+J4+K4 I4=60.73 J4=constant/(constant*M4) L4=(I4+constant)/2 M4=function(L4) I get this error on cells H4, J4, L4, and M4: #VALUE! Sometimes J4 gets a #DIV/0! error. I know this converges, as other programs using these formulae will converge. Does excel assume 0 for inital guess? -this would explain the #DIV/0 error. Is it posible to assign an initial guess for the cells in question? Iteration is enabled with 100 maximum iterations, and 0.1 maximum change. I appreciate any help on this! Peter J4 will return a #DIV/0 error if M...

I've searched and searched, but to no avail. Mayhap someone here is m final hope. I have an iterative formula which is as follows: T=SIN(T)+6.023*T^0.4 Excel's circular reference gives me back T=0. I've deduced that that i the only answer possible if Excel starts off assuming T=0 (SIN(0)= and 0^0.4=0, then 0+0=0). Is there any form of getting Excel to us another starting value for it's circular reference/iterative process? If not, then what's the use of having such a flawed function which wil falsify certain calculations? Many thanks in advance to those that might be...

Hi , I am new to this forum and right now extremely frustrated. I am using Microsoft works Spreadsheet. I want to calculate total wins by various handicappers each night.So before the season starts there are "0" total wins,each night players will make selections and add wins to their total. I have "cell" B2 to represent the total wins thus far. I have "cell" F2 to represent the wins for the Night. My goal is to have B2 keep a running total of all the wins for each night for a specific handicapper. Thus if handicapper #1 won 3 games the first night his TOTAL wins i...

i am trying to do something that i think is simple. but im am getting circular reference erro.. maybe some one can point me to a differen way to do what i want to achieve. in the attached xls sheet i want D8 to keep a running tally of R8, her is where i get bewildered, i would like R8 to be able to be reset eac week and yet D8 still retai a running total of prior weeks. each week reset the data in cells F H J L N P. in my feeble mind this seems eas to do. .but as the story goes easier said than done.. so, if anyone ca assist me i would be happier than pig in a wollar. im open for an ideas e...

Hi I am trying to solve for a variable that refers to itself. I am using MS Excel 2003. I have a feeling I need to use the iteration function or solve mathematically but am wondering if I can get some hints. Example: solving for "A." All of the other numbers are variables that I can solve for separately, and are used as examples here. A = (2,403,293*54.23)/(129,788,655+(2,405,539*(A-0.07)/A)) If I do something like B44=(B38*B39)/(B40+(B20*(B44-B25)/B44)) I'll get the #DIV/0! error. Same if I try to split out the formula into 2 formulas and making them dependent on...

I using the dependent precedent to track it down and there's nothing. How do I stop this? Delete the cell? Marc On Sun, 02 Apr 2006 21:34:52 GMT, "Marc" <mcnr(N_O-S_P_A_M)@mindspring.com> wrote: >I using the dependent precedent to track it down and there's nothing. How >do I stop this? Delete the cell? > >Marc > What is the formula? What is the contents of all the precedents of that formula? --ron If you have trouble finding the circular reference, maybe Stephen Bullen's utility will help: http://www.oaltd.co.uk/Excel/Default.htm Look for...

I just discovered something that looks like a new excel bug: to reproduce behaviour: 1. open a new workbook; 2. activate circular references; 3. enter those formulas: B4 <- formula is =B5 B5 <- formula is =1+SE(B6;0;1) B6 <- formala is =(B5=2) B7 <- formula is =B5 (same as B4!!!) (NB: SE() this is the standard IIF() function in the italian version of excel;) What's wrong: B4 and B7, will display different results even if they contain the same formula. The result displayed depends on the position of the cell conataining the formula: if it is on the left or above B5 the result...

I know i have a circular reference and I am ok with it. In the old version of excel, you could ignore the circular reference, in this version, the program automatically posts arrows, which you have to remove in the formula auditing toolbar. Is there a way to turn this off? Maybe just notify me of the error and if I click ok, show me the errors, if "ignore" no arrows? thanks, Office button - Excel options - formulas tab = check 'Enable iterative calculation' Mike "Zack H" wrote: > I know i have a circular reference and I am ok with it. In t...

I want to use a formula like the following: =IF(P7=$O$4,IF(R7="",NOW(),R7),"") The reference to R7 to the false condition gives me a circular reference. I expected this. What I want to do is leave the Cell unchanged if there is something in it already (false condition). How can I do this? Thanks.... The FALSE condition of the IF Function is optional. Try: =IF(P7=$O$4,IF(R7="",NOW()),"") Hope this helps! If so, let me know, click "YES" below. -- Cheers, Ryan "Bruce A. Julseth" wrote: > I w...

I use a spreadsheet to track stock portfolios and determine stop orders. I place the Stop Orders every 30 days. To determine the Stop Order Price, I need to take the highest value of either (1) a certain percentage (say 15%) of the cost basis, (2) a certain percentage (say 15%) of the current value, or, (3) the last stop order price. Since stock prices fluctuate, the "last stop order price" could be higher than "15% below the current value", but I can not set it up to automatically track the highest value without creating a circular reference. Following is a shor...

I am trying to create a worksheet to calculate commissions. I am a REALTOR (anybody looking to buy or sell a home?) I am trying to create a worksheet that will calculate my commissions. The problerm is that my commission is based on the amount that has been paid to the broker in the current fiscal year If I have paid less than 28000, my split is 70/30. Once 28000 has been paid to the broker, my split then becomes 100/0 split Here are the critical columns (g,h,i) Sale_Price : Brokerage_Percent :Brokerage_amount This is the last way I have tried to get this working.... Sale_Pr...

This is what I'm trying to do and I can't seem to get a circula reference to work. I want to enter a numerical weight value into cell. I then want the cell to convert that number to a % based on th sample size and display the result in the original cell. So if I hav my sample size of 100 grams in cell A1 and I enter my defect weight o 10 grams in cell B1 then I want cell B1 to calculate and display as percentage. Can I do this or is there a simpler way that I am missing I don't really want to do calculation in another cell as I am tryin to create a form. Any suggestions ---...

Hi! Has anyone used a worksheet with circular references? I'm working on a spreadsheet which contains quite a lot of circular references. I have managed to sort some of them out by adjusting the number of iterations in (tools>options>calculation) but there is a limit of 10000 iterations and that's not enough. Is there a way to increase this number of iterations? Thanks. Natasha ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Why are you using circular re...