xl Precision
Get Your Numbers Right

xlPrecision Tips

• If you don't use Excel's Insert Function Dialog, repeatedly typing in xlPrecision's functions can be tedious and prone to typographical errors. You might want to set up abbreviations for them and have Excel automatically change the abbreviations you type in to the full function names. For example, you could have Excel automatically change something like "xm" to "xlpMULTIPLY", "xs" for "xlpSUBTRACT", etc.

  • In Excel, choose Tools | AutoCorrect

  • Under "Replace:", type in xm

  • Under "With:", type in xlpMULTIPLY

  • Click Add

  • Repeat as desired for other functions, using whatever abbreviations you wish.

  • Click OK.

• You might want to have a way to increase or reduce the number of significant digits returned for a large group of xlPrecision formulas, all at the same time, without affecting others. To do so, enter the number in unused cell, and refer to it from the xlPrecision formula. For example:

  • Cell A1:
    100

  • Cells A2 - A5:
    =xlpMULTIPLY(B2,C2,,,,A1)
    =xlpMULTIPLY(B3,C3,,,,A1)
    =xlpMULTIPLY(B4,C4,,,,A1)
    =xlpMULTIPLY(B5,C5,,,,A1)

  Then, you can change the number in cell A1 to affect all the xlPrecision formulas that refer to A1.

  To keep that reference the same while copying the formula down rows or across columns, change it to an absolute reference by adding "$" characters, like this:

  • =xlpMULTIPLY(B2,C2,,,,$A$1)

   

• Although using Excel's Insert Function Dialog is more convenient than entering functions manually, especially with many arguments, it can be slow because Excel recalculates the function every time a digit or character is entered. 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 dialog, manually change the numeric operands to the desired numbers directly in the worksheet.

• If you want to chart the results of xlPrecision formulas, add another column etc. and enter formulas like this:

=VALUE(A1)
=VALUE(A2)
=VALUE(A3)

Where A1 etc. are the cells with the xlPrecision results.

Of course, Excel's VALUE function will truncate the results to 15 significant digits of precision, since that's all Excel can handle as a number. It would probably be possible to create a whole new charting engine in an Excel add-in that could chart high-precision xlPrecision results, but I am skeptical that such a chart could show the difference anyway, without far more pixels than monitors and printers have.

 

• To get a red font for negative numbers, see Format Negative Numbers with Red Font.

• To get high-precision powermod, combine xlpMOD with xlpPOWER:

=xlpMOD(xlpPOWER(2,5),23)

xlPrecision Home Page
PrecisionCalc Home Page