Problem with numbers division

Question

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.

sergeibaklan · Answer

Jose,&nbsp;I was not able to reproduce that if only not to apply the macro likeSub 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 Subbut you have that on empty workbook... Also din't see such bug was mentioned.&nbsp;Perhaps it's worth to repair the Office if helps.&nbsp;

sergeibaklan · Answer

Hi Jose,&nbsp;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?&nbsp;

alun davies · Answer

just so I have this correct you are looking at3 4 0.753 4 =3+4The Excel tool was built to Calculate 7 for the last cell and if you have =3+4 you will have had to fix it that way.

josé angeles · Answer

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.

josé angeles · Answer

If I type =3+4, the cell will display the result, 7, but it will keep the formula. If I type =3/4, the cell will also display the result, 0.75, but the formula will be replaced too with the result, loosing the formula (which I want to keep).

Thank you, Alun.

Forum Discussion

Problem with numbers division

Share

Resources