Get Your Numbers Right
I'm trying to install xlPrecision, but I'm getting weird error messages.
On Windows NT, 2000, XP and Vista, you must be logged in as an Administrator during installation.
On Windows Vista, start Setup by right-clicking xlPrecision_2.0.exe and choosing "Run as administrator".
After installation, you no longer need to be logged in as an Administrator.
I'm trying to install xlPrecision, and I'm getting the error message:
Compile error in hidden module: mdlAddin. This error commonly occurs when code is incompatible with the version, platform, or architecture of this application.
This error may occur if you try to install or use xlPrecision with 64-bit Excel. xlPrecision is not yet compatible with 64-bit Excel. For more information see xlPrecision and 65-Bit Excel.
I'm trying to install xlPrecision, and I'm getting the error message:
Setup cannot install system files or update shared files if they are in use. Before proceeding, we recommend that you close any applications you may be running.
Reboot the computer, and install xlPrecision before running anything else.
If that doesn't work, try these steps:
Go to Start | Run, type in "msconfig" (without quotes), and press Enter. That should bring up Windows XP's System Configuration Utility.
I'm trying to install xlPrecision, and I'm getting the error message:
Setup cannot continue because some system files are out of date on your system. Click OK if you would like setup to update these files for you now. You will need to restart Windows before you can run setup again. Click cancel to exit setup without updating system files.
However, even though I click OK and it reboots, it always comes back to the same error message again.
There are various reasons why this could happen. To fix the problem, I suggest starting with the steps in the Microsoft Knowledge Base article Q191096. If that doesn't resolve the problem, let me know and I'll see what else we can do.
One person who encountered this problem on Windows 2000 resolved it with these steps:
- Boot into safe mode to bypass logon script and antivirus stuff.
- Set the temp directories through the window environment manager (not at command line like instructions in Q191096 say).
- Looked for and/or deleted the *.cab and setup1.exe in winnt directory.
- Then I tested in safe mode with xlptest() it was ok.
- When I booted into normal configuration I had to goto tools> addins > browse for the xlprecision.xla etc etc.
- Working now, and I look forward to playing with it.
If that doesn't work, you might want to try installing it manually.
I'm trying to install xlPrecision, and I'm getting the error message:
[CiceroUIWndFrame: Setup1.exe - Entry Point Not Found]
The procedure entry point _except_handler4_common could not be located in the dynamic link library msvcrt.dll.
It looks like some program might have
replaced your msvcrt.dll file with one that does not have all the required
To resolve the problem, please follow the steps in the Microsoft Knowledge Base article Q324762. This article is about a different missing function in the same file, but the idea is the same.
If that doesn't help, if you're on Windows XP, check for and rename the file \Windows\System32\dwmapi.dll (or if you're on 64-bit Windows XP, it's at \Windows\SysWOW64\dwmapi.dll). This is a Windows Vista file that some install programs incorrectly install in Windows XP.
I've tried everything I can think of, and I'm still not able to install xlPrecision.
You might want to try installing it manually.
When I start Excel, I get the error message:
Can't find where xlPrecision.dll is registered. xlPrecision worksheet functions will not work. Please reinstall xlPrecision.
Download RegisterDLL.exe to your xlPrecision folder, and use it to register xlPrecision.dll. In RegisterDLL.exe, click Browse to select xlPrecision.dll, then click Register to register it. Then click Close.
I'm evaluating the Free Edition. The xlpTEST() function returns "#NAME!".
In Excel's list of add-ins, make sure xlPrecision is listed and checked.
In Excel 2003, see Tools | Add-ins. In Excel
2007, see Office button | Excel options | Add-ins | Manage: Excel Add-ins | Go.
If xlPrecision is listed there but not checked, check it on. If it is not listed, click Browse and browse to it, and then make sure it is checked on.
xlPrecision works fine when I manually start Excel and use it. But when I automate Excel from another program (for example, by using the CreateObject function), xlPrecision functions return "#NAME!".
When you start Excel by automation (such as with CreateObject or GetObject), Excel’s add-ins are not loaded, even though they remain checked in the Add-ins dialog.
For information on manually loading add-ins when automating Excel, see:
The workaround in that article requires knowing the path to xlPrecision.xla. If you’re not sure of the path to xlPrecision.xla on the target computer, you can find it in the registry.
Alternatively, you could shell out to Excel, which should automatically load all installed add-ins, then use GetObject to get a reference to it.
I'm evaluating the Free Edition. The xlpTEST() function works correctly, but other functions return error messages.
You probably need to use a different character than the comma (,) to separate the arguments. Use whatever character you normally use to separate arguments in Excel functions. In many areas of the world, that character is a semi-colon (;).
Try entering this formula:
Does it return an error? What about "=SUM(1;1)"? Whatever character works with Excel's built-in SUM function is the character you'll need to use with xlPrecision functions.
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. This example shows that the list separator character for French is the semi-colon:
I'm evaluating the Free Edition. I want to test a series of formulas in 50 cells, but I don't want to take the time to enter all the text in the pop-up windows that would result.
Enter the formulas one at a time. After each entry, copy the cell and choose Paste Special | Values. When the pop-ups become annoying, restart Excel.
Alternatively, send me a sample worksheet and I'll run it for you. I'll Copy / Paste Special | Values all the xlPrecision formulas (so that you can see the results without having to calculate them on your machine), and return the worksheet to you.
I'm evaluating the Free Edition and I'm getting prompted with every keystroke!
That can happen if you're using the Free
Edition with Excel's Formula Wizard. As explained in the tutorials, that's not
recommended because Excel's Formula Wizard recalculates with every keystroke. If
you have several nested xlPrecision functions nested in the formula, this could
cause multiple prompts per keystroke.
If the Free Edition is used without the formula wizard, the prompts are not frequent. For details on the Free Edition's prompts, see the Free Edition information page.
The formula wizard works fine with the paid versions.
xlPrecision is working fine in Microsoft Excel, but I can't get it to work in Microsoft Works. How do I get xlPrecision to work in MS Works?
Unfortunately, MS Works does not make it possible to add custom worksheet functions, so it is not possible for xlPrecision to work in MS Works.
I paid for a paid version of xlPrecision several minutes ago. Where is it?
Turnaround time is normally a day or two. My payment processing company, PayPal, accepts your payment and sends me an email with your order. Once I see that email, I create your file and email it to you. If I'm at my day job at the time, I won't be able to send it until that evening.
I bought a paid version of xlPrecision, received it in email, and transferred it to my hard drive. But when I enter an xlPrecision formula, I get "#NAME!" instead of the correct return value.
The paid versions require the supporting files installed by the free edition. Please ensure that you have installed the free edition, and reinstall it if necessary. If the free edition isn't working, the paid versions won't be able to work either.
I installed the free edition of xlPrecision, but I still only have 15 significant digits of precision in Excel.
Using xlPrecision requires modifying your worksheet formulas by replacing Excel's built-in operators and Sum() function with xlPrecision's custom worksheet functions. For details please see the Quick Tutorial.
I installed the free edition of xlPrecision, but when I enter an xlPrecision formula, it returns "#NAME!" instead of the correct return value.
In Excel, choose Tools | Add-Ins and ensure that xlPrecision is listed and checked. If it is not listed, click Browse, browse to C:\Program Files\xlPrecision (or wherever you installed xlPrecision), and double-click on xlPrecision.xla.
If xlPrecision is listed and checked in Add-Ins, and it still doesn't work, reinstall the free edition of xlPrecision.
My anti-virus software wants to remove a file that I don't recognize. How do I know it isn't an xlPrecision file?
Here is a list of all the files xlPrecision 3.1 needs, in alphabetical order:
I installed xlPrecision and it's working fine in new formulas. But it doesn't give me higher precision in Excel's built-in functions, and in custom functions provided by another Excel Add-In I bought.
xlPrecision replaces mathematical operators
-- "+", "-", "*", "/", "^" -- with worksheet functions. Other worksheet functions,
whether built-in or provided by an add-in, do not use the xlPrecision worksheet
functions, and so do not gain precision from them.
If you're building a cell formula that uses other worksheet functions, and use xlPrecision functions instead of operators in the formula, then you gain precision between those other functions, but not within them.
It's possible for add-in worksheet functions to use xlPrecision instead of operators. xlPrecision, once installed, includes a DLL that can be called by other programming code, including the programming code that is used to build add-in worksheet functions. If that code uses xlPrecision functions instead of mathematical operators, it can gain precision. Unless, of course, that same code also calls other functions that, in turn, use mathematical operators, at which point the extra precision is discarded.
Microsoft is unlikely to ever be interested in using xlPrecision to add high precision to Excel's built-in formulas. However, in a future version of xlPrecision I plan to duplicate all of the relevant built-in Excel functions with equivalent high-precision xlPrecision functions.
If you want high precision in custom functions provided by another Excel Add-In, you might want to contact the authors of that other Add-In and suggest that they use xlPrecision in a future version. For information on how the programming code that is used to build add-in worksheet functions can call xlPrecision functions to gain precision, click here.
xlPrecision works correctly in a simple formula. But when I use it in a larger formula, I don't get the high precision. For example, this works correctly:
But this does not:
In this example, Excel discards the high precision returned by xlpDIVIDE when it evaluates the subtraction with Excel's built-in operator, the minus sign.
To maintain high precision in this example, the minus sign must be replaced by xlpSUBTRACT, like this:
Wherever you use a built-in arithmetic operator ("+", "-", "*", "/", or "^") or comparison operator, (">", ">=", "<", "<=", "<>", or "="), or a built-in worksheet function that does any arithmetic or any numeric comparison, the first thing Excel does is truncate the precision down to 15 significant digits. To maintain high precision, replace them in your formula with xlPrecision functions.
When I enter this formula:
It returns "1.999999999999999999999999999999", as expected. But when I try to reduce precision to 20 significant digits, like this:
It just returns "2". What happened to the high precision?
That's actually the correct high-precision result. If we limit 1.999999999999999999999999999999 to 20 significant digits, 2.0000000000000000000 is closer to 1.999999999999999999999999999999 than 1.9999999999999999999 would be. 2.0000000000000000000 is the closest that 20 significant digits can get to 1.999999999999999999999999999999. Of course, 2.0000000000000000000 is exactly the same as simply "2".
You can see the high precision if you change the formula to this:
That formula returns 1.8888888888888888889, which again is the closest that 20 significant digits can get to 1.888888888888888888888888888888.
When I multiply two very large numbers, it returns "#VALUE!".
If the return value, including formatting options, has more than 32,767 digits and other characters to the left of the decimal, xlPrecision can only return a correct answer in exponential notation. Try changing those formulas to format their return value in exponential notation. For example, change this:
Note, there are 4 (four) commas between the "B1" and the "1" in that example.
I get a maximum of only 100 digits from xlPrecision formulas. My xlPrecision edition should allow more than 100.
xlPrecision functions default to 100 significant digits if not specified in the maximum_significant_digits argument of the function. To get more than 100 digits with an xlPrecision edition that allows more, enter a higher number in the maximum_significant_digits argument. For example, to specify 2,000 significant digits on an xlPrecision edition that allows that many, change this:
Note, there are 5 (five) commas between the "B1" and the "2000" in that example. Some functions would require a different number of commas there; please see the function reference page for the function you're using.
Starting with xlPrecision 3.1, you can customize the default maximum significant digits by going to the xlPrecision About box and clicking the Set Default Max SD button:
I get a maximum of only about 1,000 digits from xlPrecision formulas. My xlPrecision edition should allow more than 1,000.
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 normally displays only the first 1,024 characters in a cell. To view an xlPrecision return value longer than 1,024 characters, turn on "Wrap Text" for the cell:
After turning on Wrap Text, you'll probably also need to widen the column and make the row height taller, and maybe make the font size smaller.
However, although turning on Wrap Text will display more than 1,024 characters, in Excel versions earlier than 2007 it won't display 32,767 characters. The exact number of characters it will display varies depending on your configuration. To view all 32,767 characters, you can 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.
Save the file as a text file type (Save As | Save as type = "text (tab delimited)"), and view the text file in 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.
Also, in Excel 2007 and later, you can view up to 8,192 characters in the formula bar by selecting the formula in the formula bar, and pressing F9 (but don't press Enter!). When done, be sure to press Escape to bring back the formula. If you inadvertently press Enter, try Undo (Ctrl + z) to bring back the formula.
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.
Although Excel normally displays and prints only the first 1,024 characters in the cell, it makes all the characters (up to 32,767 characters) available to formulas in other cells, including xlPrecision formulas. The display of only 1,024 characters does not indicate any loss of precision in xlPrecision formulas.
I'm trying to enter a long formula with xlPrecision functions, and I get a message that the formula contains an error. When I click OK, one of the xlPrecision functions is highlighted. The same formula with Excel's built-in operators (instead of xlPrecision functions) works fine. If I calculate each function individually in the formula bar with F9, each one calculates correctly, and I get the correct final result.
This happens in Excel 2003 and earlier if more than 7 levels of functions are nested, or a total of more than 8 levels including the outer function. The function Excel highlights after you click OK is usually the 9th level (8th nested level). This limitation is noted in Excel 2003's online help under "Excel specifications and limits" | "Calculation specifications" | "Nested levels of functions".
A "nested" function is one which is used as an argument for another function. For example, in this formula, SUM is nested within IF:
A maximum of 7 nested levels, or 8 total levels including the outer function, are allowed by Excel:
This Excel limitation applies to all functions, whether built into Excel or added by add-ins such as xlPrecision. The limitation is the same whether all nested levels are the same function or any combination of functions.
Since this limitation does not affect Excel's built-in operators (+, -, *, /, ^, =, >, <, etc.), some very long formulas that work correctly with Excel's built-in operators must be split out into multiple formulas when converting the formula to use xlPrecision operator functions.
Usually the simplest and most low-maintenance solution is to simply split out the formula into multiple cells. Another solution is to create short defined names with self-contained portions of the desired formula, where each self-contained portion has at least one nested level though no more than seven, then use the defined names in the cell's formula. See the next question for an example, though that example doesn't show nesting functions in the defined name.
Excel 2007 and later allow 64 (sixty-four) nested functions, or 65 levels including the outer function.
I'm trying to enter a long formula with xlPrecision functions, and I get a message that the formula is too long. The same formula with Excel's built-in operators (instead of xlPrecision functions) works fine.
Although Microsoft Excel can return up to
32,767 characters as text from a formula, versions 2003 and earlier allow only 1,024 characters in the
formula itself. This is noted in Excel 2003's online help under "Excel
specifications and limits" | "Calculation specifications" | "Length of formula
contents". Excel versions 2007 and later allow 8,192 characters in the
Since xlPrecision's operator functions (xlpADD, xlpISGREATER, etc.) are unavoidably more verbose than Excel's built-in operators (+, >, etc.), you cannot have as many xlPrecision operator functions in a single formula as you can with Excel's built-in operators.
Usually the simplest and most low-maintenance solution is to simply split out the formula into multiple cells.
Another solution is to create short defined names with self-contained portions of the desired formula, then use the defined names in the cell's formula. For example:
- On a new worksheet, enter any numbers in cells A1, B1, and C1.
- Select cell D1.
- From the Insert menu, choose Name | Define.
- In the Define Name dialog:
- Under 'Names in workbook', enter any short name, such as "a" (without quotes).
- Under 'Refers to', enter "=xlpADD(A1,B1)" (without quotes).
- Since A1 and B1 are relative references, they actually refer not literally to A1 and B1, but rather to the cell three cells to the left, and the cell two cells to the left, of whatever cell in which this defined name is used (because A1 and B1 are three cells to the left, and two cells to the left, of the cell (D1) where this defined name is being created). So, if this defined name is used in a formula in cell D2, it will refer to cells A2 and B2. If it is used in cell E1, it will refer to cells B1 and C1.
- If you prefer, the references can be made absolute (or mixed) instead of relative, just like any cell reference:
- Click OK.
- In cell D1, enter the formula "=xlpADD(a,C1)" (without quotes).
- If you used something other than "a" when creating the defined name, use whatever name you created instead of "a".
- Note that the formula in cell D1 correctly adds up all three numbers in cells A1, B1, and C1. It does exactly the same work as the formula "=xlpADD(xlpADD(A1,B1),C1)", but uses 12 fewer characters.
- To illustrate that the formula in the defined name is relative, select cell E1 and enter the formula "=a" (without quotes). Note that it adds up the numbers in cells B1 and C1.
I want to divide large numbers using VBA in Word 2003, is it possible with xlPrecision?
Yes, you can use xlPrecision in Word VBA. You can also use it in PowerPoint VBA, Outlook VBA, Access VBA, Project VBA, FrontPage VBA, Publisher VBA, Visio VBA, and any other VBA. You can use xlPrecision in any code that can call a COM (ActiveX, OLE) server. See:
Use xlPrecision in VBA
I'm using the 2,147,483,648 SD edition of xlPrecision in Word 2003 VBA, and I'm not getting as many digits as I should.
Word VBA's TypeText method is limited to 64k, which on unicode versions of Word (such as Word 2003) is 32,767 characters. If you exceed that, it starts over; for example, if you give it 32,769 characters, it returns only the first 2 characters (32,769 - 32,767 = 2).
To work around that limitation, use Word VBA's InsertBefore method instead of TypeText. Or, if InsertBefore doesn't suit your needs, another approach would be to loop through the text 32,767 characters at a time, using Mid to get a 32,767-character chunk, and doing a TypeText on that chunk, each time through the loop.
PrecisionCalc Home Page