Sep 25 2017
08:05 PM
- last edited on
Jul 25 2018
10:08 AM
by
TechCommunityAP
Sep 25 2017
08:05 PM
- last edited on
Jul 25 2018
10:08 AM
by
TechCommunityAP
Hi, this should be a very easy one, but I've not been able to solve it.
If I type a numbers division, like say "=3/4", Excel will automatically replace that simple formula with its result, "0.75" (loosing the formula). I understand that this may be due to speed reasons, so Excel won't need to calculate the result every time I open the worksheet. But, as I have less than 100 of such formulas, I don't think this is a very time-consuming task for an i7 processor.
I just want Excel show the result while keeping the original formula, so I can know where the result came from. I don't want to have to write the "3" in one cell, then the 4 in another cell, and then in a third cell type a formula like, say "=A1/B1". I just want Excel to keep the "=3/4" formula, and display the result. Is there any option, configuration or any other way to avoid the automatic substitution of the formula with its result?
I have noticed this only with divisions, not with additions, substractions or multiplications. Before I noticed this, I wrote several divisions that have been substituted with their results and now I don't know where the numbers come from.
Can anyone help me with this, please? Thank you.
Sep 26 2017 02:49 AM
Hi Jose,
Do you mean you see 0.75 in formula bar instead of =3/4 (and no such effect for =3+4) without pressing F9 for PC version?
Sep 26 2017 08:55 AM
Sep 26 2017 04:44 PM
Yes, in the formula bar the =3/4 is replaced with the result 0.75, therfore the cell also displays 0.75.
I have Office 365 (therefore, the latest version of Excel, i.e. 2016 in this case), PC version (running on Windows 10), and I do not press F9.
There is not such effect with additions, substractions or multiplications, only with divisions.
Thank you, Sergei.
Sep 26 2017 04:56 PM
Sep 27 2017 04:14 AM
Jose, to clarify - that's only with =3/4 or with any division, let say =10.741/3.14?
Sep 28 2017 06:20 AM
Jose,
I was not able to reproduce that if only not to apply the macro like
Sub Tst() Dim cell As Range For Each cell In ActiveSheet.UsedRange.SpecialCells(xlFormulas) If InStr(cell.Formula, "/") Then cell.Formula = cell.Value End If Next End Sub
but you have that on empty workbook... Also din't see such bug was mentioned.
Perhaps it's worth to repair the Office if helps.