Simple input/display question

[Oxylot]

Hi. Really easy. Excel 3. SP3.

Cell A1 contains a value (let's say '6' but not important).
Cell B1 should contain a number (a multiplier for A1).
Effectively formula in C1 should do A1*B1.

If B1 is 1/1000000000000 (a million, millionth or Pico)
the format of the Cell B1 will display just the way as
described (1/1000000000000) and not as 1.00E-12 which
seems silly.

Conversly if B1 contains 1000000000000 it WILL display
1.00E+12 and this is correct.

In both situations, B1 is display formatted to Scientific.
The resultant answer in C1 is returned as correct if B1 => 1
but will eventually give a #VALUE the smaller B1 becomes
(less than one ten thousandth).

Strangely if B1 is entered as .01 to .00000001 the display
WILL do the proper 1.00E-factor.

Can someone straighten out my thinking ?

 

[Jim Cone]

Excel has to determine if the entry is text, a fraction or a date.
It has decided that your entry is text. Try using...
= 1/1000000000000
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Oxylot"
wrote in message
Hi. Really easy. Excel 3. SP3.

Cell A1 contains a value (let's say '6' but not important).
Cell B1 should contain a number (a multiplier for A1).
Effectively formula in C1 should do A1*B1.

If B1 is 1/1000000000000 (a million, millionth or Pico)
the format of the Cell B1 will display just the way as
described (1/1000000000000) and not as 1.00E-12 which
seems silly.

Conversly if B1 contains 1000000000000 it WILL display
1.00E+12 and this is correct.

In both situations, B1 is display formatted to Scientific.
The resultant answer in C1 is returned as correct if B1 => 1
but will eventually give a #VALUE the smaller B1 becomes
(less than one ten thousandth).

Strangely if B1 is entered as .01 to .00000001 the display
WILL do the proper 1.00E-factor.

Can someone straighten out my thinking ?

 

[Rick Rothstein \(MVP - VB\)]

To you, 1/1000000000000 is a number; but unlike 1000000000000, which is a
number, 1/1000000000000 is text (because the slash is not a number). In
order to get Excel to generate a number for the text 1/1000000000000, you
have to tell it to perform the division operation... you do that by putting
an equal sign in front of it (telling Excel to evaluate the expression.
=1/1000000000000 will generate the value you want.

Rick

 

[Oxylot]

To you, 1/1000000000000 is a number; but unlike 1000000000000, which is a
number, 1/1000000000000 is text (because the slash is not a number). In
order to get Excel to generate a number for the text 1/1000000000000, you
have to tell it to perform the division operation... you do that by putting
an equal sign in front of it (telling Excel to evaluate the expression.
=1/1000000000000 will generate the value you want.

Rick

Aaaagh. You're right. Thanks guys.
My own blind spot of the day -(