Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
maximum number size/significant digits
I seem to have hit a limit of 16 significant digits. For example:
1E+15 + 1 = 1000000000000001 (16 significant digits) (note that 1000000000000000 is displayed but the 1's place value is still retained) whereas 1E+16 + 1 = 10000000000000000. The loss of significant digits can be verified by resubtracting the the initial large number, e.i.: 1E+15 + 1 - 1+E15 = 1, whereas 1E+16 + 1 - 1+E16 = 0. Is there a way of getting past this limitation? I need more than 40 significant digits for some rather special calculations. I have created a workaround which cuts numbers into up to 3 15-significant-digit pieces, but it's inelegant and requires very long formulas. Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
maximum number size/significant digits
XL has a specification limit of 15 decimal digits of precision (see
Help, "Specifications"). There's nothing you can to in XL itself to extend the precision. There are a few add-ins around that you can Google that claim to give up to 200 decimal digits of precision, but I haven't tried any of them. In article , "noel" wrote: I seem to have hit a limit of 16 significant digits. For example: 1E+15 + 1 = 1000000000000001 (16 significant digits) (note that 1000000000000000 is displayed but the 1's place value is still retained) whereas 1E+16 + 1 = 10000000000000000. The loss of significant digits can be verified by resubtracting the the initial large number, e.i.: 1E+15 + 1 - 1+E15 = 1, whereas 1E+16 + 1 - 1+E16 = 0. Is there a way of getting past this limitation? I need more than 40 significant digits for some rather special calculations. I have created a workaround which cuts numbers into up to 3 15-significant-digit pieces, but it's inelegant and requires very long formulas. Thanks. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
maximum number size/significant digits
xlPrecision for MS Excel provides up to 32,767 significant digits. You
can download the free edition here and us it as long as you wish: http://PrecisionCalc.com Thanks, Greg Lovern http://PrecisionCalc.com More Power In Excel JE McGimpsey wrote: XL has a specification limit of 15 decimal digits of precision (see Help, "Specifications"). There's nothing you can to in XL itself to extend the precision. There are a few add-ins around that you can Google that claim to give up to 200 decimal digits of precision, but I haven't tried any of them. In article , "noel" wrote: I seem to have hit a limit of 16 significant digits. For example: 1E+15 + 1 = 1000000000000001 (16 significant digits) (note that 1000000000000000 is displayed but the 1's place value is still retained) whereas 1E+16 + 1 = 10000000000000000. The loss of significant digits can be verified by resubtracting the the initial large number, e.i.: 1E+15 + 1 - 1+E15 = 1, whereas 1E+16 + 1 - 1+E16 = 0. Is there a way of getting past this limitation? I need more than 40 significant digits for some rather special calculations. I have created a workaround which cuts numbers into up to 3 15-significant-digit pieces, but it's inelegant and requires very long formulas. Thanks. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
maximum number size/significant digits
Unfortunately, this is for a business app, so the solution has to work in all
machines in an international company and can't be just a locally installed improvement to Excel. Maybe I can show you the situation and you can propose a solution. There's a table of functions (up to 50 for the moment) and assiated roles (approximately 80 for the moment). These are from SAP, in case that helps. For example (greatly simplified): roles F1 a b c d F2 b h F3 d F4 a d F5 c e h no role appears more than once for a single function (so never F1 a a), but there is otherwise no limitation to the number of roles a function can be assigned nor is there a limit to the number of times a role can be assigned. I need to create a sheet that inverses the data, placing roles in the first column (manually entered) and automaticaly finds all the functions having the associated role. In this case: a F1 F4 b F1 c F1 F5 d F1 F3 F4 e F5 f g h F2 F5 My solution is rather complex and involved, and can currently handle a maximum of 45 functions and an unlimited number of roles. So as not to bias any possible suggestions, I'm not going to post my solution and just see what anyone can come up with. Thanks in advance. Noel " wrote: xlPrecision for MS Excel provides up to 32,767 significant digits. You can download the free edition here and us it as long as you wish: http://PrecisionCalc.com Thanks, Greg Lovern http://PrecisionCalc.com More Power In Excel JE McGimpsey wrote: XL has a specification limit of 15 decimal digits of precision (see Help, "Specifications"). There's nothing you can to in XL itself to extend the precision. There are a few add-ins around that you can Google that claim to give up to 200 decimal digits of precision, but I haven't tried any of them. In article , "noel" wrote: I seem to have hit a limit of 16 significant digits. For example: 1E+15 + 1 = 1000000000000001 (16 significant digits) (note that 1000000000000000 is displayed but the 1's place value is still retained) whereas 1E+16 + 1 = 10000000000000000. The loss of significant digits can be verified by resubtracting the the initial large number, e.i.: 1E+15 + 1 - 1+E15 = 1, whereas 1E+16 + 1 - 1+E16 = 0. Is there a way of getting past this limitation? I need more than 40 significant digits for some rather special calculations. I have created a workaround which cuts numbers into up to 3 15-significant-digit pieces, but it's inelegant and requires very long formulas. Thanks. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
maximum number size/significant digits
Noel,
I have a slight guess as to why you want high precision for this task. Anyway, my solution is very different. It is based on a formula I saw from Bob Phillips, to find the collection of unique entries in a dataset. It assumes that you have five functions in cells A1:A5. It assumes you have a maximum of 4 roles per function (this will work for up to 255 roles, as this hits the limit of columns). The roles are placed next to the function, exactly as shown in your example, hence I assume that the output cells start from G1. I.e. Column G:G will contain a, b, c,... and subsequent columns, starting from H1, will have the function codes. In H1: (array formula, needs to be entered with Shift+Ctrl+Enter) =IF(COUNTIF($B$1:$E$5,$G1)=0,"",INDEX($A$1:$A$5,MI N(IF(COUNTIF(OFFSET($B$1:$E$1,ROW($A$1:$A$5)-1,0),$G1)0,ROW($A$1:$A$5),1000)))) Copy down H1 as far next to the roles as necessary. In I1: (also array formula) =IF(H1="","",IF(ISERROR(MATCH(1,IF((COUNTIF(OFFSET ($B$1:$E$1,ROW($A$1:$A$5)-1,0),$G1)0)*(COUNTIF($H1:H1,$A$1:$A$5)=0),1,0),0) ),"",INDEX($A$1:$A$5,MATCH(1,IF((COUNTIF(OFFSET($B $1:$E$1,ROW($A$1:$A$5)-1,0),$G1)0)*(COUNTIF($H1:H1,$A$1:$A$5)=0),1,0),0) ))) Copy down and accross I1 as far as necessary. What to change: $A$1:$A$5 should be changed to whatever the length of your function codes in the input table. $B$1:$E$1 should be changed to a horizontal range wide enough to hold the various roles for a function. If you do the restructuring in another sheet it can be as wide as $B$1:$IV$1. HTH Kostis Vezerides |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
What is the maximum number of rows in an Excel spreadsheet ? | Excel Discussion (Misc queries) | |||
help with removing digits from a number | Excel Discussion (Misc queries) | |||
Maximum Number of Cell Formats | Excel Discussion (Misc queries) | |||
Least number of digits in Y-axis labels | Charts and Charting in Excel | |||
How do I format cells to a specific number of digits? | Excel Discussion (Misc queries) |