Accounting format changing to fraction

Copper Contributor

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

@Dustin935 

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.

@Dustin935 

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.

@Dustin935 

 

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.

@Dustin935 

 

Sorry, now I also see the worksheet.
Cell formatting needs to be changed.
go to cell (or select cell range), click right mouse button.
Select Format Cells
Change the number format as you wish.

Examble, you can make it a standard for all cells.

Ps You could also select the whole worksheet if you tick the corner at the top left.

 

Hope to have helped to solve your problem, if not please give us a short feedback.

 

Nikolino

I know I don't know anything (Socrates)

@NikolinoDE I don't think you understand.

 

I have already formatted the whole of column A as Accounting, then I entered the formulas that I described in the spreadsheet.  Can you please test this by clicking an empty cell in column A, verifying it is accounting format already, and then do +23/2 to match the example.

@Dustin935 

 

Please take a look at this file.

Send you my last / notes in the inserted file.
If this doesn't help either, I really don't know how I can help you. The translation probably doesn't come across as you want me to understand.

 

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.

@NikolinoDE 

 

Here again with your formula

 

It works for me ... if it doesn't work for you after these settings, please give us a short feedback.

 

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.

@NikolinoDE 

I had a look at the file and unfortunately this isn't addressing the issue.  The formatting for the entire of column A was originally _-"$"* #.##0,00_-;-"$"* #.##0,00_-;_-"$"* "-"??_-;_-@_- which is the accounting formatting.

When I do the formula +23/2 in one of those already formatted cells, then the formatting is changed to fraction and the formula removed (only 11.5 shows in the formula bar, and 11 1/2 shows in the cell due to formatting being changed without me asking it to).

 

So in summary:

- A3 originally was formatted as _-"$"* #.##0,00_-;-"$"* #.##0,00_-;_-"$"* "-"??_-;_-@_-

- +23/2 is entered into A3

- Formatting is changed to fraction and "11 1/2" is displayed in the cell

- Formula is replaced by answer to formula of 11.5 in the formula bar

I'm sorry not to have helped you, but my attempt ends here because I have finished my Excel Latin. Maybe someone else here in the forum can help you better.

Thank you and sry for taking your time.

I wish you have a nice day
Nikolino
I know I don't know anything (Socrates)
I believe to have +23/2 interpreted as a formula you need to have the Lotus compatibility setting "transition formula entry" enabled (File/Options/Advanced - scroll to the bottom).

As far as changing the format from accounting to fraction, I'm afraid I can't replicate the issue.

@JMB17 I tried that and it worked for the +23/2.  I have another workbook at work that I will send home to try as it has =23/2 in it also doing this.  Once I have it i'll copy the sheet to a new workbook (privacy reasons) and see if I can replicate the issue.

 

Thankyou for the input

@Dustin935 For what its worth... this is now happening to me. Wild, given I've spent a decade heavily using Excel and never run into this before. Really maddening.

@YoDeFoe 

Last try

 

File> Options

[Is the tick next to "Insert decimal point automatically" not set anyway?]

 

Decimal places can be specified in "Format cells".

 

Note: In order to ensure that the changes in the control panel are adopted by Excel and are up to date, you should restart Excel.

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

What is entered under File - Options - Advanced for the decimal separator?

Please also check whether the check mark next to "Use separator from operating system" is ticked, and below that next to decimal separator and thousand separator.

Cheers
Nikolino

@Dustin935  I have had this happen to me a few times in the past.  I save the file, close it, and then re-open it.  That has always fixed it for me.