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,

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,

- Trouble with a circular reference formula!
- Circular reference formula
- Circular reference formulas
- Circular reference formula
- Managing Circular References
- Formulas: Circular References
- Array Formulas = Circular Reference Error?
- Circular Reference Due To Formula Already In Cell
- Cut and paste values but keep the formulas intact in cells (circular reference though
- Cell Formulas With Circular References
- Self Reference Formula. Circular Reference
- UDF not returning proper value - circular reference/multi workbook
- Allow a circular reference
- Mysterious Circular Reference
- Frequency Circular Reference - Again!!
- Circular reference fix without manual copy / paste
- Circular Reference-funds portion on the bottom.
- UDF not returning proper value - circular reference/multi workbook
- If..then formula..circular reference error
- Can you have fixed cell reference when dragging/copying formulae?
- IF formula turning up circular references
- Circular reference in INDEX formula
- Circular reference causing formula issue
- Formula results in 'circular reference'

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

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

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.

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

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

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 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

Is there a way around this ?

Faud

PS and always guys, thank you immensly for the help

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!

Do you have any suggestions?

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

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

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

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

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

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 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.

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 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..

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?

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

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 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?