Forum Discussion
Accounting format changing to fraction
I have Excel 2013 and latest update.
I have been experiencing a strange issue that can be replicated in office 365 excel too.
Blank sheet, Select a cell and change the formatting to accounting, enter =23/2. You'd expect $11.50 right?
I lose my formula from the formula bar and instead it now just displays 11.5, the cell value is now "11 1/2" and formatted as fraction.
This doesn't happen if you were to enter say =23.1/2 as it appears to change whole numbers only to fraction. Using a = or + doesn't change the outcome. Using a custom format to resemble accounting doesn't stop it.
I noticed that with number or currency formatting the formula also disappears with them but if I type something like =23+2 then the formula remains. So it seems something strange is happening with division?
Why is this happening if I already have a format selected? Why is the formula being removed? Does regional settings make a difference (AU) as I can't find this reported anywhere but two PC's with two different versions of Excel both do this?
I'm really puzzled... Thoughts?
18 Replies
- Click on the Excel button at the top left, then in the dialog at the bottom right on Excel options.
In the dialog that follows, select formulas on the left.
Then on the right below Working with formulas, remove the check mark for R1C1 reference type.
I'm not an Office 365 expert or as far as I know, since Office 2007 you can correct this ... or should be corrected so.If you find this helpful, please mark it as "Best Answer" and as Like (click thumbs up), it will be beneficial to more Community members reading here.
Nikolino
I know I don't know anything (Socrates)
NikolinoDE This box (R1C1) isn't checked for me.
One cause of the error could probably be that you accidentally changed the standard format ...
Open the format templates in the Start tab. Then right-click the Standard style and select Modify from the context menu.It's just a chance, without a workbook you can only guess.
It is always more sensible to upload a file (without sensitive data) so that we can understand the problem. This enables us to offer you a solution that is quicker and more specific, which in turn could be available to you more quickly.Nikolino
I know I don't know anything (Socrates)
* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.
* Beware of scammers posting fake support numbers here.
Here is a quick example of what I'm experiencing
Strange that this book is happy with =23/2 but +23/2 is causing the issue. Normally it's both that are doing it.
See if you get the same issues with this.
For what it is worth, I do not experience this. If the input starts with an '=' or a '+' it remains as a formula and picks up the formatting from the cell. Without '=' or '+' it is taken to be a fraction and formatted accordingly.
I am using Microsoft 365 beta channel.
