2

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?

0

6 Answers 6

2

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.

1

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.

4
  • 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, 2009 at 16:55
  • can you provide an example that will trigger the exception case?
    – akf
    Sep 14, 2009 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, 2009 at 17:05
  • 2
    It's rather hard to tell if you don't provide an example here.
    – user3463
    Sep 14, 2009 at 17:53
1

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

0

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

0

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.

0

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

You must log in to answer this question.