largest value that can be entered into MS Excel field

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
 
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.
 
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
 
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
 
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
 
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.
 
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
 
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
 
Back
Top