I am having troubles with Excel 2013. I have all cells formatted to Accounting, but when I use + to start a division formula Excel forces the formatting to Fraction. It also removes my formula, converting the cell to a value in the process. This is frustrating and annoying and I want to disable this "feature;" however, I cannot find anything about this issue.
Below is an image that illustrates the outcome of the issue.
Excel Formatting Issue.PNG
Don't add the +. It is not needed.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU
Not using the + gives me the same result. It converts the formatting to fraction, then removes my formula and enters the value. I want to maintain the formula for future reference. I don't want to just display the value.Originally Posted by Glenn Kennedy
And using the = is cumbersome. I am entering lots of info into a budget and need to maintain those formulas for reference. It is far easier to use the + to begin a formula. I haven't experienced this any other time when using the +.
Odd. It doesn't happen to me. can you post a sample of your sheet, showing the problem.
It doesn't happen when the cell is formatted to General before entering the formula. Only when pre-formatted to Accounting. It is so strange.
The spreadsheet attached is very simple and isn't the budget that I've been working on, but it does show an example of the resulting formatting.
Thanks for your help by the way!!
OK. So, it happens with me too. If I enter +1234/3, I get fractions. However, if I enter =1234/3 it is fine. It stays as accounting. Can you double check that entering =1234/3 causes the formatting to go to accounting at you end???
Just that when the cell is formatted to General that the formula shows the calculated value and retains the formula (and + is converted to = as expected), but when pre-formatted to accounting the formula is erased, the value remains, and now the formatting is Fraction. It makes no difference if I send the entire budget, or just this simple example. It is the same outcome either way.
Entering =1234/3 does return 411.33 formatted as Accounting. +1234/3 gives 411 1/3 and is formatted as Fraction.
Ummm what am I meant to see here...
I do not seem to see the same problem. What I see in your file:
A2 is displaying the value 8759 right aligned. the formula bar shows the formula =17518/2.
If I enter =8325/2 into A2, the formula remains as entered in the formula bar, and the cell displays 4162.5.
The cell format code remains General throughout.
B2 is displaying the value 8759 kind of center aligned, in keeping with the # ?/? number format. The formula bar shows the same value.
If I enter 4162.5 into B2, the entry stays the same in the formula bar, and the cell displays 4162 1/2.
The cell format remains # ?/? throughout.
Lotus compatibility does not seem to effect this behavior.
I'm using 2007. Is there some other setting in 2013 (or something else specific to the newer version) that could be doing this?
On edit: If I enter =17518/2 into B2, the formula bar shows =17518/2 and the cell displays 8759 (with the trailing spaces for the fraction part).
If I enter =8325/2 into B2, the formula bar retains the formula, and the cell displays 4162 1/2.
It still seems that I cannot recreate the behavior you describe.
Last edited by MrShorty; 12-11-2015 at 01:47 PM.
This is also occurring for me in Excel 2010 as well
MrS, preformat a column to accounting and enter +123/3 I get a fraction, too.
IrisS. So why do you want to use the +. It works fine without it and, unless I'm mistaken, the need to use + went out with Lotus 123.
I use the + to start all formulas (pretty much). If I just enter (for example) 325+65 I don't get a value in return; however, if I enter +325+65 I get the 390 value I'm expecting formatted as Accounting. I get tired of always having to hit the = to do simple calculations. I do just fine with all other math functions, just not division for some reason. I was hoping it was going to be an easy fix. Lol!
Just on the offchance. Have you tried turning your PC off and on again.... I know it's the "hairy man's" approach to everything; but....
I have had this issue for a while now and I have had it happen on multiple computers. I just was finally frustrated enough to try to remedy it.
Edit: I tried it on another computer in my office (still Excel 2013) and it acts the same way on that computer.
Last edited by Iriscience; 12-11-2015 at 02:14 PM.
That's the part I was missing. When I do that, it behaves as you describe.MrS, preformat a column to accounting
I hate when Excel thinks for itself, like this.
Sorry, but I don't think that I can help further; other than to say... learn to love "=" and forget Lotus 123
Additional observation: Is this behavior limited to the "built in format"? If I preformat a cell as custom $#,##0.00, and enter +17518/2, the formula bar is converted to the value 8759, but the cell format remains unchanged, so the display is $8,759.00.
If I preformat the cell as custom _($* #,##0.00_);_($* (#,##0.00) [this is the regular "Accounting format code" with the text and 0 sections removed], It behaves like the other custom format.
How much of your concern is that it converts the formula to a value, and how much is the number formatting change? If it is more about the number format, it seems that this behavior might be tied into using the built in number format. A potential resolution might be to use a custom format that is slightly different from the built in format code.
The majority of my concern is keeping the formula for future reference, so if I leave this position someone else can follow along behind me and follow my rationale by looking at the formulas. I really appreciate all the help that you and Glenn Kennedy have given me!! I will just have to remember to use = when doing division.
Thanks again!
One final observation (use at your own risk). If I go into Excel options and turn on "transition formula entry" in the Lotus compatibility settings, I can enter +17518/2 into a cell preformatted as accounting or similar. It will leave the cell format alone, and it will leave the formula in the formula bar.
I do not know all that changes with these Lotus compatibility settings, but it seems that Excel is capable of what you want. Whether it is a good idea, I don't know. It may very well affect something else that you like. I tend to think it is best to learn to work with Excel on its own terms without the Lotus settings.
I looked at the Excel help page for that setting and this was what was explained for how that option affects Excel:
So I definitely won't be activating that option. Thank you so much though! At least now I know I have to work around that limitation."When Transition Formula Entry is selected, Microsoft Excel converts formulas entered with Lotus 1-2-3 release 2.2 syntax to Microsoft Excel syntax and makes names defined in Microsoft Excel behave as defined names behave in Lotus 1-2-3.
In addition, if the Transition Formula Evaluation check box is selected, when Microsoft Excel evaluates formulas, text strings are converted to 0, Boolean expressions are converted to 0 or 1, and database criteria are converted according to the rules used in Lotus 1-2-3."
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks