#VALUE error

  • Thread starter Mahadevan Swamy
  • Start date
M

Mahadevan Swamy

When I go to a cell a type the formula: = 2^12*B15 , it reports a
#VALUE error. B15 is a text value.

When I look at my friend's workbook, he has written the same formula
and it reports 0. How can I do this? I have checked my number formats
and changed it to special but still it is not solving the problem.

How can I solve this problem?

Thanks in advance.

Swamy
 
D

Dave Peterson

Your friend has a Lotus 123 compatibility setting toggled on:

Tools|options|Transition tab|Transition formula evaluation
is not checked for you and it is checked for him.

I don't use Lotus 123 and don't want any of those options checked.

If I didn't want to see the #value error, I'd either hide it with:
Format|Conditional formatting

a check in the formula:
=if(isnumber(b15),(2^12)*b15,0)

or use a different formula:
=(2^12)*N(B15)

====
Yep, I added ()'s. I find that they make it easier for me to read--I don't have
to rely on or remember the order of precedence.
 
M

Mahadevan Swamy

Thanks a lot for your solution. :)

Your friend has a Lotus 123 compatibility setting toggled on:

Tools|options|Transition tab|Transition formula evaluation
is not checked for you and it is checked for him.

I don't use Lotus 123 and don't want any of those options checked.

If I didn't want to see the #value error, I'd either hide it with:
Format|Conditional formatting

a check in the formula:
=if(isnumber(b15),(2^12)*b15,0)

or use a different formula:
=(2^12)*N(B15)

====
Yep, I added ()'s. I find that they make it easier for me to read--I don't have
to rely on or remember the order of precedence.
Click to expand...
 
M

Mahadevan Swamy

I am curious to know what this Lotus123 compatibility is? and what is
this transition formula evaluation?
 
D

Dave Peterson

MS wants to have all Lotus 123 users buy excel and use that. To make those
user's transition a bit easier, MS allowed them to change some settings--the way
excel behaves (navigation keys), the way excel treats text in arithmetic (you've
seen this), and how it treats your formulas when you enter them into a cell.

Try entering:
01/02/2007
with transition formula entry checked and unchecked.

As a user of Lotus back in the 80's (and not since), I've lost track of what all
the differences are.

From xl2003's help:

Transition formula evaluation Opens and evaluates Lotus 1-2-3 files without
losing or changing information. With this option selected, Excel evaluates text
strings as 0 (zero), Boolean expressions as 0 or 1, and database criteria
according to the rules used in Lotus 1-2-3.

Transition formula entry Converts formulas entered in Lotus 1-2-3 release 2.2
syntax to Excel syntax, and makes names defined in Excel behave like names
defined in Lotus 1-2-3.


And toggle the navigation key and watch the difference when you hit home and
ctrl-home as well as the tab key.
 
Top