xl
Precision
Get Your Numbers Right
(When xlPrecision is installed, this tutorial is also available from within Excel by choosing Tools | xlPrecision | xlPrecision Help and Function Reference.)
Tutorial
(This is a detailed, in-depth tutorial. For a quicker start, you may prefer to begin with the Quick-Start Tutorial.)
This tutorial illustrates the three fundamental Excel limitations that xlPrecision solves, and how to use xlPrecision to get around those limitations.
First, to see the limitations that xlPrecision solves:
Create a new workbook in Excel.
In cell A1, enter "67" (without quotes).
In cell A2, enter "89" (without quotes).
In cell A3, enter "=A1/A2" (without quotes).
Right-click cell A3 and choose Format Cells.
On the Number tab, in the Category listbox, select Number.
In the Decimal Places spinner, enter the maximum (30), and click OK.
Widen column A as much as necessary to see the number.
Note that cell A3 shows:
0.752808988764045000000000000000
The correct answer would continue infinitely. For example, here are just the first 30 correct significant digits:
0.752808988764044943820224719101
In addition to being unable to return numbers with more than 15 significant digits, Excel is unable to accept operands with more than 15 significant digits. For example:
In cell A4, enter "123456789123456789" (without quotes).
Right-click cell A4 and choose Format Cells.
On the Number tab, in the Category listbox, select Number.
(Optional) In the Decimal Places spinner, enter 0, and click OK.
Widen column A as much as necessary to see the number.
Note that cell A4 shows:
123456789123456000
Excel truncated the number to 123456789123456000 -- it didn't even round it correctly to 123456789123457000.
Create a new workbook in Excel.
In cell A1, enter "1000.8" (without quotes).
In cell A2, enter "1000" (without quotes).
In cell A3, enter "=A1-A2" (without quotes).
Note that cell A3 shows "0.8", which is correct. But things are not as they seem. Excel is showing a slightly different number than it is actually storing in cell A3. To see what number Excel is actually storing in cell A3, continue with the following steps.
Right-click cell A3 and choose Format Cells.
On the Number tab, in the Category listbox, select Number.
In the Decimal places spinner, increase decimal places to at least 15. Click OK.
Note that cell A3 now shows "0.799999999999955".
0.799999999999955 is the number Excel is actually storing in A3, and that is the number that any formula referencing cell A3 will be given to use. It's very close to 0.8. But with enough repetition, as with summing a large range of numbers that each have a similar small inaccuracy, the total discrepancy can become large.
Create a new workbook in Excel.
In cell A1, enter "9E+307" (without quotes).
In cell A2, enter "1" (without quotes).
In cell A3, enter "=A1*A2" (without quotes).
Note that cell A3 returns "9.00E+307", indicating that the operation was successful. This shows that Excel is able to work with numbers as large as 9E+307 (9 with 307 zeroes after it).
Now, change cell A2 from "1" to "10" (without quotes).
Note that cell A3 now returns the error value "#NUM!". This shows that Excel is unable to work with numbers as large as 9E+308 (9 with 308 zeroes after it).
The same is true -- except worse! -- for very small numbers:
In cell B1, enter "1E-307" (without quotes).
In cell B2, enter "1" (without quotes).
In cell B3, enter "=B1/B2" (without quotes).
Note that cell A3 returns "1.00E-307", indicating that the operation was successful. This shows that Excel is able to work with numbers as small as 1E-307 (a decimal point, then 306 zeroes, then a 1).
Now, change cell B2 from "1" to "10" (without quotes).
Note that cell B3 now returns "0.00E+00", indicating that as far as Excel is concerned, 1E-308 is actually equal to zero! It would be far better for Excel to return the error value "#NUM!", correctly indicating that it is unable to work with numbers that small.
Note that no matter how large you make the number in cell B2, B3 continues to return "0.00E+00", indicating that Excel still considers it equal to zero.
xlPrecision provides high precision in Excel by replacing Excel's low precision arithmetic operators (+, - *, /, ^), comparison operators, (>, >=, <, <=, =), and some of its worksheet functions (SUM, MEDIAN, RANK, and many others), with custom worksheet functions, such as xlpADD, xlpMULTIPLY, xlpISLESS, xlpMEDIAN, etc. For a complete list of xlPrecision functions, see the Function Reference.
Unfortunately, Excel does not allow changing the behavior of its built-in operators and worksheet functions, so it is not possible for xlPrecision to apply high precision to your existing worksheet formulas. Using xlPrecision requires modifying your worksheet formulas by replacing Excel's built-in operators with xlPrecision's custom worksheet functions. Of course, you only have to modify those formulas from which you want high precision.
For example, to get high precision from this worksheet formula:
=2/3
Change the formula to this:
=xlpDIVIDE(2,3)
Be sure to put a comma (",") between the 2 and the 3, not a slash ("/").
Depending on your world region and language, you might need to use a different character than the comma. Use whatever character you normally use to separate arguments in Excel functions. In many locales, that character is a semi-colon (;). If you're not sure, go into Windows' Regional/Language Options Control Panel and find your "List Separator" character. Use that character wherever xlPrecision documentation shows a comma.
The result should be as long as your edition of xlPrecision allows, or the default length of 100 significant digits, whichever is shorter. For example, if you are using the 35SD edition, the result should be 35 significant digits long, like this:
0.66666666666666666666666666666666667
xlPrecision results are returned as text that look like numbers, not as values that Excel recognizes as numbers. This is because Excel would immediately truncate the results to 15 significant digits if it recognized them as numbers.
You can use the results of xlPrecision formulas as the operands in other xlPrecision formulas, but using them as operands in Excel's arithmetic functions will truncate them to 15 significant digits.
The number of significant digits xlPrecision provides depends on the edition of xlPrecision you have. For example, the free edition provides 150 significant digits. Paid versions provide up to 32,767 significant digits. To determine how many significant digits your edition of xlPrecision provides, go to Excel's Tools menu and choose xlPrecision | About xlPrecision. One of the lines on the About box that pops up starts with "SD Edition:" and indicates the maximum number of significant digits your edition of xlPrecision provides.
Another way to determine how many significant digits your edition of xlPrecision provides is to enter "=xlpSD()" in any cell (without quotes, and xlpSD is not case-sensitive). The number returned is the maximum number of significant digits your edition of xlPrecision provides.
If you don't specify how many significant digits you want, you'll get either 100 or 5 less than the maximum allowed by your xlPrecision edition, whichever is less.
You can specify any number of significant digits up to 32,767. For example, to get 1,000 significant digits, change the formula to this:
=xlpDIVIDE(2,3,,,,,1000)
If you specify more significant digits than your xlPrecision edition allows, then you'll get the maximum allowed by your edition.
Alternatively, you can specify fewer significant digits to speed up calculation.
Depending on how many significant digits your xlPrecision formula returns, the result may be too long to conveniently view. You can view the full result by right-clicking the cell and choosing Format Cells | Alignment | Wrap Text, and widening the column to the width of the screen. To view the full result without changing column widths or wrapping text, copy the cell and paste into Notepad or a word processor.
xlpADD(), xlpSUBTRACT(), xlpMULTIPLY(), xlpROOT(), and xlpPOWER() all work essentially the same way as xlpDIVIDE():
|
Change this: |
To this: |
| =2+3 | =xlpADD(2,3) |
| =2-3 | =xlpSUBTRACT(2,3) |
| =2*3 | =xlpMULTIPLY(2,3) |
| =2/3 | =xlpDIVIDE(2,3) |
| =2^(1/3) | =xlpROOT(2,3) |
| =2^3 | =xlpPOWER(2,3) |
The comparison operators are similar too:
|
Change this: |
To this: |
| =2=3 | =xlpISEQUAL(2,3) |
| =2<>3 | =xlpISNOTEQUAL(2,3) |
| =2>3 | =xlpISGREATER(2,3) |
| =2>=3 | =xlpISGREATEROREQUAL(2,3) |
| =2<3 | =xlpISLESS(2,3) |
| =2<=3 | =xlpISLESSOREQUAL(2,3) |
If any of the xlPrecision functions return "#VALUE!", make sure you are separating the operands with a comma, not an arithmetic operator. For example, if you enter "=xlpADD(A1+A2)" instead of "=xlpADD(A1,A2)", it will return "#VALUE!".
For a complete list of xlPrecision functions, along with detailed information on using them and examples, see the Function Reference.
So far in this tutorial, the numbers in cells A1 and A2 are entered in Excel as numbers. But if you use operands longer than 15 significant digits, Excel will truncate them to 15 significant digits. For example:
In cell A1, change the number 67 to 12345678901234567890.
Right-click cell A1 and choose Format Cells.
On the Number tab, in the Category listbox, select Number.
In the Decimal Places spinner, enter the maximum (30), and click OK.
Note that Excel truncated the number to 12345678901234500000 -- it didn't even round it correctly to 12345678901234600000.
xlPrecision is capable of using operands with up to 32,767 significant digits. But to prevent Excel from truncating them to 15 significant digits, you'll need to enter them as text instead of as a number:
Right-click cell A1 and choose Format Cells,
On the Number tab, in the Category listbox, select Text (not Number).
In cell A1, change the number 12345678901234500000 to 12345678901234567890.
Note that this time, Excel did not change it. That will work for xlPrecision functions, but note that if you use it in a non-xlPrecision formula, Excel will truncate the number again.
Alternatively, you can enter long operands within the formula by enclosing them with double-quotes so that Excel treats them as text rather than as numbers. For example, change the above formula to this:
=xlpADD("123456789123456789123456789","123456789123456789123456789")
By default, xlPrecision formats negative results with a leading hyphen ("-"). On all editions except for the 25 SD edition, you can optionally format negatives with parentheses ("()"):
In cell C4, change the formula "=xlpSUBTRACT(A1,A2)" to "=xlpSUBTRACT(A1,A2,2)".
Note that the return value changes from "-86419753208641975320" to "(86419753208641975320)".
The 3rd argument is "format_negative". The only valid values for format_negative are 1 and 2. Entering 1 formats negatives with a leading hyphen. Any values entered other than 1 or 2 are ignored, and negatives will be formatted with a leading hyphen.
format_negative is ignored in the 25 SD edition, which always formats negatives with a leading hyphen.
Negatives formatted with parentheses can be used as operands in other xlPrecision formulas, as well as negatives formatted with leading hyphens.
Some xlPrecision functions use different syntax than this for formatting negatives. See the Function Reference entries for each xlPrecision function.
By default, xlPrecision does not add thousands separators such as the comma in "1,000". On all editions except for the 25 SD and 35 SD editions, you can optionally add them:
In cell C4, change the formula "=xlpSUBTRACT(A1,A2,2)" to "=xlpSUBTRACT(A1,A2,2,TRUE)".
Note that the return value changes from "(86419753208641975320)" to "(86,419,753,208,641,975,320)".
The 4th argument is "format_thousands". format_thousands is ignored in the 25 SD and 35 SD editions, which never return thousands separators.
The thousands separator generated by format_thousands is internationalized. This means that the local thousands separator will be used -- for example, a comma in the USA, a period in Germany, and a space in France. Note, the decimal symbol is also internationalized -- for example, a period in the USA, a comma in Germany and France.
Results formatted with thousands separators can still be used as operands in other xlPrecision formulas.
Some xlPrecision functions use different syntax than this for formatting thousands separators. See the Function Reference entries for each xlPrecision function.
On all editions except for the 25 SD edition, results can optionally be formatted with the local currency symbol (for example, "$" in the USA):
In cell C4, change the formula "=xlpSUBTRACT(A1,A2,2,TRUE)" to "=xlpSUBTRACT(A1,A2,2,TRUE,TRUE)".
The 5th argument is "format_currency". format_currency is ignored in the 25 SD edition.
Results formatted with the local currency symbol can still be used as operands in other xlPrecision formulas.
Some xlPrecision functions use different syntax than this for formatting currency. See the Function Reference entries for each xlPrecision function.
On all editions except for the 25 SD edition, results can optionally be formatted with the local currency symbol (for example, "$" in the USA):
In cell C4, change the formula "=xlpSUBTRACT(A1,A2,2,TRUE)" to "=xlpSUBTRACT(A1,A2,2,TRUE,TRUE)".
The 5th argument is "format_currency". format_currency is ignored in the 25 SD edition.
Results formatted with the local currency symbol can still be used as operands in other xlPrecision formulas.
Some xlPrecision functions use different syntax than this for formatting currency. See the Function Reference entries for each xlPrecision function.
If you don't specify how many significant digits to return, you'll get 100, or less if your edition of xlPrecision only allows fewer than that. You can get faster calculations by specifying a lower number of significant digits. For example, this formula specifies 50 significant digits:
=xlpDIVIDE(2,3,,,,,50)
On editions that allow more than 100 significant digits, xlPrecision prevents inadvertently causing long calculations by defaulting to 100 significant digits unless more are specified in the formula. Editions that do not allow more than 100 significant digits default to 5 less than the maximum allowed. For example, the 25 SD edition defaults to 20 significant digits. To get more digits, specify the amount in the maximum_significant_digits argument. For example, this formula specifies 500 significant digits:
=xlpDIVIDE(2,3,,,,,500)
If you specify more significant digits than your xlPrecision edition allows, you'll get the maximum allowed by your edition.
Microsoft Excel 97 and later allows up to 32,767 text characters in a cell, and Excel 2007 can display and print all of them.
However, Excel 2003 and earlier can display and print only the first 1,000 characters in a cell. To view or print an entire xlPrecision return value longer than 1,000 characters in Excel 2003 or earlier, do any of the following:
Copy the cell, and Paste Special | Values to another cell. Then, with the new cell selected, look in the formula bar. The formula bar displays the entire result (up to 32,767 characters), though you may need to scroll down within the formula bar to see it all.
Copy the cell and paste into Notepad or a word processor.
Using the Text Box tool on Excel's Drawing toolbar, draw a textbox on the worksheet. Select the cell and Copy | Paste Special | Values to another cell. With the new cell selected, click in the formula bar and select the entire contents. Copy the contents, and paste into the textbox.
Although Excel 2003 and earlier displays and prints only the first 1,000 characters in the cell, it makes all the characters available to formulas in other cells, including xlPrecision formulas. The display of only 1,000 characters does not indicate any loss of precision in xlPrecision formulas.
To display and print all 32,767 characters in a cell in Excel 2007, you'll need to make the font size very tiny, in addition to maximizing the column width and row height. In some cases you may prefer some of the above workarounds even in Excel 2007.
The 32,767 SD edition is only able to return 32,767 significant digits when the result is a positive integer not formatted with either thousands separators or currency symbols. This is because Excel only allows a maximum of 32,767 characters in a cell. If one of those characters is a decimal, then only 32,766 characters are left over for digits. The same is true of currency symbols and negative formatting. If the number is smaller than 1, then zeroes between the decimal and the first significant digit will also reduce the available number of significant digits. And of course, thousands separators can reduce it by up to 25%.
In all cases, the 32,767 SD edition will give you as many significant digits as possible with the formatting you have chosen.
If the return value is so large that it has more than 32,767 characters to the left of the decimal, then xlPrecision is of course unable to return a correct value and instead returns "#VALUE!". Note, that's a vastly larger number than Excel can return without xlPrecision. Excel itself can only return or recognize a number with a maximum of 308 digits to the left of the decimal.
If you are using the 1,500 SD or higher edition, or the Free Edition, of xlPrecision, you can use Excel's Insert Function dialog to insert xlPrecision functions more conveniently than typing them in directly:
Using the Insert Function dialog with xlPrecision functions requires the 1,500 SD or higher edition, or the Free Edition, of xlPrecision. Not recommended with the Free Edition as it causes the Free Edition dialog to appear multiple times.
Using Excel's Insert Function Wizard can be slow because Excel recalculates the function every time a digit or character is entered in the Wizard. To speed up that process, enter "0" for all numeric operands (such as num and rt for xlpROOT), so that the function does not take long to calculate. Then, when done with the Wizard, manually change the numeric operands to the desired numbers directly in the worksheet.
An interesting way to compare xlPrecision with Excel is to look at how they calculate factorials. A factorial is the number resulting from multiplying a whole number by every whole number between itself and 1, inclusive. For example, the factorial of 5 is:
1 x 2 x 3 x 4 x 5 = 120
Factorials are symbolized with an exclamation point ("!"). For example, the factorial of 5 is represented as "5!". An example of how factorials are used is to determine the total possible number of ways a given number of items can be arranged in a row. For example, the factorial of a standard deck of playing cards is 52!.
Without xlPrecision, Excel can calculate factorials up to 170!. But it can only calculate factorials at full precision up to 20! -- after that, Excel rounds them to the first 15 digits. xlPrecision can calculate factorials up to 9,273! -- all at full precision.
To see it for yourself:
Create a new workbook in Excel.
In cell A1, enter "=ROW()" (without quotes).
Copy cell A1 down through A200.
In cells B1 and C1, enter "1" (without quotes).
In cell B2, enter "=xlpMULTIPLY(B1,A2)" (without quotes).
In cell C2, enter "=C1*A2" (without quotes).
Copy cells B2 and C2 down to B200 and C200. (But note, if you're using the Free Edition, you'll need to click OK once per row, so you might want to start out with fewer rows.)
Select column C, and choose Format | Cells | Number tab. In the Category dropdown listbox, select Number. In the Decimal Places spinner, select 0.
Widen columns B and C so that you can see as much of them both together as possible.
Note that on rows 1 - 20, Excel (column C) and xlPrecision (column B) are identical.
Note that cells B21 and C21 are different. In C21, excel has rounded it down. That's because 21! has 16 non-zero digits, and Excel can only give you the first 15 of them. xlPrecision, in cell B21, gives you all 16 non-zero digits.
Note that at row 171, Excel (column C) starts returning "#NUM!". That's because Excel can't recognize a number that large.
xlPrecision can return factorials down through row 9,273, though the precision depends on the xlPrecision edition. The 32,767 SD edition can return 9,273! at full precision, and the 1,500 SD edition can return 693! at full precision. The 35 SD and 25 SD editions can return 36! and 29! respectively at full precision, which may not sound like much but are still better than Excel's 20! at full precision.
Getting back to the deck of playing cards (52!), Excel (cell C52), rounding 52! to 15 significant digits, returns:
80,658,175,170,943,900,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000,000
xlPrecision (cell B52), returning 52! at full precision, returns:
80,658,175,170,943,878,571,660,636,856,403,766,975,289,505,440,883,277,824,000,000,000,000
(That number is precisely correct -- the final 12 zeroes are not due to rounding.)
That's a difference of this many possible arrangements of playing cards!
21,428,339,363,143,596,233,024,710,494,559,116,722,176,000,000,000,000
xlPrecision offers many additional features that take you beyond Excel's capabilities in other ways. See Data Control & Analysis Features.
Find xlPrecision's online help by choosing Tools > xlPrecision 2.0 > xlPrecision 2.0 Help and Function Reference:
For helpful tips on using xlPrecision, see:
For more detail on usage and syntax, please see:
For help on troubleshooting common problems, see: