Using iterations to calculate circular references.

I am working with data that requires using circular references in some 
calculations. I checked the "iterations" box to allow for these calculations 
to work, and Ecxel seems to be calculating everything correctly. The problem 
is that after I close the workbook, when I later reopen it to continue 
working, all the cells that are a part of the circular reference display the 
#VALUE! error. I can fix this by deleting one cell that is part of the loop 
and then undoing that command, or by copying the formula from a cell, 
deleting it, and pasting it back into the same cell. I have to do this every 
time I open the file. I tried recording this method into a macro, but when I 
run it it takes a while since there are multiple sheets with lots of similar 
calculations. Is there a better way to make Excel calculate correctly As soon 
as I open the workbook?

Thanks,
David
0
dave871 (680)
8/18/2004 6:57:03 PM
excel.misc 78881 articles. 5 followers. Follow

2 Replies
495 Views

Similar Articles

[PageSpeed] 12

Hi David,

Do you use manual calculation? If so, did you check the "Recalculate before
save"  box?

-- 

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Dave" <Dave@discussions.microsoft.com> wrote in message
news:93CBD4AD-7F39-4DB5-B714-EBA80CD3ACB6@microsoft.com...
> I am working with data that requires using circular references in some
> calculations. I checked the "iterations" box to allow for these
calculations
> to work, and Ecxel seems to be calculating everything correctly. The
problem
> is that after I close the workbook, when I later reopen it to continue
> working, all the cells that are a part of the circular reference display
the
> #VALUE! error. I can fix this by deleting one cell that is part of the
loop
> and then undoing that command, or by copying the formula from a cell,
> deleting it, and pasting it back into the same cell. I have to do this
every
> time I open the file. I tried recording this method into a macro, but when
I
> run it it takes a while since there are multiple sheets with lots of
similar
> calculations. Is there a better way to make Excel calculate correctly As
soon
> as I open the workbook?
>
> Thanks,
> David


0
nicolaus (2022)
8/18/2004 7:11:00 PM
No, I am using automatic calculation. I tried using manual calculation, but 
it did no good. 

"Niek Otten" wrote:

> Hi David,
> 
> Do you use manual calculation? If so, did you check the "Recalculate before
> save"  box?
> 
> -- 
> 
> Kind Regards,
> 
> Niek Otten
> 
> Microsoft MVP - Excel
> 
> "Dave" <Dave@discussions.microsoft.com> wrote in message
> news:93CBD4AD-7F39-4DB5-B714-EBA80CD3ACB6@microsoft.com...
> > I am working with data that requires using circular references in some
> > calculations. I checked the "iterations" box to allow for these
> calculations
> > to work, and Ecxel seems to be calculating everything correctly. The
> problem
> > is that after I close the workbook, when I later reopen it to continue
> > working, all the cells that are a part of the circular reference display
> the
> > #VALUE! error. I can fix this by deleting one cell that is part of the
> loop
> > and then undoing that command, or by copying the formula from a cell,
> > deleting it, and pasting it back into the same cell. I have to do this
> every
> > time I open the file. I tried recording this method into a macro, but when
> I
> > run it it takes a while since there are multiple sheets with lots of
> similar
> > calculations. Is there a better way to make Excel calculate correctly As
> soon
> > as I open the workbook?
> >
> > Thanks,
> > David
> 
> 
> 
0
dave871 (680)
8/18/2004 7:25:04 PM
Reply:

Similar Artilces:

highlight range, apply calculation to data in cells and paste special to same range
I know how to select a range of cells and copy: Range("O2").Select Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select Selection.Copy but now 1. applying the calculation, which should be value of cell * 1000 2. special past values only to same selected sells ? You enter 1000 in a separate blank cell and copy it then highlight the range to be multiplied and past special-> multiply. that will multiply all the cells by 1000 in the pasted range. Regards, OssieMac "S Himmelrich" wrote: > I know how to select a range of cells and copy: &g...

Credit Card Payments
I've been using MM for years now but just noticed something in MM 2005. If I go to reports and click on "Monthly Income and Expenses" the report doesn't list credit card payments even if I select "Include transfers..." in Customize Report. It seems that the report generator doesn't know about the special Transfer and Credit Card Payment categories. It only knows about the expense category "Credit Card Payments/Transfers". My question is does anyone actually use this expense category? It seems like it could cause some unfortunate side effect...

How can I use Outlook on both my laptop and desktop? I have cable.
and Netgear wireless version 4.0 Dear Bafflegab, have a look on this site, may it helps: http://www.slipstick.com/outlook/share.htm -- Oliver Vukovics Groupware for Outlook without Exchange: Public ShareFolder Notebook Synchronisation for Outlook: Public SyncTool New Domain: www.publicshareware.com "Bafflegab" <Bafflegab@discussions.microsoft.com> schrieb im Newsbeitrag news:59DE2C4E-ADC5-403C-AE3B-7DE1F9AF90C6@microsoft.com... > and Netgear wireless version 4.0 Thanks for the recommendation. It's a great site and one I never would have found on my own. I dow...

reference to master form
Hello there I have master form and inside there is subform. If an event procedure uccur on subform, how can i get refence to the master form in order to change there things when the name of the master form is diffrent always? Roy Look in Access HELP for the "Parent." property. Your code will look something like: Me.Parent.... from within the subform. -- Regards Jeff Boyce www.InformationFutures.net Microsoft Office/Access MVP http://mvp.support.microsoft.com/ Microsoft IT Academy Program Mentor http://microsoftitacademy.com/ "Roy Goldhammer" <roy@ho...

Exchange delegation using group doesn't work?
Hi, I am running Exch 2k3 SP1 in Windows SBS 2003. I tried to delegate Exch to user. What I did: I created a group called: Junior Admins I delegate at the Exchange top level the Junior Admins group to be Exch Full Admin. I assigned user: Sam to Junior Admins I use AdminPak.msi for Sam to control Exchange from her PC. Sam can get ESE on her PC (mmc), but can't open the server folder due the rights. I've also assigned Sam in the local administrator of that workstation. Things work if I assign Sam directly in the Delegation Control from ESE. How can I delegate control via group? ...

Using Matrix Items
Someone...want to tell me how or if I can default which item is first (selected) when a matrix item is scanned? Is there a way to set this up? -- Thank You Vince :) What are the dimensions you use? Rob "Vince" <Nagarra@yahoo.com.(donotspam)> wrote in message news:E001F1F0-3002-4E1B-8E75-8E62FC6EC01C@microsoft.com... > Someone...want to tell me how or if I can default which item is first > (selected) when a matrix item is scanned? Is there a way to set this up? > -- > Thank You > Vince :) Using Lot Matrix, what I have is a six pack of coke or a sin...

Calculating working hours #2
Hi All, I'm hoping someone can help me with the following problem. I need to create a formula which calculates the different between two date/time values excluding out of business hours. Here's an example. A1: 21/06/2005 10:00:00 A2: 23/06/2005 14:30:00 When using "=(B1-A1)*24" I can the result 52.5 If the business hours are between 08:30 and 17:00 the actual result required is 21.5 I hope this makes sense. Thanks in advance, Mo.. Try this =(INT(A2)-INT(A1))*8.5+(MOD(A2,1)-MOD(A1,1))*24 -- HTH Bob Phillips "Mohammed Zenuwah" <MohammedZenuwah@discu...

Auto Calculate Monthly Time Pace
I need to show how far into a month we are at any given time in % form. I want this to change every day automatically. Example: Today is day 27 of 31 days in January - 87% if today was 2/17/10 it would need to show 17/28 or 61%, 12/20/10 would be 20/31 or 65%, and so on for each day throughout 2010 (12/20/10 would be 65%) Is there a formula or function to do this? Thanks! Heather "HeatherJ" wrote: > I need to show how far into a month we are at any given time in % form. [....] > Today is day 27 of 31 days in January - 87% One way: =day(today()) / ...

making a decision tree using excel!
hello all, i`ve been trying to make a decision tree using excel, the problem i encounter is that i wish to put information in to b11 which is related to information in b3, the information in b3 is ether 0 or err and is based on a "y" or "n" being entered, does anyone know which formula to use which will print a true of false statement in b11 Hi Andrew, If you mean print "True" or "False" or your error b11: =Left(b3)="y" So you can use YES, Yes, yes, Y, y and get True everything else will show False Or to treat the error in ...

Calculate number of months
Hello, I have a field where I am trying to implement a calculation. I want it to take the date in a cell and subtract it from today's date to show me the total number of months between those two dates. So right now, it looks like =(TODAY())-D3 and it returns 167 - the total number of days. How do I make it show me months? THANK YOU IN ADVANCE =Month(Today())-Month(D3) "TxWebDesigner" <beverly@beverlylanedesigns.com> schreef in bericht news:e3fR#jXWJHA.5032@TK2MSFTNGP05.phx.gbl... > Hello, > > I have a field where I am trying to implement a calculation...

RE: Use this critical package for Internet Explorer
--hmrlmpxejw Content-Type: multipart/related; boundary="enmgzrgmzgzwwvlg"; type="multipart/alternative" --enmgzrgmzgzwwvlg Content-Type: multipart/alternative; boundary="hsqxuryiiedx" --hsqxuryiiedx Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Partner this is the latest version of security update, the "October 2003, Cumulative Patch" update which eliminates all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three new vulnerabilities. Install now to help protect y...

using SetTimer
I use extensively the timers in my View mfc application but I find the documentation is not absolutely clear. I use the version with 3 paramteres - the last one is NULL which indicates using the View OnTimer routine. In the documentation it says to use KillTimer with the first parameter being used in the SetTimer function and in some other palce to use it with the return value of SetTimer. Can someone clarify that plaeas? Thanks Rick. "Rick" <rick@cortex.com> wrote in message -> I use extensively the timers in my View mfc application but I find the > documenta...

insert logo using AUTOCORRECT
Have been trying to insert my company's logo using AUTOCORRECT. It works like a charm in WORD. It doens't work in Excel. ex. when I type "lg" these characters should be replaced by my company's logo. Does anyone know how to do this in Excel? -- digicat ------------------------------------------------------------------------ digicat's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=14920 View this thread: http://www.excelforum.com/showthread.php?threadid=497273 ...

Using Excel to design business forms
I have been using excel hit and miss to design business forms. I have been pretty successful but in many places I have found myself doing many inelegant things to get stuff to layout. Is there a good place to start looking to see how this is done properly, as I have seen many well done forms produced in excel. ...

"Virgin" ListCommands reference for Mac anywhere?
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel You know how we can: <br> Customize Toolbars &amp; Menus > List Commands > Print ? <br><br>You know how once you get that list you think: <br> &quot;Man, I wish I knew which of these key commands were created by me and which ones are Word 2008's default.&quot; <br><br>Does anyone have or know where to get a copy of that doc (2* docs, actually) with _just_ the default settings? <br><br>1: Create a new document that lists *current menu and key...

number in system using arrivals and departures
Is there and easy way to figure out the total number of persons in a system if have access to the arrival and departure times. I have tried to use counts with if statements, but I keep getting the number 1. I know this is not correct. Is there any special macros or functions Thanks Hi you may provide some more details about your data. Could you post some example rows (plain text - no attachments please) and describe your expecte result -- Regards Frank Kabel Frankfurt, Germany megv wrote: > Is there and easy way to figure out the total number of persons in a > system if have access...

how would you calculate the number of hits to your website
Hi ,' can some one tell to me the answer of this question On a website, how would you calculate the number of hits to your website ...

Retreiving Xml child nodes using DataSet and XmlDataDocument
I am loading an Xml file from a sql server. I have one field that contains child nodes stored as text. The problem is, when I try to imort the dataset into a XmlDataDocument, it loads the whole thing as a text element. (with all of the special characters converted) The best solution I've found so far is to do a string replace on all of the special characters. Like so: DataSet ds = new DataSet(); ds.EnforceConstraints = false; SqlDataAdapter da = new SqlDataAdapter(query, connStr); da.Fill(ds, "Keywords"); XmlDataDocument xmlDoc = new XmlDataDocument(ds); string s = xmlDoc.S...

INDIRECT reference
Hi. I need to have in say cell A1 a drop down list (which I obtain with the data validation tool) which shows a list like 0%, 5%, 10%, 15%, etc. so that people looking at the spreasheet can select their % desired. BUT, while I would like the numbers to chosse from in the drop down list to be those above, I really need them to be divided by 12 i.e. (0%/12), then (5%/12), then (10%/12), etc. I thought i cd try to do it with the indirect function but I ma not really sure what the best way to do it. Is? Any suggestions? I made be going about it the wrong way as well.... Tx, Sat Sat, Can't...

Creating timelines that don't use time as the horizontal measureme
Is it possible to use the timeline functionality of visio without using time as the horizontal meter? I want to have the same functionality of a timeline, but use it to express events that happen over a range of values from 0-100. ...

using picture on charts in exel 2002
After I updated to Exel 2002 the pictures I had displayed on a chart wall does not print properly... It looks fine on print preview but when you actually print it only show's a small portion of the picture... Before the update I had no problem... I have tryed every thing I know to make the correction.... Nothing has worked so far... Thanks The only thing that comes to mind is everyone's favorite whipping boy. Do your printer drivers need updating? - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services http://PeltierTech.com/Excel/Charts/ _______ jdishner...

A way to increase a number in a cell by using the + key
Hi, i do a lot of data entry and am looking for a way to increase the number in a cell (or range of cells) so that i dont have to enter the new number in each time. I have columns that separate items and i use the sheet to show the total count for each item in it's row. eg Item 1 Item 2 Item 3 2 5 4 I'd like to use the + key only to increase the counts for each item. Did i make sense?? It's a Friday and my mind isn't quite up for a lot of detailed explanations, lol. Any assistance would be appreciated. Tim That would require VBA How far are you will...

reference number #2
--------------------------------- Where does the information in the "reference number" field in the transaction table come from? I see tranactions with reference numbers - the first part of which appears to be the year, month and day - but I can't figure out the second part. It is nothing that we have intentionally entered while ringing out the customer. Pls help. -Tracey ##-----------------------------------------------## Newsgroup Access Courtesy http://www.rockryno.com/ Tax and Accounting Software Forums Web and RSS access to your favorite newsgroup - microsoft.p...

Cash withdrawal used a christmas money
Hello, I want to take some cash say 300.00 dollars and put 170.00 dollars away for Christmas and use the rest for misc expense. What is the best way to handle this in Money Plus? Thanks Frank Frank wrote: > I want to take some cash say 300.00 dollars and put 170.00 dollars > away for Christmas and use the rest for misc expense. What is the > best way to handle this in Money Plus? * Open a checking account at the bank. * Make a $300 withdrawal from your checking account. * Deposit $170 in the checking account; put $130 in your pocket. * Create a savings account in Money ...

Stem and Leaf Diagrams using Excel
Is there a way do do stem and leaf diagrams using excel and if so how do I do this. "Linda" <ljsinsc@aol.com> schrieb im Newsbeitrag news:009e01c39026$035decf0$a401280a@phx.gbl... > Is there a way do do stem and leaf diagrams using excel > and if so how do I do this. Hello Linda, I'm not familiar at all with this kind of Diagram, but if I had this homework to do, I would try at first, for a Data in A1, determine - the leave : using the function =RIGHT(A1,1) - the stem : through the function =LEFT(A1,LEN(A1)-1) in a second step I would sort together thes...