maximum number size/significant digits

[noel]

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.

 

[JE McGimpsey]

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.

 

[gregl]

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

 

[noel]

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

 

[vezerid]

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,MIN(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