Fix Circular Reference Formula

Hi,

While pasting a formula, by mistake some circular reference created. The circular reference message is bugging frequently, whenever I'm working with the file. Could somebody please help me how to get away with this message.

Thanks & Regards,


Hi,
I am using windows XP and operating excel 2003 (i think). I am currently attempting to create a formula to use in a debt management spreadsheet. Basically, i am trying to take a money value, and have it deducted from a existing money value that can continually be changed each month. Example, in cell a1 i would like to enter the amount payed on a credit card per month. Cell a1 would then be deducted from cell a2, an existing balance. This would then allow me to keep track of the current balance as i paid towards it each month. Every time i type in =a2-a1, in the a2 cell it tells me its a circular reference and i can't follow any other help. It seems so simple. I just can't make it work. All i want to do is type in one number every month and have it deducted from a total that can change every month. Thanks for the help... this will help me greatly and solve some frustration..dan

Hi

I have a formula with circular reference. When a I put any valur in any of
these reference cells the formaula gets overwritten by the value. Is there
any way that I can put values in any of these cells without overwriting the
formuls to try various permutations. Below is the example:-

Cell A B C
500 10 2%
Formula +C1*A1 B1/A1

Cell B1 & C1 are having formula with cicular reference. What I am trying to
get is that if I change the B1 value to 50 from 10, then C1 should
automatically change to 10% & vice versa. OR If C1 is changed to 10% B1
should change to 50 so that I can try as many combinationas possible.

The problem here is that once i put "50" in B1 or "10%" in C1 the formula
gets overwritten with the absolute value.

Would be thank ful if anybody can suggest any solution for this?

Regards
Abhi

Help!

I’ve accidentally created a ‘circular reference formula’ and can’t get rid of it.

When I open my Excel worksheet I get a message giving me three options.

But none of these options help if I don’t want any formula at all.

I’m working in Excel 2003 and only use it for data storage purposes and don’t have the need for formulas.

Hi

I have a formula with circular reference. When a I put any valur in any of
these reference cells the formaula gets overwritten by the value. Is there
any way that I can put values in any of these cells without overwriting the
formuls to try various permutations. Below is the example:-

Cell A B C
500 10 2%
Formula +C1*A1 B1/A1

Cell B1 & C1 are having formula with cicular reference. What I am trying to
get is that if I change the B1 value to 50 from 10, then C1 should
automatically change to 10% & vice versa. OR If C1 is changed to 10% B1
should change to 50 so that I can try as many combinationas possible.

The problem here is that once i put "50" in B1 or "10%" in C1 the formula
gets overwritten with the absolute value.

Would be thank ful if anybody can suggest any solution for this?

Regards
Abhi

Did you know that circular references can hide inside of formulas
and spring up unexpectedly? Here's an example you can test.

Consider the following standard usage of the IF function:

=IF(MyCond, MyFormula_1, MyFormula_2)

Now, what if a circular reference existed in MyFormula_2. As long
as MyCond returns TRUE, we would have no idea there is a circular
reference in the spreadsheet.

This is interesting for more than one reason. Obviously, we need
to be careful when setting up an IF function so as not to create
circular logic. If the formulas are purely mathematical, we might
even consider using binary logic to accomplish the task (see the
"BinSwitch.zip" example file on the website). Binary logic would
mathematically process each MyFormula element so circular logic
would stand out.

As I mentioned, there is another side to this that is interesting.

MUCH more intersting!

If we should happen to actually need circular logic in a model
you could embed the circ ref within an IF function! This way, you
could actually create model switches to turn on/off circular
formula logic!

"Why is this exciting?", you may ask.

Remember, the downside to using circular logic is that it throws
you into an iterations situation. You get the little "Calculate"
message at the botton of the Excel app. Once you've made the
commitment to use circular logic you've told the model to ignore
all circular formulas you create. That prevents us from knowing
if we accidentally create bad circular references.

Well (here's the neat part) if I embed all my circular reference
formulas within IF functions and tie all those IF's to a single
on/off switch, I can easily disable all the circular functions
in my model and turn off iterations momentarily to see if there
are any unexpected circular refs in the model!

AHAAAA! Perhaps I've finally got a way to "safely" incorporate
circ ref logic in my model. Now I guess I'll have to modify my
hard stance against circular reference logic in models.

Aaron

www.XL-Logic.com

Greetings All -

I feel like I'm living the story of the little Dutch boy with his finger(s) in the dike. As soon as I solve a problem somewhere, a leak pops up somewhere else!

Today's issue is circular references. I fixed (or thought I did) my last problem, and started autofilling the corrected cell up and down the column. It took Excel a little longer than usual to copy; maybe 3-5 seconds. When it finally completed, it popped up the dialog box that warns of a circular reference that it can't display for me. The cell it's identifed is then listed in the Status Bar, and I'm on my own to find and correct the situation.

"No worries", I tell myself. "All I have to do is look in the listed cell for a reference to itself, and start splitting up the formulas into different cells. Then I can have the original cell look in the new cells for data. Problem solved." Oh, if only! (g)

For starters, the cell identified as having the circular reference doesn't have a direct reference to itself. "No worries", I tell myself. "I'll just trace the Precedents and see which of those cells reference itself or this cell. Then I'll break that cell down into it's component parts and ship off the offending routine to a column at the end of the spreadsheet, then reference it from the original cell". Oh, if only! (G)

It turns out that not only does the original not reference itself, but neither do any of its Precedents. As I started randomly deleting and swapping components of the formula, trying to get a bead on the problem, I quickly run into a series of cells all trapped in circular references. This is not good, and forced me to leave the building and take in a matinee to clear my head. (g)

I'm back now, and ready to engage in some rational thought. At this point, I'm only looking for general troubleshooting advice and suggestions, though I'm happy to provide specifics it that will help.

All input appreciated. Thanks in advance!

Rick

I have a workbook that I am developing that contains quite a few array formulas. Everything works great, all values update normally, but Excel tells me there is a circular reference in the lower left status section.

I am using Win 7 / Office 2007. The file is saved as xls as some of my co-workers are still using XP / 2003. If I open the workbook on the Excel 2003 then I get NO warnings about circular references at all, everything works as intended.

There is only a single sheet with formulas, and I have run the formula auditing on every single cell that contains a formula - checked precedents and dependants to no avail. If I try "formula > Error Checking" - Circular references is greyed out like it can not find a problem - but the message persists in the lower left.

At this point I can not upload the workbook as it contains sensitive info, but any pointers on correcting this "issue" (if there really is one to fix)

If needed, let me see if I can cleanse the file and upload it

Just curious what I should be looking for - there are about 300 total formulas, about 100 of them being array formulas ( a list of 30 items)

The array formulas are used to calculate a weighted average and a few other things that they are well suited for. i know that performace takes a hit using these things, but they are the cleanest solution to my setup and have had good luck in the past using them with Excel 2003 - now that I am migrated over to Excel 2007 I get this message on the same workbooks that were flawless on Excel 2003

Any hint, tricks, or tips would be greatly appreciated

-Lee

Workbook attached. Workbook contains 53 sheets. 52 sheets are identical and represent one sheet for each week of the year. At the bottom of each column is a SUMIF function that member JimFuller1 showed me how to use. The 53rd sheet is where I would like to calculate all of the totals (per item and per first-aid kit) I tried using the same SUMIF principal, applying it to all sheets but I keep getting a circular reference. I asked a coworker of mine and he said that it was due to the fact that the cells I am trying to put in the SUMIF function allready are part of a different SUMIF formula.
Is there a way around this ?

Faud

PS and always guys, thank you immensly for the help

Hey, advice and help is extremely appreciated to my pathetic vba skills...i'll try to illustrate my prob below:

A B C

Above are three columns A, B and C where i have my info. the formula in cell C is (A-B). i need to be able to cut and paste the values of C onto A (thus deleting values in column A and replacing it with those in C) but keep the formulas in C intact so that it will recalculate new values. I can't seem to do it in the first place and it being a circular reference just makes it worse!

I have a spreadsheet that I need to update a cell (U4) based on another cell's (L4) value. The formula is =IF(L4>=1,TODAY()+7*L4,"—") for cell U4. It works fine when the value of L4 changes from blank to a number or from a number to another number. The problem is that if I change L4 today to 1, I see today's date plus the number of days in U4. For example, if I enter 4 in L4, U4 changes to 27-Oct-11. If I open the spreadsheet tomorrow, 28-Oct-11 appears. Once there is a date in U4, I don't want it to change. It would be great if I could have this in the U4 formula, =IF(AND(L4>=1,U4"—"),TODAY()+7*L4,"—"), but it then gives me a circular reference.

Do you have any suggestions?

Hiya

I am confused about a formula that someone else has set into an excel sheet to calculate something. However I am confused (not hard I know!!)!..

The formula is =IF(SUM($D25:$F25)>0,G25/SUM($D25:$F25),W$31)

What I don't understand is that the cell this is in is W31...!!!

How can this work - is it not a circular reference..?????

Please explain...

Thanks

Hi all,
I have a data workbook (values, and matrices) and a workbook does the
calculation (contains UDF). Calculation workbook with circular references is
linked to Data workbook.
When a value is changed in data workbook, the calculation workbook ALWAYS
returns a wrong answer (although the UDF is thoroughly tested and always
yields expected results). It can be corrected when the FIRST (only the
first) cell of the matrices is modified (even by adding 0 to the formula).
Once that problem is fixed, we can then change the value and it will always
returns the right value. Modifing non-leading cells on the matrix does force
recalculation, but it will always give wrong answer.
Does anyone have any idea on how to troubleshoot the problem?
Thanks

I would like to allow for a circular reference, but seem unable to do this by either using the VB code:

Private Sub Workbook_Open()
Application.Iteration = True
End Sub

OR selecting the Iteration check box in Tools --> options.
(They work, but not when the formula is copied)

I really want to do something basic:

I need to calculate X as follows:
X
A
B
C
X+A+B+C

D
E
F
D+E+F

Where X is the difference between (D+E+F)-(A+B+C)
and (X+A+B+C) = (D+E+F)

(All amounts are fixed, except X which is calculated by the difference between the two sums.

Surely there must be an easy way to calculate the difference.
Then populate X with the difference outside a direct link?

In this case I would like to avoid VBA code to do it.

Thanks in advance, Francois



I created a rather large P&L projection model (11+ Mb, 68 sheets). When
modifying the 2007 sheets using "methodology" used in 2006 sheet, a Circular
Reference error appeared.

Note: the methodology I developed for 2006 worked fine and did not generate
a circular reference message. Only when I was creating/copying the new
sheets/formulas into the 2007 area did the problem occur.

Several hours later, I need some help...

Information which may help you help me/actions taken to ID root of problem:
- no cell location appears next to the Circular Reference msg in the lower
toolbar, on any of the sheets/tabs in the spreadsheet
- the Circular Reference toolbar has 1 address shown in the drop down list,
but it cannot be "selected"
- I have systematically deleted sheets out of the wb (from 68 down to 1) and
the message hasn't disappeared
- I have "valued" every single sheet in the wb, effectively eliminating all
formulas, yet the Circular message is still displayed
- I have experimented with the Iteration & Maximum Change settings in
Tools/Options/Calculation, to no avail.

20 plus years using spreadsheets, and I'm totally lost how to find/fix this
problem...

Any suggestions? Any possibility that the message is erroneous, that a
"flag" has been set that needs to be reset (I'm reaching now...)

Please help
--
Randy

I posted a message a few hours ago regarding my problem with a frequency
formula I was trying to use with students' grades on a spread sheet. Biff
answered and solved my problem. I did what he suggested and it worked!! (A
copy of my problem and Biff's response is below this paragraph.) Then I hit
a key by mistake and the whole thing got screwed up. So, armed with Biff's
solution, I entered the same information Biff suggested, in the exact cell
cells and rows, but now it does not work! I am totally flumoxed since it
worked 5 minutes ago. Anyway, if Biff is out there, or anyone else with a
solution, I'd sure appreciate the help. Here's the messaging:

>-----Original Message-----
>Hi!
>
>With the grades in the range B50:B60, the "bins" in the
>range C50:C57
>
>Select the range D4957.
>
>Type this formula in the Formula Bar and enter it with
the
>key combo of CTRL,SHIFT,ENTER:
>
>=FREQUENCY(B50:B60,C50:C57)
>
>An alternative to using an array. In D50 enter this
>formula and copy down to D57:
>
>=SUMPRODUCT(--(B$50:B$60>=C50),--(B$50:B$60
>Biff
>
>>-----Original Message-----
>>I am a professor and have a spread sheet containing
>student grades. I am
>>trying to use the frequency function so that I can then
>create a chart
>>showing how many grades occured within certain grade
>ranges. I've got column
>>B with the students' grades (B50:B60) and column C with
>the bins (C50:C58)
>>using score ranges 30, 40, 50 etc. Whenever I finish
>entering the array
>>formulas from a cell in column D, a box appears that I
>have a circular
>>reference and it assigns a value of 0. I've followed
all
>the instructions
>>for trying to fix it, and I've read the knowledge base
>articles. But I am
>>not a computer science professor and cannot understand
>what I've done wrong
>>or how to fix it. Can anyone please help me? Here are
>my columns B and C:
>>
>>B
>>60.72
>>41.4
>>56.58
>>71.76
>>45.54
>>73.14
>>37.26
>>73.14
>>80.04
>>60.72
>>55.2
>>
>>C
>>30
>>40
>>50
>>60
>>70
>>80
>>90
>>100
>>
>>--
>>Thanks very much!!
>>Barrie
>>.

--
Thanks very much!!
Barrie

I have a circular reference that I cannot avoid. For no apparent
reason, the circular reference breaks every now and again. I end up
having to manually paste values into one of the circular cells and
then repaste the circular reference. Is there a fix to this?
Shouldn't iterations fix this?

Thanks in advance.

I've got a circular reference problem.

I have a cash flow statement that has a funds portion on the bottom.
I push the operating cash flow into the funds section to then calculate
interest income which becomes part of the cash flow statement.

Example

Interest Income - Cell b11 - This becomes part of total revenue
which feeds into the cash flow total positive or negative.

The fund section starts with a fixed number and then either increases
or decreases based on the cash flow positive or negative

Example

Fund Balance 10,000 - cell b53
Cash Flow 1,000 - cell b55

The interest income would be 23 if we assume a 2.5% interest rate.
If the calculation drops into cell b11 which would make the total of
b53 & b55 produce 11,023 which the formula would then recalculate on.

Hi all,
I have a data workbook (values, and matrices) and a workbook does the
calculation (contains UDF). Calculation workbook with circular references is
linked to Data workbook.
When a value is changed in data workbook, the calculation workbook ALWAYS
returns a wrong answer (although the UDF is thoroughly tested and always
yields expected results). It can be corrected when the FIRST (only the
first) cell of the matrices is modified (even by adding 0 to the formula).
Once that problem is fixed, we can then change the value and it will always
returns the right value. Modifing non-leading cells on the matrix does force
recalculation, but it will always give wrong answer.
Does anyone have any idea on how to troubleshoot the problem?
Thanks

all,

I am getting circular reference error while using this formula...please suggest if anyone has way to resolve it...

thx in advance...

A2 =IF(A1="buy",(B1+A2),A2)

putting above formula in cell A2..

When you drag or copy a formula which refers to another cell, then that
cell's reference is updated when the formula is dragged or copied across a
spreadsheet. Is it possible to 'fix' the reference of the outside cell such
that the formula refers to a fixed cell irrespective of where the formula is
copied to?
For example a formual in cell B2 containing a reference to cell A1 will
always refer to the cell immediately above and to the left when copied
elsewhere. Is it possible for the formula to be forced to refer to cell A1
irrespective of where it is copied?

im trying to use a formula that says IF (A1=1, B1=2) and apply is to the whole column of B, but it keeps saying that im doing a circular reference. I understand what the circular reference is but how do i do what im trying to do w/o getting one?

Hi guys

Please see attached workbook, sheet 1PL, Column AJ.

I am trying to get this column to pick up the successor task based on the following criteria. In this example, we'll look at row 46 (Task 2.1), so formula in cell AJ46, should idenitfy critical successor based on:
1. Does taks 2.1 exist anywhere in Column K?
2. If so, then on whichever row it exists, is there a "Y" in column AN?
3. If so, this is the critical task
4. If not, the critical task is the task (from all those that have 2.1 in Column K) with the latest finish date.

The correct formula that I want is in cell AJ37, but this returns a circular reference.

A previous version of my formula is in cells AJ46 and AJ64, which doesn't return the circular reference problem, but doesn't give me the right answer either!

How do I get the formula in cell AJ37 (which should return the right answer) work without giving this ciruclar reference problem?

Any ideas greatly appreciated....

Many thanks
Sunil

Hi guys,

First of all I would like to say hello to everyone on the forums .

Now I was hoping someone might be able to help me with an issue I'm having with a formula in exel. I have been trying to find a solution for two days now and can not find it, so I was hoping some one here might be able to help.

I have atached a workbook with the formula and the cells I am trying to calculate.

Here is the problem;

I have two colums which contain data, one has measurements(first column) and the other has a deviation from the mean value (second column) calculated from all the measurements in the first column.

The formula I have created seems to work untill I get a zero reading, once a zero reading has been calculated it seems to throw back random numbers rather than leaving the cell as zero.
I think the problem might be to do with circular reference that I am being warned about when I open the work book.

So basicaly what I would like the formula to do Is return a zero in the deviation column if the measurement column reads zero, and also if possible would I be able to have a + sign in front of my positive values in the deviation column.

I hope this makes sense, and someone might be able to help me.

Thank you for you time.

Rgds,
Spacemonkey.

I couldn't think of a suitable title but I'll try and explain.
I have attached the sample.
What I want seems to be very complicated and I don't know if it can be done or not.
I've already had lots of valuable help and just two things need to be ironed out if possible.

The attached timesheet is based on a working day of 7 hours 24 minutes. (I've hidden the 7:24 in row14)
The times are inserted in the cells IN, OUT, IN, OUT
The total hours are in row 13
and Row 15 works out the diference between the actual hours worked relative to the 7:24 for the purpose of flexitime as either credit or debit.
Everything works fine when the first IN & OUT are filled in, eg column 'D' of my sample sheet when only the morning is filled in. and in column 'K' when the complete day is filled in, But when only part of it eg, IN, OUT, IN are complete the calculation goes wrong, showing -9:00 hourstotal time and in row 15 16:24.hours

I tried this formula
in row 13, but I get a circular reference warning, and to be honest I've lost the plot and am stumped. =IF(OR(E8=0,E13=0),"",(E13-E14)),IF(E10=0),"",(E10-E9)+(E8-E7)
What I want to do is only calculate E8-E7 when E9 is filled in and then only do the full calculation of (E10-E9) +(E8-E7) when E10 is complete. If by any chance anyone can understand what I've just tried to explain you deserve a medal, because even I am confused now.
Thanks in anticipation
(edited) I've altered the formula a bit because I missed a bit out but it still doesn't work. Any ideas?