Aug 08 2020 10:02 PM
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?
Aug 08 2020 11:14 PM
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.
Aug 09 2020 04:51 AM
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.
Aug 12 2020 04:33 AM
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.
Aug 12 2020 04:41 AM
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)
Aug 12 2020 04:47 AM
@NikolinoDE This box (R1C1) isn't checked for me.
Aug 12 2020 04:55 AM
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)
Aug 12 2020 05:17 AM
@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.
Aug 12 2020 05:47 AM
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.
Aug 12 2020 05:52 AM
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.
Aug 12 2020 05:58 AM
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
Aug 12 2020 06:09 AM
Aug 12 2020 09:40 AM
Aug 14 2020 02:02 AM
@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
Feb 16 2021 05:05 PM
@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.
Feb 17 2021 07:05 AM
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)
Feb 17 2021 08:41 AM
Feb 10 2024 07:14 AM
@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.