Free Microsoft Excel 2013 Quick Reference

- Circular Reference Problem - HELP
- Circular reference
- Validation & Circular Reference Problems
- How to Avoid Circular Reference with Late Fee Calc. Problem
- Identify Circular Reference
- If Function & Circular Reference
- Circular reference when using RANK function
- Nesting: Circular Reference - Disappearing toolbar
- Circular Reference With Dynamic Named Range Used In Formula
- Formulas: Circular References
- Circular Reference>>>>>>>Help Me Plz!
- Anyone good at maths? Excel Horse Racing Problem
- How to avoid circular reference?
- Quick Intentional Circular Reference
- Mysterious Circular Reference
- Getting rid of a circular reference error message
- Problem with Circular Reference
- Frequency Circular Reference - Again!!
- Highest Value / Circular Reference Help
- Circular Reference Madness

I have a circular reference problem with my interest income which calculates off of the money in the fund and the operating cash flow.

Here is the set-up

Sales - Cell B5

Interest Income - Cell B11 - This is pulling from a section where the operating cash flow feeds to.

Total Income - Cell B14

COGS - Cell B40

Operating Cash Flow - B43 - total

Fund Opening Balance - B 46

Net Cash Inflow - Outflow - B47 - Pulls from B43 above

Based on how much money the fund has Fund Opening + Net cash Inflow

interest is calculated on the sum of the two and drops in to cell B11

which is where the circular problem comes into play.

Any help would be appreciated in this matter, and for any additional questions, comments, or thoughts I may be reached at DarqPhire@yahoo.com or JayBoyer@gmail.com

Thanks

* Rent

* Late Pay Interest calculated on Rent balance

* Payment

* Payment Part Rent

* Payment Part Late Fee (to be deducted from payment preferrentially)

* Rent Balance

* Late fee Balance

* Combined Balance

* as soon as I want to calculate the Late Fee based on the Rent Balance, the circular reference happens and nothing calculates right any more. I didn't figure out yet how to avoid this.

* Do I have to do this completely different ?

http://www.herber.de/bbs/user/69698.xls

Thx for help.

Regards,

Frank

This is the link to the thread where ByTheCringe2 showed me the power of SUMPRODUCT and sorted my problem out. I don't think you'll need it, but I'd rather reference it before Roy reprimands me! lol

I am building on the same topic, though.

Hi All!

I have an investment where the monthly interest is added back to the capital. The next month earns interest on the higher figure and this higher interest is again added onto the capital and the story starts all over at the next month. OK, that's easy to do (Again, I think!).

All works fine UNTIL the end of the Tax year is reached and Tax on the interest earned is payable from the investment as the capital carried forward is reduced by the Tax payable. In the attached file I show how I calculate the interest earned for each following Tax year (which always ends at month 2) using SUMPRODUCT. Sumproduct is used because I never know whether the initial investment is made in month 5, 11, or any other month and by using certain controls (thanks ByTheCringe2!) I can simply total the interest earned in each tax year - the months between month 3 (start) and 2 (end).

On a seperate sheet I calculate the Income Tax payable as follows:

Firstly, I calculate the standard Tax payable on the investor's income from other sources. I then add the taxable portion of the interest to the standard income from other sources and calculate the tax payable on that combined figure. I then deduct the two figures from each other and get the increase in Tax payable because of the interest earned.

This "increase in Tax payable"-figure is then returned to the spreadsheet on which the interest is added onto the capital. See Sheet2! cells O9:O15. The Tax payable for each Tax year is placed next to taxable values I calculate using SUMPRODUCT (again!) in column N9:N15.

VLOOKUP is then used to transfer this Tax payable figure into the calculations (Column K9:K68) as and when required (only when tax is payable) - and should be deducted from the value in (Same row) Column H and that answer should be transferred to next row Column E (Balance at beginning of month) -

BUT it causes CIRCULAR REFERENCE faults and I am absolutely frustrated at not being able to figure out how to sort it out!

Sheet2, Column E9:E69 (See Sheet2!E19 for Comment explanation) in the attached workbook is where I need the correct answers.

Help, please!

Harry

P.S. The Tax calculations are not included but will be if needed - I do not think that it is causing the problems

I posted the following problem in this link: http://www.excelforum.com/showthread.php?t=600181

BUT, when I didn't get a reply, I decided to post it here. The problem is as follows:

i got three cells namely; M7, AC7 and BA7. Initially, an input value is entered in cell M7 which will be processed to give AC7. An IF function is entered in cell BA7. This IF function is really simple. It states that IF the difference between the values of M7 and AC7 is greater than 1 then, decrease 0.01 from M7, otherwise, retain the value of M7. The syntax is:

=IF((M7-AC7)>1, M7-0.01, M7)

As I told you, it is really simple and looks fine. I have no problem with it so far. The problem came about when I tried to make the circular reference. I am trying to make M7 to equal BA7. Well, before I made the circular reference, I made a manual entering of the values of cell M7. What I have done was that I put a value like 29.94 in M7 then I let the calculation to proceed. The the value that I get in BA7 (i.e. 29.93), I re-entered it in M7 then I repeated the process until I got a value (i.e. 29.90) that was not changing due to the use of the IF function. The manual calculations are shown in Table2 of the attached file.

I said to myself instead of entering the values manually, I can use a circular reference so that the cell M7 = cell BA7. Unfortunatelly, the circulation of the values instead of giving me 29.90 which is the right answer, it gave me 29.89 which ment that Excel has done one more step further of the IF function. In other words, Excel should have stopped decreasing 0.01 from M7 when M7-AC7 was less than one. This is achieved at M7=29.90 (as clearly seen in Table2). However, Excel went one step further and done: BA7=29.90-0.01=29.89 then it stopped subtraction at 29.89 which is not correct. I don't know why.

I tried with two other people to resolve this dilemma but we couldn't. Then, I decided to seek help from you if you can. If you want to ask any question for clarification I would be more than happy to reply to you.

I am building a pretty basic system for golf scores. I am not really an expert at excel, but have knowledge of some functions. The problem I have, and it seemed to arise from nowhere, is that in my ranking table of players scores, I have an option to rank by 2 different things, gross score and net score.. the option is changed using a bullet button. It ranks by net score fine, however when I try to select it to rank using gross score, a dialogue appears telling me that there is a circular reference, and that a cell in the formula refers to the forumlas result. I have checked and checked but cannot find the circular reference that it is referring to. I have attached a stripped down version of my system, with just the neccessary bits to the problem, and would appreciate it if someone could have a look over it and offer any help/advice. The problem can be seen in the "ranking table" sheet.

Thanks in advance

I hope someone can help - I have a problem creating a circular reference with dynamic ranges. I have searched already but can't find the specifc problem.

I have a spreadsheet where I use vba code to insert extra columns. Within the worksheet there are sum calculations at the end of each row. When I add a new column within the table I would like the sum calculation to expand to account for this extra column.

Now, I thought that dynamic ranges would be the way to go but the only way I can see to do it creates a circular reference as the CountA function trys to count the cell with the sum function in it.

Any suggestions?

Thanks in advance.

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 bunch of sheets in a workbook and all the total calculations of each sheets is transferend to the sheet called "COST SUMMARY". Now the BACKGROUND:

1) I have one sheet called "Misc. Project". It has different field in it, like :

Field Inspector

Safety Items (etc etc)

Each of these fields have a column called "Total" which has the following formula in each cell:

=IF(E28=0,"",IF(SUM(1+'COST SUMMARY'!$L$6/100)*G28*I28*(1+J28/100)*(1+K28/100)=0," ",SUM(1+'COST SUMMARY'!$L$6/100)*G28*I28*(1+J28/100)*(1+K28/100)))

2)so "Total" column depends upon these cell values mentioned in the above formula. Now for a particular field called "Safety Items) i don't want to put a number manually in "G28" cell. I want this cell value to be the 1 % of the Sun totals on the "COST SUMMARY" sheets. Now that sub-total depends on the result of the above formula.

PROBLEM:

The problem is if i put the formula like "='COST SUMMARY'!Sub-total*0.01), it gives me a circular reference. Is there any way i can get rid of circular reference and accomplish the desired results as well. ( i want G28 value to be the 1 % of the Sub-total on the 'COST SUMMARY' sheet)

I hope i presented my problem clearly. Please help me in that, my dead line is coming up. All thoughts would be appreciated. Thanx in advance

Regards

Ram

******** ******************** ************************************************************************>Microsoft Excel - file.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutC2D2E2C3D3E3C4D4E4C5D5E5C6D6E6C7D7E7C8D8E8C9D9E9C10D10E10C11D11E11C12D12E12C13D13E13C14D14E14C15D15E15C16D16E16C17D17E17C18D18E18=

ABCDEF1Â oddsÂ (decimal)BettingÂ onÂ allÂ horsesÂ (F2:F18/B2:B18)SumÂ ofÂ allÂ betsÂ otherÂ thanÂ oneÂ adjacentProfit/lossÂ perÂ outcomeÂ 2HorseÂ 12.25£444.44£718.08£281.9210003HorseÂ 24.3£232.56£929.97£70.03Â 4HorseÂ 37.6£131.58£1,030.95-£30.95Â 5HorseÂ 48£125.00£1,037.53-£37.53Â 6HorseÂ 585£11.76£1,150.76-£150.76Â 7HorseÂ 624£41.67£1,120.86-£120.86Â 8HorseÂ 747£21.28£1,141.25-£141.25Â 9HorseÂ 854£18.52£1,144.01-£144.01Â 10HorseÂ 954£18.52£1,144.01-£144.01Â 11HorseÂ 1069£14.49£1,148.04-£148.04Â 12HorseÂ 1154£18.52£1,144.01-£144.01Â 13HorseÂ 1289£11.24£1,151.29-£151.29Â 14HorseÂ 1379£12.66£1,149.87-£149.87Â 15HorseÂ 1459£16.95£1,145.58-£145.58Â 16HorseÂ 1569£14.49£1,148.04-£148.04Â 17HorseÂ 1684£11.90£1,150.62-£150.62Â 18HorseÂ 1759£16.95£1,145.58-£145.58Â Sheet1Â

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box

PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

The above table represents what I have so far.

Its a representation of a horse race, the odds for all the horses (not true decimal odds, as I have minus 1 from each odds to represent the profit return only, not including initial stake), the amount required to be bet on each horse in the race to successfuly return £1000 regardless of the outcome of the race, the sum of the betting stakes spent on all of the horses apart from the one in that row, and the product of taking the profit for the horse winning and taking away the sum of all other bets on the other horses.

As you can see, from this assortment of betting patterns, only the first 2 horses make a profit, the others would make a loss. That isnt important, but what I am having a problem with doing is the following:

1. Arrange (automatically) the bets in collumn C such that the sums in column E are all equal (or are near enough equal), i.e will more than likely all be a small negative amount- but importanly, are all equal regardless of the end result of the race.

2. If possible, be able to select an amount for the first 3 or more horses in the list that I want to earn- eg £300, and the rest of bets on the other horses automatically change so that the end result in collum E- again is an exact same amount regardless of the result of the race.

I keep getting a circular reference problem, and FYI, the 1000 used at the start was used only as a way of getting some proportional betting pattern across alll horses such that the result of a horse winning in £ would be the same regardless of which horse won. It is a limitation of my Excel skills that it is there, and feel free to ommit this with respect to problem 2.

Thank you for any help, and I hope I have made this clear enough to understand!

Max.

I was asked to create a simple program to calculate some value based on multiple cells entries. The task, which seemed to be fairly simple at first, has gone far beyond just entering a formula in the Total cell because each cell in this formula must satisfy several conditions, and my boss wants the program to check these condiotions right upon user enters his values in the cells. I used both the Validation tool and VBA code to fulfill this requirement but in the end a circular reference was created and I don't know how to avoid this problem. Then I found this forum and I hope experts will give me a piece of advice.

What I have is a column where cells C7, C8, C9 all must be >0 (I used Validation), numeric, not empty. Cell C7 always greater than C8.

For C7 I wrote this code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$C$8" Then

Range("C7").Select

If IsEmpty(Range("C7").Value) Then

Msg = "Please type in the population size."

Style = vbOKOnly + vbExclamation

Title = "Missing Value"

Response = MsgBox(Msg, Style, Title, Help, Ctxt)

ElseIf IsNumeric(ActiveCell.FormulaR1C1) = False Then

Msg = "The value you've entered is incorrect. Please type in the correct number."

Style = vbOKOnly + vbExclamation

Title = "Incorrect Number"

Response = MsgBox(Msg, Style, Title, Help, Ctxt)

Else

Range("C8").Select

End If

End If

This part works perfectly. Now I have to check C8 but when I wrote same code again (just replaced C7 with C8 and C8 with C9), I created a circular reference: once stepping at C8, the program started an endless loop between C7 and C8. Shortly speaking, I need to check the values in C7, C8 and C9 each time they are entered and make sure they satisfy all conditions. If not, a standard message should appear (not text in the Total cell).

I worked with MS Excel spreadsheets a lot but I am not a professional programmer. The piece of code I wrote and posted here was mostly a result of long Internet research and brainstorming. If somebody knows a good solution to my problem please share it with me.

Thanks

Diana

I'm inexperienced with excel and this is the first time i've used VBA/ macros etc... in excel, so ANY help would be appreciated!

Thanks

xxxx

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

circular reference that, apparently, has got to be there. I added a

private sub to make sure the sheet keeps the ITERATION checkbox

selected:

Code:

--------------------

Private Sub Workbook_Open()

Application.Iteration = True

End Sub

--------------------

One problem still is that the sub apparently goes into effect AFTER the

workbook checks for circular references. What happens is that the person

opening the workbook gets the "excel cannot do this" message that is

rather unsightly and potentially confusing to the user.

How do I:

A. Get rid of (supress?) the warning box that appears or

B. Have the sub get called upon BEFORE the workbook checks for circular

references?

C. Figure out a way to avoid the circular reference altogether.

Anything would be good at this point. Please help!

+-------------------------------------------------------------------+

|Filename: lease.zip |

|Download: http://www.excelforum.com/attachment.php?postid=4516 |

+-------------------------------------------------------------------+

--

sharkfoot

------------------------------------------------------------------------

sharkfoot's Profile: http://www.excelforum.com/member.php...o&userid=32164

View this thread: http://www.excelforum.com/showthread...hreadid=525887

I am trying to make a custom amortization table using the one that microsoft

has a teplate for.

I would like to take a calculated value, subtract the supplied for "total

interest" and project that value into the supplied "loan amount".

The problem I am having is that when I try to do this, I get a circular

referance error. How can I correct this without having to write the entire

table from scratch?

Thanks in advance,

Paul M

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

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 shortened version of the spreadsheet:

Column A is a certain percentage below the current value.

Column B is a certain percentage below the cost basis.

Column C is where I have the problem. I need this cell to keep track of the

highest price that was ever reached when I update the spreadsheet. I need to

create a "watermark", as the current value fluctuates.

Column D has the formula to take the highest value of Column A, Column B, or

Column C, which creates my circular reference.

As I update the current values monthly, I have just been copying the values

from Column D (Highest value of A, B, & C) to keep track of this. I know you

can allow circular references, but is there any other way to keep track of a

highest value or achieve what I am trying to?

I can give more info if needed.

I am having a sudden problem. When I press F9 to calculate, I am told there is a circular reference. In the status bar it tells me where it is. BUT THERE IS NO CIRCULAR REFERENCE.

As a result, it will not update my sheet. I have had no problem with this sheet in the past. It is not very complex. I even tried to delete 3/4 of the sheet to see if maybe I was asking it to do too much. No such luck. It just points to another "circular refernece". BUT THERE IS NO CIRCULAR REFERENCE. The toolbar does not come up because the error message says it can't locate the reference.

I try to bring the toolbar up, but it has no formula in it. It's useless to me.

Does anyone know why Excel might do this? I am stressing right now. This is a two month project on the verge of distruction.

HELP!!!!!