largest value that can be entered into MS Excel field
 

If I type in the following number into a field:
123,456,789,123,456,789
and hit enter, I get the following number in the field:
123,456,789,123,456,000

If I then take the second number, subtract 2 from it in another cell, and
then do an exact function on the 2nd and 3rd (now 2 less than the 2nd), I get
a "TRUE" result.

I'm using Excel 2003, service pack 2.

largest value that can be entered into MS Excel field
 

Excel keeps track of 15 significant digits--no more.

So when you typed in that giant number, excel actually changed it what you saw.

And when you subtracted 2 from that 123,456,789,123,456,000 number, excel still
kept track of 15 digits. So it resulted in 123,456,789,123,456,000.



Aaron Z wrote:

If I type in the following number into a field:
123,456,789,123,456,789
and hit enter, I get the following number in the field:
123,456,789,123,456,000

If I then take the second number, subtract 2 from it in another cell, and
then do an exact function on the 2nd and 3rd (now 2 less than the 2nd), I get
a "TRUE" result.

I'm using Excel 2003, service pack 2.

--

Dave Peterson

largest value that can be entered into MS Excel field
 

15 digits, everything after that will be truncated to zero


--


Regards,


Peo Sjoblom



"Aaron Z" wrote in message
...
If I type in the following number into a field:
123,456,789,123,456,789
and hit enter, I get the following number in the field:
123,456,789,123,456,000

If I then take the second number, subtract 2 from it in another cell, and
then do an exact function on the 2nd and 3rd (now 2 less than the 2nd), I
get
a "TRUE" result.

I'm using Excel 2003, service pack 2.

largest value that can be entered into MS Excel field
 

that is correct, there are 15 digits of precision in Excel.

Aaron Z wrote:

If I type in the following number into a field:
123,456,789,123,456,789
and hit enter, I get the following number in the field:
123,456,789,123,456,000

If I then take the second number, subtract 2 from it in another cell, and
then do an exact function on the 2nd and 3rd (now 2 less than the 2nd), I get
a "TRUE" result.

I'm using Excel 2003, service pack 2.

largest value that can be entered into MS Excel field
 

How do I get it to not change the number? I need it to retain what its
given, unmodified; and as importantly, display it (without having to first
change the cell to text format). It sounds like this isn't possible given
the 15-digit constraint.

Thanks for the quick reply.


"Dave Peterson" wrote:

Excel keeps track of 15 significant digits--no more.

So when you typed in that giant number, excel actually changed it what you saw.

And when you subtracted 2 from that 123,456,789,123,456,000 number, excel still
kept track of 15 digits. So it resulted in 123,456,789,123,456,000.



Aaron Z wrote:

If I type in the following number into a field:
123,456,789,123,456,789
and hit enter, I get the following number in the field:
123,456,789,123,456,000

If I then take the second number, subtract 2 from it in another cell, and
then do an exact function on the 2nd and 3rd (now 2 less than the 2nd), I get
a "TRUE" result.

I'm using Excel 2003, service pack 2.

--

Dave Peterson

largest value that can be entered into MS Excel field
 

You can't obviously make any calculations but if you precede the entry with
an apostrophe ' or format the cell(s) as text before you type it will be
displayed correctly


--


Regards,


Peo Sjoblom


"Aaron Z" wrote in message
...
How do I get it to not change the number? I need it to retain what its
given, unmodified; and as importantly, display it (without having to first
change the cell to text format). It sounds like this isn't possible given
the 15-digit constraint.

Thanks for the quick reply.


"Dave Peterson" wrote:

Excel keeps track of 15 significant digits--no more.

So when you typed in that giant number, excel actually changed it what
you saw.

And when you subtracted 2 from that 123,456,789,123,456,000 number, excel
still
kept track of 15 digits. So it resulted in 123,456,789,123,456,000.



Aaron Z wrote:

If I type in the following number into a field:
123,456,789,123,456,789
and hit enter, I get the following number in the field:
123,456,789,123,456,000

If I then take the second number, subtract 2 from it in another cell,
and
then do an exact function on the 2nd and 3rd (now 2 less than the 2nd),
I get
a "TRUE" result.

I'm using Excel 2003, service pack 2.

--

Dave Peterson

largest value that can be entered into MS Excel field
 

If the number are entered as text, anytime the values need to by used by a
function the VALUE function can be used to convert the text value to numeric
ones. However, you'll still be limited to 15 digits of precision
--
Kevin Backmann


"Bob I" wrote:

that is correct, there are 15 digits of precision in Excel.

Aaron Z wrote:

If I type in the following number into a field:
123,456,789,123,456,789
and hit enter, I get the following number in the field:
123,456,789,123,456,000

If I then take the second number, subtract 2 from it in another cell, and
then do an exact function on the 2nd and 3rd (now 2 less than the 2nd), I get
a "TRUE" result.

I'm using Excel 2003, service pack 2.

largest value that can be entered into MS Excel field
 

The VALUE function is obsolete, if you need to calculate a text number using
an operator like + - * / the calculation itself will convert them but there
is no sense if you have more than 15 digits
I haven't found a single occasion where the VALUE function is more useful
than 1* or --
except maybe for pedagogical reasons


--


Regards,


Peo Sjoblom



"Kevin B" wrote in message
...
If the number are entered as text, anytime the values need to by used by a
function the VALUE function can be used to convert the text value to
numeric
ones. However, you'll still be limited to 15 digits of precision
--
Kevin Backmann


"Bob I" wrote:

that is correct, there are 15 digits of precision in Excel.

Aaron Z wrote:

If I type in the following number into a field:
123,456,789,123,456,789
and hit enter, I get the following number in the field:
123,456,789,123,456,000

If I then take the second number, subtract 2 from it in another cell,
and
then do an exact function on the 2nd and 3rd (now 2 less than the 2nd),
I get
a "TRUE" result.

I'm using Excel 2003, service pack 2.

largest value that can be entered into MS Excel field
 

You made me look that up!<bg
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Peo Sjoblom" wrote in message
...
The VALUE function is obsolete, if you need to calculate a text number
using an operator like + - * / the calculation itself will convert them
but there is no sense if you have more than 15 digits
I haven't found a single occasion where the VALUE function is more useful
than 1* or --
except maybe for pedagogical reasons


--


Regards,


Peo Sjoblom



"Kevin B" wrote in message
...
If the number are entered as text, anytime the values need to by used by
a
function the VALUE function can be used to convert the text value to
numeric
ones. However, you'll still be limited to 15 digits of precision
--
Kevin Backmann


"Bob I" wrote:

that is correct, there are 15 digits of precision in Excel.

Aaron Z wrote:

If I type in the following number into a field:
123,456,789,123,456,789
and hit enter, I get the following number in the field:
123,456,789,123,456,000

If I then take the second number, subtract 2 from it in another cell,
and
then do an exact function on the 2nd and 3rd (now 2 less than the
2nd), I get
a "TRUE" result.

I'm using Excel 2003, service pack 2.

largest value that can be entered into MS Excel field
 

Thanks everyone for the feedback. Looks like using text formating is the
answer for now.

"Dave Peterson" wrote:

Excel keeps track of 15 significant digits--no more.

So when you typed in that giant number, excel actually changed it what you saw.

And when you subtracted 2 from that 123,456,789,123,456,000 number, excel still
kept track of 15 digits. So it resulted in 123,456,789,123,456,000.



Aaron Z wrote:

If I type in the following number into a field:
123,456,789,123,456,789
and hit enter, I get the following number in the field:
123,456,789,123,456,000

If I then take the second number, subtract 2 from it in another cell, and
then do an exact function on the 2nd and 3rd (now 2 less than the 2nd), I get
a "TRUE" result.

I'm using Excel 2003, service pack 2.

--

Dave Peterson

largest value that can be entered into MS Excel field
 

Peo is a very good teacher!


RagDyer wrote:

You made me look that up!<bg
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Peo Sjoblom" wrote in message
...
The VALUE function is obsolete, if you need to calculate a text number
using an operator like + - * / the calculation itself will convert them
but there is no sense if you have more than 15 digits
I haven't found a single occasion where the VALUE function is more useful
than 1* or --
except maybe for pedagogical reasons


--


Regards,


Peo Sjoblom



"Kevin B" wrote in message
...
If the number are entered as text, anytime the values need to by used by
a
function the VALUE function can be used to convert the text value to
numeric
ones. However, you'll still be limited to 15 digits of precision
--
Kevin Backmann


"Bob I" wrote:

that is correct, there are 15 digits of precision in Excel.

Aaron Z wrote:

If I type in the following number into a field:
123,456,789,123,456,789
and hit enter, I get the following number in the field:
123,456,789,123,456,000

If I then take the second number, subtract 2 from it in another cell,
and
then do an exact function on the 2nd and 3rd (now 2 less than the
2nd), I get
a "TRUE" result.

I'm using Excel 2003, service pack 2.





--

Dave Peterson

largest value that can be entered into MS Excel field
 

Pertaining to Excel Pedagogy!<vbg
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Dave Peterson" wrote in message
...
Peo is a very good teacher!


RagDyer wrote:

You made me look that up!<bg
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Peo Sjoblom" wrote in message
...
The VALUE function is obsolete, if you need to calculate a text number
using an operator like + - * / the calculation itself will convert them
but there is no sense if you have more than 15 digits
I haven't found a single occasion where the VALUE function is more
useful
than 1* or --
except maybe for pedagogical reasons


--


Regards,


Peo Sjoblom



"Kevin B" wrote in message
...
If the number are entered as text, anytime the values need to by used
by
a
function the VALUE function can be used to convert the text value to
numeric
ones. However, you'll still be limited to 15 digits of precision
--
Kevin Backmann


"Bob I" wrote:

that is correct, there are 15 digits of precision in Excel.

Aaron Z wrote:

If I type in the following number into a field:
123,456,789,123,456,789
and hit enter, I get the following number in the field:
123,456,789,123,456,000

If I then take the second number, subtract 2 from it in another
cell,
and
then do an exact function on the 2nd and 3rd (now 2 less than the
2nd), I get
a "TRUE" result.

I'm using Excel 2003, service pack 2.





--

Dave Peterson

largest value that can be entered into MS Excel field
 

So I guess you're calling me pedagogical... I use it in Vlookup formulas to
convert text to value. I just kinda like that it documents the fact that I am
working with text.
--
HTH...

Jim Thomlinson


"Peo Sjoblom" wrote:

The VALUE function is obsolete, if you need to calculate a text number using
an operator like + - * / the calculation itself will convert them but there
is no sense if you have more than 15 digits
I haven't found a single occasion where the VALUE function is more useful
than 1* or --
except maybe for pedagogical reasons


--


Regards,


Peo Sjoblom



"Kevin B" wrote in message
...
If the number are entered as text, anytime the values need to by used by a
function the VALUE function can be used to convert the text value to
numeric
ones. However, you'll still be limited to 15 digits of precision
--
Kevin Backmann


"Bob I" wrote:

that is correct, there are 15 digits of precision in Excel.

Aaron Z wrote:

If I type in the following number into a field:
123,456,789,123,456,789
and hit enter, I get the following number in the field:
123,456,789,123,456,000

If I then take the second number, subtract 2 from it in another cell,
and
then do an exact function on the 2nd and 3rd (now 2 less than the 2nd),
I get
a "TRUE" result.

I'm using Excel 2003, service pack 2.

largest value that can be entered into MS Excel field
 

If macros are an option for you, give the function after my signature a try.
In the VBA editor, insert a module (Insert/Module from the menu bar) and
copy/paste the code after my signature into the Module's code window... you
will now be able to call this function from the spreadsheet just like any
other spreadsheet function.

The function limits your math operations to addition, subtraction,
multiplication and division, but it will allow you to handle inputted values
up to 28 significant digits long (whether there is a decimal point in the
number or not), so long as the calculated answer has no more than 28 total
digits (which should easily cover the range you seem to be talking about).
The numbers and mathematical operation for them are inputted in their
natural order... the first argument is the initial value; the second
argument is one of these symbols "+", "-", "*", "/" (which symbol goes with
which math operation should be obvious); and the third argument is the value
to which the operation will be applied. There is an optional fourth argument
which is used to determine whether the answer should be returned with commas
(thousands separators) or not (the default is FALSE, no commas). Oh, and
your **large** numbers (more than 15 significant digits) should always be
text (use a leading apostrophe to enter them into a cell that will be
referenced by the formula; otherwise use quotes around the number if it is
entered directly as an argument). The calculated answers will always be
returned as text (the assumption being if you used the function, you did so
with very large numbers).

Here is an example showing both methods of specifying the numerical
arguments. Put the indicated **text** number in cell A1...

A1: '123,456,789,123,456,789

Then put this formula in any other cell...

=BigMath(A1,"+","12345678912345678.12345", TRUE)

The cell with the formula will display 135,802,468,035,802,467.12345 for the
answer.

Rick


Function BigMath(Operation As String, Value1 As String, Value2 As String, _
Optional WithCommas As Boolean = False) As String
Dim X As Long
Dim Pattern As String
Dim Answer As Variant
Select Case Operation
Case "+"
Answer = CStr(CDec(Value1) + CDec(Value2))
Case "-"
Answer = CStr(CDec(Value1) - CDec(Value2))
Case "*"
Answer = CStr(CDec(Value1) * CDec(Value2))
Case "/"
Answer = CStr(CDec(Value1) / CDec(Value2))
Case Else
' Impossible calculation in order to force a #VALUE! error
Answer = 10 ^ 400
End Select
Pattern = "#"
If InStr(Answer, ".") Then
Pattern = Pattern & "." & String(Len(Answer) - InStr(Answer, "."), "#")
End If
If WithCommas Then Pattern = "#,##" & Pattern
BigMath = Format$(Answer, Pattern)
End Function


"Aaron Z" wrote in message
...
How do I get it to not change the number? I need it to retain what its
given, unmodified; and as importantly, display it (without having to first
change the cell to text format). It sounds like this isn't possible given
the 15-digit constraint.

Thanks for the quick reply.


"Dave Peterson" wrote:

Excel keeps track of 15 significant digits--no more.

So when you typed in that giant number, excel actually changed it what
you saw.

And when you subtracted 2 from that 123,456,789,123,456,000 number, excel
still
kept track of 15 digits. So it resulted in 123,456,789,123,456,000.



Aaron Z wrote:

If I type in the following number into a field:
123,456,789,123,456,789
and hit enter, I get the following number in the field:
123,456,789,123,456,000

If I then take the second number, subtract 2 from it in another cell,
and
then do an exact function on the 2nd and 3rd (now 2 less than the 2nd),
I get
a "TRUE" result.

I'm using Excel 2003, service pack 2.

--

Dave Peterson

largest value that can be entered into MS Excel field
 

That's a good enough pedagogical reason, I don't use it because it takes up
more space, same with DATEVALUE. <bg I just clamp down upon it off and on
just to let people know it is not really necessary




Peo


"Jim Thomlinson" wrote in message
...
So I guess you're calling me pedagogical... I use it in Vlookup formulas
to
convert text to value. I just kinda like that it documents the fact that I
am
working with text.
--
HTH...

Jim Thomlinson


"Peo Sjoblom" wrote:

The VALUE function is obsolete, if you need to calculate a text number
using
an operator like + - * / the calculation itself will convert them but
there
is no sense if you have more than 15 digits
I haven't found a single occasion where the VALUE function is more useful
than 1* or --
except maybe for pedagogical reasons


--


Regards,


Peo Sjoblom



"Kevin B" wrote in message
...
If the number are entered as text, anytime the values need to by used
by a
function the VALUE function can be used to convert the text value to
numeric
ones. However, you'll still be limited to 15 digits of precision
--
Kevin Backmann


"Bob I" wrote:

that is correct, there are 15 digits of precision in Excel.

Aaron Z wrote:

If I type in the following number into a field:
123,456,789,123,456,789
and hit enter, I get the following number in the field:
123,456,789,123,456,000

If I then take the second number, subtract 2 from it in another
cell,
and
then do an exact function on the 2nd and 3rd (now 2 less than the
2nd),
I get
a "TRUE" result.

I'm using Excel 2003, service pack 2.