Avoid a Circular Reference

Experts,

I have a field colored red in the attached.
I have a circular reference if use the formula N14-P14.

I am thinking there is someway to avoid the circular reference by modifying the formula?

thank you test.xlsx
pdvsaProject financeAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pdvsaProject financeAuthor Commented:
screenshot
0
Rob HensonFinance AnalystCommented:
The only way to avoid a circular reference (CR) in the formula is for column P to be a hard-coded value.

However, you can change your settings to allow CR.

Excel 2003 = Tools > Options > Calculation - Enable Iteration.

Not sure about the path for the same in 2007.

Thanks
Rob H
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gowflowCommented:
what do you mean is there a way to avoid ? formula in P is depending on N and N is depending on P so it create obviously a circular reference.

Question in P can you get a formula not depending of N ?
gowflow
0
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

pdvsaProject financeAuthor Commented:
There is a way to store the value of N-P to avoid a CR.   I think I have seen this before.  I think this was on another website Mr Excel.  My memory is a little fuzzy about it though.  
0
gowflowCommented:
well one way to fix it is using vba I don't know if this suits you ?
gowflow
test.xlsm
0
gowflowCommented:
pdvsa did you check the file I posted ? would this solution be feasible to you ? You only need to carry over the formula in Col O and the code will take care of Col P.
gowflow
0
pdvsaProject financeAuthor Commented:
gowflow...thank you.  I had a very busy day yesterday.  It wasnt exactly what I was looking for but I am sure it will do the trick.  the issue is that it is saved as .xlsm and i want to send out to people and I know there is security issues with macros in excel and if they dont enable then the code might not work from what I remember.  Is that true?

thank you
0
Rob HensonFinance AnalystCommented:
Any code would require macros to be enabled before they work.

The xl2007 route for the iteration that I mentioned is:

Windows button > Excel Options > Formulas > Enable iterative calculation.

Thanks
Rob H
0
pdvsaProject financeAuthor Commented:
thank you.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.