HomeОбразованиеRelated VideosMore From: Subjectmoney

Excel Tutorial Circular Reference & Iterations - How to Fix Circular Reference Formulas 2016 2013

303 ratings | 81132 views
http://www.excelfornoobs.com http://www.excelfornoobs.com/exceltutorial7.php?title=Excel%20For%20Noobs%20Tutorial:%20Circular%20Reference%20and%20Enabling%20Iterations%20to%20Fix%20Circular%20Reference%20Formulas Excel 2013 Tutorial for Beginners on How to Fix circular reference problems in Excel. A circular reference error arises when a formula either refers to the cell it is inserted in or refers to another cell that refers to its own cell. For example, if we were to enter =A1+B1+C1 into cell C1 we would get a circular reference warning. This is because we entered a formula into cell C1 that refers to cell C1 as an argument. The problem with formulas that contain circular references is that every time the formula is calculated, it must be calculated again. This could go on infinitely. When you enter a formula with a circular reference a warning box will appear as shown below. Circular Reference Excel Formulas 2013 When this box appears you can click OK and Excel will just display a 0 as the result of your formula, or you can click Help and Excel will display a help screen providing more information about circular references. Circular Reference Help Excel 2013 There are times when circular references are necessary. For example, suppose you owned a company and your employees' salaries were a percentage of net profit. Since net profit is calculated as Revenues -- Expenses and salaries are an expense, we would run into a circular reference problem. We must know what net profits are before we can calculate salaries and we must know salaries before we can calculate net profits. In this case we can use circular references to our advantage. Examine the data and formulas in the Excel screenshot provided below. I am using the show formulas feature so you can see what exactly is entered into the cells. As you can see we have a circular reference problem because Net Profits in cell B4 is =B1-B2-B3 but cell B3 contains a formula that refers to cell B4. Excel 2013 Formulas circular reference iterations The formulas entered into this worksheet are resolvable formulas and if the formula is repeated enough times a desired result can be reached for both cell B3 and cell B4. To solve this circular reference problem we simply need to enable iterative calculations. To enable iterative calculations follow the steps below: Step 1: Select File and then click on Options. The Excel options dialog box will appear. Step 2: In the Excel options dialog box click the Formulas tab. Step 3: In the Calculation Options group check the box where it says Enable iterative calculation. Now Excel will run the maximum allowed iterations until it reaches a result where Salaries are exactly 10% of Net Profit. Look at the screenshot below to see the final result. https://www.youtube.com/user/Subjectmoney https://www.youtube.com/watch?v=j0nRTcoKrE0 http://www.roofstampa.com hjttp://roofstampa.com http:/www.subjectmoney.com http://www.excelfornoobs.com Microsoft Excel 2016, Excel 2016, Excel 2016 Tutorial, Microsoft Excel 2016 Tutorial
Html code for embedding videos on your blog
Text Comments (48)
Karen Dolan (6 days ago)
this was really helpful
katy Escobar (8 days ago)
Thank you!!!!!
Abdullah Alemran (16 days ago)
Thank you
Chill Watch (3 months ago)
interacting in 2018
Yu Tong Jason Huang (4 months ago)
Simple and reasonable.
zohra akter shima (7 months ago)
That's really helpful
PIZZA MAX TARIQ ROAD (8 months ago)
THANKS its very helpfull
Lindsey Swicegood (10 months ago)
Thank YOU!! I've been breaking my brain for hours. This was so simple
Denise Cardona (10 months ago)
Thanks for the easiest explanation I could find.
Lukman Arif (1 year ago)
Thank you for sharing tutorial on this topic. I have a problem with its consistency. I have such circular reference formula. It indeed works well upon my setting on iteration of 1. But when the worksheet is saved, then reopened, it gives warning of circular reference. I find it disturbing as one may get it wrong when receiving the file. Also, it seems to be inconsistent as I have to re-apply/activate for its iteration on the Option menu to make it work upon re-opening for new entry. Do you or anyone have solution for this issue? Thanks a lot in advance!
Todd Gaetke (1 year ago)
Thanks for the clear and concise explanation of something I've ignored for years. Eazy-peazy
K2anuckles2 (1 year ago)
Thank you. Helped a lot. Sincerely Newbie Friend
Patryk Sołtysik (1 year ago)
but you can easily solve it with simple equation: 1500-600-0.1*x=x 1.1x=900 x=818.18
fahad1992fah (1 year ago)
Kamille Fullmer (1 year ago)
Thank you!
Di Davey Fan (1 year ago)
Giovanni Azael (2 years ago)
What if it doesn't allow the file to open?
Windy Martdianzah (2 years ago)
got the same situation and watch your video. SOLVED !! thanks
Marta Codeço (2 years ago)
Thank you for sharing
ARMANDO CAMPOS (2 years ago)
Thx for sharing. It just helped me on My Num Tools HW.
Russell Deming (2 years ago)
The exact help I was looking for. Thank you for sharing!
Chad Hugo (2 years ago)
Very HELPFUL! Saved me work so much!
Sivan Grinberg (2 years ago)
Thanks a lot, Great video!!
Martin Williamson (2 years ago)
sarah Al-Megren (2 years ago)
If for example I have a value in cell A1, lets say 2 What I want to do is multiply that cell by 0.75 but in the same cell I don't want to have to put a formula in B1 such as =(A1*0.75), because I already have a value in B1 and I don't want to have to insert a new column. if I highlight cell A1 and put the formula =(A1*1.333) into it the value becomes 0.00 Can what I'm asking be done? or would I need to create a macro? Any help would be appreciated. Thanks
Curtis Chu (2 years ago)
So embarrassed that I forgot about how to solve this. Thank you for the video!
Laura Newill (2 years ago)
Thanks!!   I have been dealing with that error message for ages, not knowing what it was.
Adriaan Fick (2 years ago)
Kerine Davis (3 years ago)
great job
Jarrett Welch (3 years ago)
Thanks for the help. This saved me hours.
Jalla Bajalla (3 years ago)
Thank you so much saved me hours of work!!!!
Bong Romulus Rosales (3 years ago)
Thank you, great information
Margie M Barbee (3 years ago)
Thank you very much for explaining this, becasue I had so many problems with it. Very informative.
Swayam Prakash (3 years ago)
Thank you for the Excel cir. reference tips, it is very informative.....
Michael Musha'sha (3 years ago)
Thanks for that!
Manohar Raj (3 years ago)
Thank you so much this video help me so much .
Constance Jackson (3 years ago)
Thank you.  This tutorial was very helpful.  Perfect solution.
Marcielle Schultis (3 years ago)
Reading about iteration and circular references just wasn't clicking for me, thank you for the visual.
Rich House (3 years ago)
In case people want a non-iterative solution you can substitute salary for profit like this. Profit = Rev - Expenses - 0.1Profit  1.1Profit = Rev - Expenses  Profit = (Rev - Expences) / 1.1 . For this example B4 would be =(B1 + B2) / 1.1 and B3 remains the same without circular issues. Saves you having settings issues and no need to worry about accuracy. 
jun kuroz (4 years ago)
Thanks dude ^__^
Lhara Mae (4 years ago)
kflo54 (4 years ago)
Helpful, I appreciate it.
IvCastilla (4 years ago)
:) Thank you, I was thinking is not possible make calculation ussing circular references. IvCastilla
Ryan Crosson (4 years ago)
Thank you sir, this was driving me crazy! That was a quick fix.
Eman A. (4 years ago)
I have a question. I have two workbooks. Workbook 1:  iterative calculation (enabled) Workbook 2: iterative calculation (disabled) Each time i open Workbook 2 first then later i open Workbook 1 next, the setting for workbook 1 will change and becomes disabled. So i get the error message all the time and my formulas wont work. But if i keep Workbook 2 open first and open other workbooks next, there's no problem at all. Please help me on this because there is not point of changing the settings all the time whenever i am i a situation like this. Thanks.
N Roma (4 years ago)
Can't thank you enough, no one else could explain this as you did.
MishMash (4 years ago)
Thanks, Very helpful... I think Microsoft had thought of everything :)
Ahmed Abdul Waheed (5 years ago)
Hey i have a problem..If i change the value in ANY cell it runs the calculations again..changing the value ..for example i have a formula in cell A1 that is =A1+A2...if i change the value in A3 or B1 (any other cell)..it runs the formula again of A1+A2 changing the value of A1 ...how can i fix that?

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.