Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function to find 'n'th largest alphanumeric field (like "Large") | Excel Worksheet Functions | |||
LArgest spreadsheet under Excel - Rows | Excel Discussion (Misc queries) | |||
How to check for and replace the largest number in excel | Excel Worksheet Functions | |||
How do I set up excel to tab from field to field in a form? | Excel Discussion (Misc queries) | |||
How to Join/concatenate a date field with a time field in Excel? | Excel Discussion (Misc queries) |