Cell reference in formula always returns a zero value

[Jim Alderman]

Example:

Cell A1 contains the number 6.
Cell A2 contains the formula =A1 but displays a value of 0.

The calculation option is set to Automatic.

What has gone wrong? I am obvious missing something here.

 

[David Biddulph]

I would guess that A1 contains text, rather than the number 6. Look in the
formula box when A1 is selected, and see whether you've got any leading or
trailing spaces or other characters.

 

[Jim Alderman]

No, A1 is numeric. I created the simple example for the ease of
discussion. I have existing spreadsheets with elaborate formulae that
used to work fine. Now they all fail. Cells that contain a formula
display the value of the formula before my issue began. If I go to one
of the referenced cells and change its value, the formula cell still
shows the original value. Go figure - I have no idea what is going

 

[Gord Dibben]

Jim

=A1 will return whatever is in A1 whether it is text or numeric.

If it shows a zero something else is going on.

How does the 6 get entered to A1.....manually or by formula?

Do you have any helpful event code in the sheet that is changing the 6 in A2 to
a zero?

Right-click on sheet tab and "View Code"

Anything there that could be doing the deed?

Right-click on the Excel logo at left of File on the worksheet menubar and
select "View Code".

Anything there?


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Jim

Tools>Options>Calculation.

Are you set to "Automatic"?


Gord

No, A1 is numeric. I created the simple example for the ease of
discussion. I have existing spreadsheets with elaborate formulae that
used to work fine. Now they all fail. Cells that contain a formula
display the value of the formula before my issue began. If I go to one
of the referenced cells and change its value, the formula cell still
shows the original value. Go figure - I have no idea what is going

Gord Dibben MS Excel MVP

 

[Jim Alderman]

Gord:

Thanks for your suggestions. However, nothing shows up as an issue in
either code view.

In my simple example, the 6 was keyed into A1.

It appears that something has failed within Excel. I can no longer
create a formula with a cell reference and have the value of that cell
returned to the formula. Also, existing formulae will no longer update
when the value of a referenced cell is changed.

It looks like I will need to reload Excel.

 

[Jim Alderman]

Gord:

Yes, I am set to automatic.

Jim

Tools>Options>Calculation.

Are you set to "Automatic"?


Gord



Gord Dibben MS Excel MVP

 

[Jim Alderman]

Gord:

After further testing I have found that the problem is simply that
formulae no longer work. A formula of =1+1 displays a value of 0. It
appears that my Excel installation is hosed.

 

[Andre Croteau]

Jim,
Is it possible the the cell A2 has a custom format "0" (0 must be in
quotes)?
rgds

André

 

[Jim Alderman]

Andre:

Thanks for the thought, but there is no custom format. All formulae
are failing, both in existing spreadsheets and new ones. When you open
a new spreadsheet and type =1+1 into A1 and it displays 0, Excel must
certainly have experienced some type of corruption.

Jim

 

[skido700]

I may have a solution. I had this exact same problem; there were no formatting issues and calculation was set to automatic. There were also no weird codes. I had even clicked "Calculate Now" button.

However, after clicking the "Calculate Sheet" button, it worked! I don't know why "Calculate Sheet" worked and "Calculate Now" didn't, but apparently there's a difference.

Again, calculation was already set to automatic.

 

[Kuralew]

Hello,

Please make sure the number of columns that you are referring are of the same match to the destination. i.e. if you are referring from column c1:G4 (in the case of merged cells), the destination should also be of the same size. I had the same problem and solved it this way.

 

[abickley23]

I know this is older, but I had the exact same issue. When i hit calculate sheet like skido700 suggested, it worked! thank you!

 

[yoy]

Had the same problem. Whenever I referenced a calculated cell, despite it having a numericformat value, the new cell always = 0 ( example: A5= formula with result 87. If I created another formula (=A5+3) in cell (BB2), BB2 = 0. The calculate sheet trick did not work for me. I did notice if I set calculations to manual, and reran the above operation, I got the correct answer =A5+3=>90. Setting sheet calculations back to automatic, would result = 0 once again. No new calculation changes would update on my sheet for that matter.

The problem was a circular argument in my sheet. The solution was to correct the circular argument and all my calculations worked again. I believe, the one circular argument kept excel in a perpetual loop and failed to complete all calculations if set to automatic. Setting it to manual, would temporarily stop the calculations and let me run a new calculation.

Fix any circular arguments. This will avoid future calculation errors resulting in zero, or DIV/0 errors.

 

[jasimbaya]

"Calculate Sheet" in the Formulas tab worked for me as well. Thank you skido700.

 

[kcdillos]

I recently had the same issue, however, "Calculate Sheet" did not work. The issue appeared to be excel not recognizing the cells needed to be recalculated in a macro file.

Ctrl + Alt + Shift + F9 solved the problem for me.