functions that don't function

P

Peter Lipp

Hi,

I have some strange effects that I can't explain - and am looking for ideas.

What happens is:

I have a cell where I can enter anything (almost) that starts with a "=",
and the cell allways displays 0. I realized that when I had a more
complicated calculation that doesn't produce the right results. Then I
played around and changed it to =9 or =2+3 or whatever - I can see the
correct equation, but the value displayed is always 0. Zero. Null. I also
copied a =expression to a different cell and excel displayed the value of
the original cell, non-zero, but the value should clearly be different. ....
Seems related to the first effect.

Any ideas how to fix that? (In a new worksheet things behave as expected)

thanks

peter
 
B

Bernie Deitrick

Peter,

It is possible that somebody got confused and used a custom format of "0"
when they should have used 0 (or perhaps some other strange formatting).
"0" will show a zero no matter what the cell has entered in it. Try
reformatting the cell.

HTH,
Bernie
MS Excel MVP
 
K

Ken Wright

Is it by any chance a sheet that would have been used for a presentation, as
if so it is always possible that the cells have been formatted as Thousands
or Millions, such that entering 1234567 would display 1.2 if formatted for
millions. This is done by adding a comma(s) after the existing format, with
each comma taking it back 3 decimals. This means that entering a number of
anything up to say 49,999 would appear as 0.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission :)
------------------------------­------------------------------­----------------
 
P

Peter Lipp

Try reformatting the cell.
Tried. No changes. Can set it to currency e.g. and it shows 0 ? as a result.
Or 0.000000000000000000? (to check for the thousands or millions that Ken
suggested). Interestingly, I also can enter an expression that is invalid
(like =asdasd+ksdgsdg) and it displays 0. Maybe this gives a clue to the
reason?

Peter
 
K

Ken Wright

If you have any circular references, then that will also prevent the calcs
working.

Your call, but if you want to send me the book, then I'll happily take a
look.

Just need to take the NOSPAM bit out of my email addy


--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------­------------------------------­----------------
It's easier to beg forgiveness than ask permission :)
------------------------------­------------------------------­----------------
 
Top