I am updating a workbook a colleague had handed off to me. There are several sections that contain formulas that pull data from other cells (some of these are simple calculations that sum two cells, others are more complicated formulas). These formulas appeared to be working until I double clicked one formula to view its inputs, then hit enter. The result, which was a value >0 before, turned to 0.
This occurs throughout the time series.
I tried F2 + ctrl/shift/enter, but I still get 0's as a "new" result, even though the inputs have not changed.
Any ideas?
Thanks in advance for your help!
What's the formula. Is it pointing to another sheet, linked to another workbook? Do they have the needed values.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Maybe you have calculation set as manual
Regards
tom1977
If You are satisfied with my solution click the small star icon on the left to say thanks.
Some thoughts,
1. Try: Hit F9 to recalculate.
2. Select the cell which is giving you 0, On "Formula Tab" click "Trace Precedents" see if the arrows go to the right cells.
3. Make sure your precedent cells are actually numbers and not text.
????
ChemistB
My 2¢
Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)
@NBVC- this happens across 5 or 6 rows in one section of the workbook... one equation is:
=IF(H535<0, 'Ind Retirement'!$D$5*(H530)+H535, 'Ind Retirement'!$D$5*(H530-I533-SUM($H537:H537)))
another formula is:
=I530-SUM($H537:I537)
where I530 =168.88 and H537+I537 = 0. the result of this sum is 0, where it should be 168.88
@ChemistB
1) no luck
2) the precedent cells are the correct cells that should be going into the problem cell
3) all values are either percentages or numbers
....
Not easy to tell what's going on....
Can you attach the workbook or something that shows same problem?
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Sample problem is attached- highlighted cells are the "problem" cells
The formulas are involved in a Circular Reference
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
I've followed Excel's "circular reference" ID arrows by evaluating the formulas involved, and was unable to determine where the circular reference has come to be
Does anything pop out at you?
I am not sure that correct but look at attachment
Regards
tom1977
If You are satisfied with my solution click the small star icon on the left to say thanks.
As per the link I showed, you can go to Office Button, click Excel Options, select Formulas and then check Enable iterative calculation.
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
@NBVC
that worked! thank you so much!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks