| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Cell reference in formula always returns a zero value
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Cell reference in formula always returns a zero value
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. -- David Biddulph "Jim Alderman" wrote in message ups.com... 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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Cell reference in formula always returns a zero value
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 on here. David Biddulph wrote: 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. -- David Biddulph "Jim Alderman" wrote in message ups.com... 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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Cell reference in formula always returns a zero value
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 On 27 Nov 2006 16:58:35 -0800, "Jim Alderman" wrote: 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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Cell reference in formula always returns a zero value
Jim
ToolsOptionsCalculation. Are you set to "Automatic"? Gord On 27 Nov 2006 17:21:20 -0800, "Jim Alderman" wrote: 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 on here. David Biddulph wrote: 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. -- David Biddulph "Jim Alderman" wrote in message ups.com... 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. Gord Dibben MS Excel MVP |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Cell reference in formula always returns a zero value
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. Gord Dibben wrote: 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 On 27 Nov 2006 16:58:35 -0800, "Jim Alderman" wrote: 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. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Cell reference in formula always returns a zero value
Gord:
Yes, I am set to automatic. Gord Dibben wrote: Jim ToolsOptionsCalculation. Are you set to "Automatic"? Gord On 27 Nov 2006 17:21:20 -0800, "Jim Alderman" wrote: 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 on here. David Biddulph wrote: 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. -- David Biddulph "Jim Alderman" wrote in message ups.com... 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. Gord Dibben MS Excel MVP |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Cell reference in formula always returns a zero value
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. Jim Alderman wrote: Gord: Yes, I am set to automatic. Gord Dibben wrote: Jim ToolsOptionsCalculation. Are you set to "Automatic"? Gord On 27 Nov 2006 17:21:20 -0800, "Jim Alderman" wrote: 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 on here. David Biddulph wrote: 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. -- David Biddulph "Jim Alderman" wrote in message ups.com... 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. Gord Dibben MS Excel MVP |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Cell reference in formula always returns a zero value
Jim,
Is it possible the the cell A2 has a custom format "0" (0 must be in quotes)? rgds André "Jim Alderman" wrote in message ups.com... 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. |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Cell reference in formula always returns a zero value
And
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 Andre Croteau wrote: Jim, Is it possible the the cell A2 has a custom format "0" (0 must be in quotes)? rgds André "Jim Alderman" wrote in message ups.com... 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to convert a month to a quarter ...... | New Users to Excel | |||
| Reusing formula | Excel Discussion (Misc queries) | |||
| Array formula returns blank in the cell where it is entered | Excel Worksheet Functions | |||
| Using a cell reference within a cell reference in a formula | Excel Worksheet Functions | |||
| copied formula has correct cell reference, but result of original | Excel Worksheet Functions | |||
Linear Mode
