Formula Shows Up As A 0 (circular Reference For Mac

Posted on

317 comments Once in a while everyone is bound to come across this problem. You type a formula in a cell, then you press ENTER. Nothing happens. You check if a donut chunk went in to the key board and some how jammed the ENTER key. So press it again, this time harder. Excel formula showing as text instead of actual result, like this: Now what to do?

Formula shows up as a 0 (circular reference for mac pro

Of course, you can be careful when eating donuts. But careful donuts sure sounds like a paradox.

So instead lets roll up our sleeves and find out the reason for this mishap. The top reason for Excel formula showing as text: You may have accidentally pressed CTRL+` (back quote symbol, the key below escape key in your keyboard) or activated the “Show Formulas” mode in Excel.

When you do it, excel shows the formulas instead of their results. To fix this error and get back the values (or results) just press CTRL+` again or click on the “Show formulas button” The next reason why formulas are shown as formulas: You may have set the cell formatting to “Text” and then typed the formula in it. When you set the cell formatting to “Text”, Excel treats the formula as text and shows it instead of evaluating it. To fix this error, just select the cell, set its formatting to “General”. Now edit the formula and press enter. (Alternatively you can press F2 and then Enter after setting format to General).

The less probable reason why formulas are shown as formulas instead of values: You may have accidentally typed a single quote ‘ before the = sign in the formula. When you type single quote ‘ in a cell excel treats the cell contents as text and does not evaluate any formulas within. To fix this error, just remove the single quote. What is your experience with excel formula errors?

The very first time I pressed CTRL+` by accident, it nearly freaked me out. All the columns became too wide and the formatting went for a toss. Everything looked weird.

It took me a while to figure out that I accidentally pressed the Show Formulas shortcut (CTRL+`). I felt huge relief when I got the results back. What about you? Did the formula error ever freaked you out? What other things about formulas worry you? Share using comments. More on Formula Debugging:.

I am only having issues with one spreadsheet. After copying and pasting a chart into another sheet and eliminating past month's data. Formulas stopped working. I was only getting the formula in the space after hitting enter. I followed the directions here and got everything set to 'general', redid my formula, hit enter. And now it always says 0 instead. I went into a new spreadsheet and tried out an example of what I have been trying to do and the equation worked perfectly.

So I am not entering the formula incorrectly. What else could it be? The global find & replace (Ctrl+ H) of = with = is truly BRILLIANT!!!

Thanks to Sebastian and Valeria for this! The problem happens to me when I concatenate multiple source cells into long formulas to build complicated lookup-up tables and other worksheets where it's much easier to build dozens of complex formulas as text strings and then copy their values into the working sheet; doing the global search & replace of = for = char(61) 'activates' all the formulas, which otherwise sit there in perfect syntax but recognized only as 'constants' by Excel.

My old method was to 'F2' & ENTER in every cell, one at a time; Ugh! Thanks, again!! I have experienced the following weird behavior with SUM function.

Imagine you have the following values on the column A 1 1 And you use the formula =SUM(A1:A2) And the result you get is 1 instead of 2. Then you use the following formula: =A1+A2 and you get 2 I figured out that one of the cells contained the number stored as text. I also figured out that the SUM function doesn't convert such numbers to text as the plain formula do.

Try it by yourself. I wrote an article that summarizes the problems of the Excel Sum formula, it may help you to avoid those errors and more in the future. @Chandoo: I'm using an IF formula =ROUND(IF(AG3=0,0,((AI3/AF3).100)),2). Cell AG3 has =IF(ISERROR(QueryActual!K3/'No of proposalsActual'!K3),'0',QueryActual!K3/'No of proposalsActual'!K3) formula in it. However, even if AG3 is 0, excel reads it as 'FALSE' and does the division!

One more thing. The same formula works for other cells with only a 0 in it. But when the above said formula (in AG3) is referred it does not work.

I'm sure this is some excel error because sometime back I had used the same formula and it used to work perfectly. It was in Excel 2007 and now I'm using 2010. Please help me with this. I am using a large spread sheet converted from an older excel program. I am calculating in 3 columns.

Sometimes one of the two columns will have the same calculation in it and I noticed that the answer is different. I checked the cell formulas and they are the same. I have tried to cut and paste and it won't over ride what is in the cell and still calculates incorrectly. I was just upgraded to 2010 so am struggleing with the new options. What could this be? I checked and 'set percision as displayed is NOT checked'.

In Excel 2007 my formulas are suddenly copying as values, not as appropriately offset-adjusted formulas. In other words, copying is now behaving just as if I if I had done the corresponding 'Paste Special' and selected 'Values', but I am using simple 'Copy' + 'Paste' (or, with the same wrong results, CTRL+c and CTRL+v). This failure now occurs in entirely new spreadsheets (new workbooks, not just new worksheets), and not just in the worksheets where formula pasting was previously working normally. My next move is to OOCalc and/or to other machines, but I would appreciate any help.

(I'm a programmer with 40+ years' experience, and this is going to bug me maybe that's where the term 'bug' comes from? till I see it fixed.) Thanks in advance. Supplemental re 'In Excel 2007 my formulas are suddenly copying as values.' I just noticed that if I do try to use “Paste Special”, now all the numerous usual choices are missing and I get the entirely different window on which the only choices are: 'HTML' 'Unicode Text' 'Text' -or- (when I just tried that again), just 'Unicode Text' 'Text' GROWL. I hope someone will point out that I'm just doing something entirely stupid (rather than discovering that my machine is now haunted by a particularly devious virus).

Excel 97-2003 to Excel 2010 migration problem: Hi all! I'm sure that many of you has allready experienced the following problem: I have developped an application in Excel 97-2003 under windows XP using workbooks with a lot of formulas linking many of the workbooks.

I'm now trying to migrate the application, thus the workbooks from Excel 97-2003 to Excel 2010 under windows 7. The procedure I'm using is to load the workbooks under Excel 2010 and 'save as' them using the format option Excel 97-2003. But when I open the workbooks with the biggest number of formulas and links, I get the following message box and excel terminetes when I click on the single button Close the Program appearing on the window: 'Microsoft Excel has stoped running A problem caused the stop of the program. Windows will close this program an tell you if a solution is avialable' Do some of you have an idea how to solve my problem? Thanks in advance for your suggestions.

Hi again, Got an interesting question. In a nut shell. I've extracted data from the web into a worksheet.

I had to sort the data using formulas to extract things like the 'mill' out of '125 mill' among others because it would be infeasible to manually do so. Then, I used a rather large offset function to extract certain type of values from my modified data like 'Total Sales', 'Yield', etc. That works fine, but when I create a chart from the offset function values nothing comes up.

Is it at all possible to extract data into a chart from two separate formulas? Side note: There is all kinds of different data formats like percent, dollars, and regular numbers.

Greatly appreciate any help. Hi There I consider myself a pretty good excel user and I have come across an issue that I can't figure out. I am linking 2 different files (both 2007) and the workbook that I am trying to establish a link does not recognize the 'linkee' worksheet as something to link to.

Sorry, hard to explain. I simply want to have the contents of one cell link to another cell in a different work book. However, when I add the '+' and go to link, it doesn't recognize the cell as anything it can link to.

I have tried changing all the cell format, the 'allow link updates' in the security centre and have tried linking different cells and workbooks. Everything works as expected except for one source spreadsheet so that tells me its something to do with that spreadsheet. Any advice would be great. Hi, need help. I put in a password using the general options while saving my work sheet and after that i've noticed that the formulas on my worksheet is no longer working, I have a formula to compute average handling time and the sum and the average is not computing and that i need to manuall click on the function menu to compute each and is time consuming. I am using excel 2003. I am not sure if putting a password affected my work sheet or what, but I need to it to be back up and working the same way as it was.

I'm facing a issue- when I open a particular file I only see a #Value error (cells in this file are linked to other files which are not on my system). However, when my peers open the file they see the calculated values (without error). I'm using 2010, my peers are using 2003/2007. If I set the calculation option to manual, I too am able to see the value correctly (however, others do not need to do this).

What's different in 2010 thats causing this? Can this be fixed? Appreciate any help!

@Hui, We (me and my colleagues) do not have access to the network location of the linked files- We are an Analytics team while the file comes from Finance, there's no way we can access those systems:). Still, I do used to see the proper values in the file earlier. If I open the old files now it shows an error, however, I've used these files earlier without any issues. Also tried opening the files in other 'MSO 2010' systems, it does show an error, while anyone using 2003/2007 doesn't see these errors. Another thing, this started happening only in the last few days. All was working fine until, say, Nov end.

I've checked the 'trust center' settings and everything looks normal. I wonder if any recent update to Office 2010 is causing this. Thanks, Kunal. Hi All, I was having issues with a Summary Report designed to compile individual summaries from a variety of scopes in one large project. I linked all the individual summary sheets into the Summary Report, placing each as its own table onto one sheet. Everything looked great until I tried to tally all the individual costing into an overall project total - only one of the sheets' values totalled correctly!!

After much frustration and trying all of the examples listed here, I discovered a simple proble - the import turned all of the '$' signs from a text formatted condition into an actual cell entry. This cancelled out the number when I tried to SUM the values. I performed a simple 'Find/Replace' command with nothing entered into the 'Replace' field to get rid of all these symbols and all of my calculations work as they should. I didn't see this posted anywhere else on here, so thought I would add my discovery to this wealth of information. Hi Guys, I need Help for excel 2010.

I am importing some data from our official website. It is daily analysis sheet of our store. Its shows all figures are in $ sign. By the way this data is get update in every 1 min as our store is busy. Now when i import data on my excel sheet it shows figure with $ sign ( $ 421.20). But when I want to add two cell data (=A1+A2) it shows '# value Error'. As i tried to change the format from 'General' to 'Currency' but as data get updated every 1 min that format get reset.

This problem just occurs in my laptop. On my office desktop (Office 2010) its work fine. On my colleague's laptop (Office 2003) its works fine. Please help me.

Everything on the sheet is fine. The next order of business is create a new sheet with formulas. Sure ok no problem; I double-click on the column divisions to make everything fit and look pretty. My dilemma: Before turning any work, I like to look at everything in preview. When in preview mode, not only is the formula showing but the file name appears before and after the formula.

Ugg what happened, I have never seen these included in the formula and cannot figure out how to reduce it to the formula alone and hence, fit on the sheet. Any suggestions? Formula in non preview mode: =COUNTIFS(Table1Bedrooms,5,Table1Baths,'=4') formula in preview mode (of course I cannot copy it.) =COUNTIFS('ExcelLA8-31myname.xlsx'!Table1Bedrooms,5,Table1Baths,'=4,'ExcelLA8-31myname.xlsx'!). Hi, I am having a similar problem re formulas entering #value!

Error after copying data from an HTML page and converting text to columns. I notice that the columns containing the numbers have 'space' before and after the number and only if I manually go into each of the cells and remove this space then will it be recognised as a number. Also tried to do find & replace to remove the space, but wasnt recognised.

Any ideas on the best way to solve this problem. Also, what would be the best way to convert an HTML table to Excel? Hi ya, CAN ANYONE HELP PLEASE?!? I have a basic statement that isn't working: =IF(G21=$M$12, 'Yes', 'No') This is in cell CI21 M12 is an absolute cell while the column G reference will need to change within each row G21 is populated by a VB.Value = ' M12 is a Validation list All cells are set to 'General' - the Show Formulas is off - and the Automatic calculation is on But it still doesn't update when the value of M12 is altered from the list?

I have tried typing long hand with more brackets, copying another IF that does update and amending the cell references, switching the order to M12=G21, creating the formula in a 'nearer' cell (incase it was a capacity issue) and checked that all three cells are comunicating with =SUM(G21-M12). Which does work and display the result. I've also tried physically selecting the cells rather than typing their references in the formula - but nothing has worked.

PLEASE HELP!! I tried so many things, made sure the cell was not set to text, copied the formula from another cell where it worked, thought I tried everything for the last couple of hours and then found your site. There was not a ` before the = sign, just a space. I backspaced in the formula bar to remove it, and the whole thing works again, yeah!! I can't figure out how it was adding the space, but that tiny space threw a money wrench into the formula big time. Thank you for helping me not pull my hair out (at least for today!), and for reminding me that most errors are operater errors and fixable.

I have an issue I could use help with. I have a master spreadsheet that I have transferred information from several other spreadsheets into using a VLOOKUP formula. If a cell was blank with no data to copy, I had the formula return '. I then pasted the values from the lookup data in column AJ into column C.

The issue is that I have a LEN formula that points to column C and if it is blank, it is supposed to point to column A. However, for any blank cells in column C it is returning 0 without moving on to A. If I hit F2 in column C, the formula works, but I have 23K rows of data and none of the other fixes I've seen here have worked.

Any ideas on how to get them to convert without going line by line? I use Excel Mac 2011: in a table where all the formulas are two numbers multiplied together from two other tables, I have a formatting glitch where the format of some cells (but not others) cannot be changed / format seems to be locked - I want 187.2 to display as 187, but I cannot change it from 187.2 (cannot make it currency or any other format - the format is truly locked). Yet neighboring cells behave as they should. Re-pasting the formulas has no effect. The sheet is not protected, although all cells show as 'locked'. The cell contents are not locked, I can change freely. However, if its a number, it will only show one format / one decimal point.

I have a question. I am new to excel 2007. I volunteer at a school and I am making a new report card in Excel 2007.

I have the spreadsheet done. Rockets bounce back ready for mac download. Now I have used the formula=average(H14,AB14)to get an average. The problem is in the other cells it is giving a rounded number( I want the rounded number) but It is giving me an incorrect number due to the number in format cells function is not rounded.

So my final average is not correct. How do you get excel to calculate by the rounded numbers? I hope I have explained my problem well enough.

I'm hoping someone can help me. First, I found this website a few months back when I was having the problem that most people are. Had hit the hotkey combo to show formulas and this page cleared it up for me. But today and yesterday I've had a very very strange issue. I must have hit the same hot-key combo, but what happens is just a SINGLE CELL is showing the formula of what I type in ANY other cell. As soon as I start another formula, it starts showing the new formula in that cell.

I can delete the cells but it just keeps showing in that same place. Reformatting it does nothing. The standard stuff I've been reading does nothing. It's just one cell which shows the formula I'm typing into OTHER cells. Very frustrating! I NEED HELP, PLEASE!!! My excel formulas SUDDENLY went gaga and I have been sweating in the last 6 hours ever after!

When you highlight a column to SUM, instead of giving result in the cell immediately after the last number to be summed, IT GIVES ME FORMULA ON THE CELL OF THE LAST NUMBER TO BE SUMMED. If I re-do and run through the routine again, IT DOES SAME THING BUT THIS TIME, IT GIVES FORMULA ON THE FIRST CELL OF THE COLUMN TO BE SUMMED and on and on it keeps rotating. I have tried everything I read here BUT TO NO AVAIL! We can Skype if need be, here’s my Skype ID: schrodingerr. Hi, One of my users is using Excel 2010. He uses Platts formula to extract market data, which requires Platts plug-in. However, I noticed something weird, when the same file is open on a laptop without the plug-in, the formulas keep showing the result, instead of error.

Formula Shows Up As A 0 (circular Reference For Machine

I tried evaluating the formulas, it actually evaluated an error (#NAME?) but somehow it continued to show the result (correct result). As if, the excel refuses to show the error. Opened the same file on Excel 2016, this time the formula errors are all over the place (which is the correct behavior). Anyone could explain this? Can anyone help with this problem. I have an excel worksheet made to calculate Payment in Lieu of Taxes.

IT works with tax rates and percents. I have it all working properly except the last column. To get the% due cell I entered a formula and it gives me the resulting percent in the correct cell. (example: d3/d4 and that goes into cell d5 which is my%due cell) Then I needed to calculate the $ due in another cell.

Formula Shows Up As A 0 (circular Reference For Mac)

So I tried to add a formula to take the total $ amount from one cell (let's say cell e6)and multiply it by the%due cell (D5).the amount is wrong. I think it is a problem with me already having a formula to calculate the% due. Then when I use that cell in my next formula it gives me a wrong total. Like just a few dollars off.

(example: e6.d5) My numbers are.5904 cell d3; 5.6111 in cell d4 and that should equal about 11% to go into cell d5. Then I have $2591.56 in cell e6 and multiply it by d5 (11%) and should get: 285.07 but it keeps giving me 272.68 instead. Sorry this is so long, not quite sure how to explain it.