Cell Keeps Changing Formula to Value

[lnagel]

This is the strangest thing I've seen yet

Excel 2016

I have a particular range of cells that, no matter what I do, insist on replacing a formula with the numeric value of that formula UNLESS I enter the formula (manually) with an apostrophe in front of the "=" such as '=SUM(CC3/$BQ$24)

Entering the formula manually with the apostrophe seems to "fix" the problem but I am unable to copy from previous cell and have it work

Have looked at everything
Only happens with a few cells in a particular column
Copying formula only does not work
Copying "All" does not work
Turning Auo Calc on or off doesnt matter
Have tried Microsoft Office Repair - no difference

Any suggestions here from someone that has seen this before would be really appreciated

See diagrams below for this head scratcher



This is a corrected cell - Entered the formula with apostrophe in front




This is next cell down in column after copy of previous cell




Re-enter formula (manually) with apostrophe




Cell now shows and evaluates formula correctly

 

[Special-K99]

Could a macro be causing the change?

 

[lnagel]

No coding that pertains to these cells..... Checked for that also

Thanks

 

[Special-K99]

Hang on, why do you have a SUM around a divison ?

=SUM(CC3/$BQ$24)

is just

=CC3/$BQ$24

This prob wont solve your problem but it doesn't make sense, you can't (theoretically) SUM one number.

 

[Special-K99]

Hang on again!

You say "the formula gets replaced with the numeric value of that formula"

Thats what formulas do display values.

e.g.

A1 = 12
B1= 3

in C1
If I enter A1 / B1 I would expect to see the result of that formula 4 (12 divided by 3).

What are you expecting to see?

1. Remove any formatting from those cells.

2. Copy the formula and values to a NEW spreadsheet, dont copy the spreadsheet, manually enter the values and formulas. What does that produce?

 

[lnagel]

What I'm expecting to see when I click on the cell with the formula - is the formula as shown in the last PIC

What I am seeing is the value of that formula 0% - (instead of the formula) as shown in 2nd Pic

This exact same formula is working in numerous cells on this particular spreadsheet - and only a single column of cells appear to have the issue

I have tried copying to an external spreadsheet and I get the same results - I have tried copying from an external spreadsheet (that is working) with the same results

I have not tried deleting the existing column and recreating ( I can try that when I get home later today)

I can try without the parens but dont believe this will have any effect on the calculation

 

[lnagel]

And the winner was ---- Special K99 who pointed out my "DOH" error --- Yes underlying code was causing the issue

A couple of true-isms are appropriate here

Sometimes you cant see the forest cuz of the trees AND You can lead a horse to water but sometimes he remains just a dumb ***

Thank you very much

 

[Special-K99]

Ooh goodie! Do I get a prize?

The thought that it's now solved is the best prize I can have.
Glad you got it sorted

 

[daveq]

Thank you for this, it solved my problem too. Although it is clearly a serious Excel (2019) bug. I have code which does very rarely update the table. But nowhere near the cproblem cells. When I disabled the code the formula no longer got converted. Re-enabled the code straight afterwards and formula was retained. Excel was for some reason converting it when it shouldnt