Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Circular reference problem help Results

Greetings,

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.

I have a problem regarding a circular reference. See the attached lease.xls and you can visualize what the issue is a bit better. The long and short of it is that this is a sheet to figure a lease on an automobile. In order to make the lease figure properly, I need D11 to equal the same thing as D36 (Lease Worksheet tab), but one is dependant on the other so I get a circular reference problem that I can't quite get through. Can anyone help with this?

Do you know of a way to create a group of Data Validation Lists that derive their content from the same source such that each entry which is selected causes other entries to become unselectable? For example, if I select the 3rd item in the list, can the 4th item be striken-out, crossed-out, shaded-out, or otherwise set apart from the others to indicate that it should not be selected? And is there a way to cause this effect to be reversable without creating a Circular Reference; i.e., selecting entry 4 would affect entry 3 in the manner stated above. In short, picking #3 negates #4 &/OR picking #4 negates #3. Also, is there a way to cause a comment to appear in the validation list to indicate this point (not a validation comment, but one within the list), such that any negated entry is still visible but not selectable? If this is not available in standard excel, is there a VBA that might give this result?

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

Hi, I'm trapped in a circular reference problem since I need to calculate "late fee interest" :

* 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

http://www.ozgrid.com/forum/showthread.php?t=68115]

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

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

Hi all,

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 have built quite a complex spreadsheet based model, which appeared to be working correctly. I changed a date, which logically shouldnt cause any errors, however after 10 mins of calculating, a circular reference appears. I activated the circular reference toolbar to trace the problem, but as soon as the workbook has finished calcing - resulting in the same circular reference, the toolbar simply disappears. Any help would be greatly appreciated!!!!!!

Hi People,

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.

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

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

Hi, I have been working on this for hours and hours and cant get my exceptionally small brain around this problem. I hope there is someone here with a maths bent that may be able to help me.

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

Hi,

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 currently doing an ICT project. When I enter my system, before my Userform1 appears (my main menu) a message shows up saying I have a circular reference, I have to click 'cancel' for the message to clear, it also leaves a blue dot over the circular reference. This is an intentional and I do not believe there is a way around it. Considering I've been working on this project for a few months now and have very little time to complete it, I really do not have enough time to think of another formula to get around the problem. Is there anything I can do to get rid of a. the message that appears, b. the blue dots which appear or c. the whole circular reference!

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

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 have a workbook (attached to the message so take a peek) with a
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

If someone could help with my issue, it would be great!

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

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 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 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 have a circular reference issue. I have tried doing a search, but can't find any help on this topic. I really hope someone out there knows about this.

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


No luck finding an answer? You could always try Google.