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? Of course, you can be careful when eating donuts. But careful donuts sure sounds like a paradox. ![]() I've just explored this on the phone with Microsoft support. It turns out that OS 10.8, at least, doesn't like slashes in file names. Changing them to dashes solved the problem. 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? How to setup wifi on hp laserjet pro 400 youtube with usb port for mac. 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(Query_Actual!K3/'No of proposals_Actual'!K3),'0',Query_Actual!K3/'No of proposals_Actual'!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.
0 Comments
Leave a Reply. |