up vote 2 down vote favorite
share [g+] share [fb]

In excel I have a cell with formula ("=......"). On the cell, I can clearly see the value of the formula, but the formula bar shows "=#VALUE!". How is that possible?

link|improve this question
feedback

migrated from stackoverflow.com Sep 14 '09 at 17:10

This question came from our site for professional and enthusiast programmers.

6 Answers

up vote 2 down vote

highlight the cell and hit F2 then edit it in place.

Additional info:
I also want to be clear that your formula isn't 

("=...")

but instead is 

="..."

For example, 

=CONCATENATE(A1,"asdf")

is a good formula. 

("=CONCATENATE(A1,"asdf"")

is not a good formula.

link|improve this answer
feedback
up vote 1 down vote

It means that there is something wrong with the formula in the cell. Perhaps there is a cell that you are trying to reference that is empty or there has the wrong datatype.

link|improve this answer
But if i click on that cell and hit "copy" and paste it on a notepad, the "result" is correct (my formula is just a string concatenation of various substrings" and other ranges) – Saobi Sep 14 '09 at 16:55
can you provide an example that will trigger the exception case? – akf Sep 14 '09 at 17:03
Even if my formula is wrong, what if i want to edit my formula? I can't even do that now, since the formula bar shows "=#VALUE!", and my formula is not "=VALUE!" – Saobi Sep 14 '09 at 17:05
2  
It's rather hard to tell if you don't provide an example here. – Randolph West Sep 14 '09 at 17:53
feedback
up vote 0 down vote

It might be something that only looks correct such as :

A1+A2+A3 - error
A5: =SUM(A1+A2+A3) - error

A6: =SUM(A1,A2,A3) - correct
A7: =SUM(A1:A3) - correct

link|improve this answer
feedback
up vote 0 down vote

Possible that your formula requires a specific addon that is not installed - For example using yearfrac() in a system which doesn't have Analysis ToolPak (ATP) installed will cause throw this error.

link|improve this answer
feedback
up vote 0 down vote

methinks... the cell size isnt that big enough to fit the value.. try increasing the width

link|improve this answer
feedback
up vote 0 down vote

Just double click on the cell and Press Shift+Ctrl+Enter.You will be able to see the values properly.

link|improve this answer
feedback

Your Answer

 
or
required, but never shown
discard