#VALUE error

  • Thread starter Thread starter Mahadevan Swamy
  • Start date 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
 
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.
 
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...
 
I am curious to know what this Lotus123 compatibility is? and what is
this transition formula evaluation?
 
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.
 
Back
Top