Does anyone know why excel converts my formulas to values when I am in number format and my formula starts with "+" instead of the typical "=" (and how to turn it off)?
Formula Example: +7+5 shows up in the cell as a value of 12 and not a formula anymore. I want to keep the formula. If my cell format is "General" it works as I want it to and doesn't convert to value.
Note: it keeps the formula if i use +A7+A8, but not when I use numeric values for the calculation.
Try pressing
Ctrl+` (the key to the left of 1)
Regards
Special-K
Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
Thank you Special K, but what it seems you are referring to is a display issue. What I am talking about is Excel converts (not displays) my formula to a value and the formula is no longer available i.e. I cant see what numbers were used to create the value.
May be your formatting is set to text for some cells
select all cells and click formatting and click general
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST
More we learn about excel, more it shows us, how less we know about it.
for chemistry
https://www.youtube.com/c/chemistrybyshivaansh
Thank you Hemesh, but as stated in my original post my cell format is "Number". Also I need to say that I am using Excel 2013, not 2003 as stated in my profile.
+ sign is old method of =
if you want to see not calculated formula use ' or text format, or custom format: "+"0;;;
Try an apostrophe
in front of the + sign '+
this is just what I saidOriginally Posted by pschultz614
When I enter +8+6 in a cell, I get the result 14 shown, but when I select the cell again I see the formula:
=8+6
in the formula bar. Maybe there is a setting in File | Excel Options that controls this, but I haven't seen one in Excel 2007.
Hope this helps.
Pete
Just to be clear: Are you showing the formula bar? If you look in the formula bar it is showing you 14 and not "=8+6"?
I tried messing with the lotus compatibility settings and looked through other calculation settings but (if the above is true) I could not reproduce your issue.
Do you have any macros running in this spreadsheet? Can you upload an example spreadsheet (Go Advanced>Manage Attachments)?
ChemistB
My 2?
substitute commas with semi-colons if your region settings requires
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)
Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
Of course, the simple answer is: don't do that. Enter =7+5 instead of +7+5.
That said, the problem seems to be an anachronism of specific formats, for example some Currency formats. (But not the default Currency format.) I don't believe I have seen the problem with a simple Number format; perhaps the misbehavior is locality sensitive. Click on Custom in the Format Cells context menu, and post exactly what appears there.
One dubious (not recommended) work-around is to set(!) the Lotus Transition Formula Evaluation option. (You might expect the opposite.)
However, I do not recommend doing that because it changes the evaluation of some arithmetic expressions. See http://office.microsoft.com/en-us/excel-help/calculation-differences-between-microsoft-excel-and-lotus-1-2-3-formulas-HP005198694.aspx.
In another forum, someone suggested rearranging the arithmetic terms. Of course, that does not work for +7+5. But perhaps you can defeat the "feature" by adding some idempotent operations. For example, does +7+5/1 work?
(Of course, you might want to limit the operations to those on a numeric keypad. I don't remember what they are, off-hand.)
As a final resort, you might fiddle with the (Custom) format to see if you can elimimate the feature that causes the partial evaluation in the Formula Bar.
By the way, it is indeed important to defeat that "feature", for reasons of correctness, not just aesthetics.
For example, +10/1.5 is converted to 6.66666666666667 in some contexts. But the binary representation of 6.66666666666667 is not the same as the binary representation of 10/1.5. A1=A2 might return TRUE because of a (dubious) heuristic in Excel. But MATCH(A1,A2,0) returns a #N/A error, indicating no match.
I will try to clarify: I am not wanting to "SEE" the formula in the cell so changing the cell format to TEXT or putting an apostrophe in front of the formula doesn't help me.
The key word I keep trying to use is CONVERT. Excel converts my formula into a value.
Please see attached snip tool pictures of a formula in cell "F74" BEFORE I hit "Enter" and AFTER I hit "Enter". You can see the cell and the formula bar.
Also, I do not want to use the "=" sign because it is much slower since it is not on the numeric keypad and I have a lot of numbers to type and dont want to always be searching for the "=" sign on the keyboard.Before.JPGAfter.JPG
I will try to clarify.... Did you read my response (#11), posted 2.5 hours before your follow-up quoted above?!
I acknowledged and explained the problem to the best that anyone can, AFAIK. I have dealt with this problem several times in the past.
I explained that it is an inherent behavior of Excel.
I would call it a defect; as such, don't expect a "clean" solution. I suspect it is a vestige of some Lotus compatibility; but someone implemented it backwards (see below).
I offered one work-around, to wit: setting(!) the Lotus Transition Formula Evaluation option. (I would expect it to be the opposite: setting the option might cause the misbehavior.)
But read the compatibility issues that option creates; see the URL that I cited in response #11. If you can live with that, setting that option might be a reasonable choice for you.
Normally, I would recommend against it, if only because the compatibility issues are very subtle.
[EDIT] I recommend deselecting that option after your data entry in order to minimize subtle compatibility problems with subsequent changes.
Unfortunately, setting the Lotus Transition Formula Entry option does not work around the problem. (I would expect that it, not the Evaluation option, would do the trick.) Too bad: that would have been a safer Lotus option to set as a work-around.
I suspect that's as good as it gets. Unfortunately, we cannot work around every Excel defect, at least not with the limitations that you impose.
(Personally, I think it is "faster" to enter formulas reliably using "=" instead of "+". In my experience, when we must do something repetitively "a lot" of times, we quickly adapt to keystrokes that initially seem inconvenient.)
-----
FYI, I mentioned that I cannot duplicate the misbehavior with +7+5 and the Number format. I am using Excel 2007.
But I can duplicate the misbehavior with +350*2/12 and the Number format, a user's example that I discussed in another forum nearly 3 years ago.
It becomes =350*0.166666666666667. As I cautioned, that is not the same binary result as 350*2/12.
Caveat: Duplicating the data entry misbehavior is tricky. The cell must be formatted "appropriately" first, of course. Also, we must enter the formula directly into the Formula Bar. In contrast, if we copy the text +350*2/12 and paste (press ctrl+C) directly into the cell, the formula is entered as =350*2/12, as we want it.
Last edited by joeu2004; 01-27-2016 at 02:52 PM. Reason: cosmetic; EDIT
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks