Cell reference in formula always returns a zero value


J

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.
 
Ad

Advertisements

D

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.
 
J

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
 
G

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
 
G

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
 
J

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.
 
Ad

Advertisements

J

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.
 
A

Andre Croteau

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

André
 
J

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
 
Joined
Sep 5, 2013
Messages
1
Reaction score
0
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.
 
Ad

Advertisements

Joined
Aug 30, 2017
Messages
1
Reaction score
0
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.
 
Joined
Jul 9, 2019
Messages
1
Reaction score
0
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

Joined
Jul 3, 2020
Messages
1
Reaction score
0
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.
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top