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

Advertisements

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

Jim Alderman

Gord:

Yes, I am set to automatic.


Gord said:
Jim

Tools>Options>Calculation.

Are you set to "Automatic"?


Gord



Gord Dibben MS Excel MVP
 
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.
 
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.
 
Ad

Advertisements

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!
 

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