| Problem: You
try to run the TreePlan add-in, you click the New Tree button, and you receive an error message like the
following: "The formula you typed contains an error."
Possible cause: When TreePlan creates a tree diagram, it
enters formulas in worksheet cells. The TreePlan formulas use a period
as the decimal separator and a comma as the list separator. On some
computers, Excel or the operating system is set to use a comma as the
decimal separator and a semicolon as the list separator. On such systems
the error message occurs. Example of a rollback formula
TreePlan may try to enter in a cell:
=IF(ABS(1-SUM(H1,H6))<=0.00001,SUM(H1*I4,H6*I9),NA()) Same formula with separators
that may be expected by Excel:
=IF(ABS(1-SUM(H1;H6))<=0,00001;SUM(H1*I4;H6*I9);NA()) Solution:
Before using TreePlan, (a) be sure that the operating system is set to
use a comma as the list separator, and (b) be sure that Excel is set to
use a period as the decimal separator. The following instructions
refer to Windows 7 and Excel 2010. Similar features are available for
other operating systems and for other versions of Excel. (a) Instructions
for List Separator: The list separator is an operating system
setting. In Windows 7 choose Start > Control Panel.
If the Control Panel is showing the Category view, choose Clock,
Language, and Region > Change the date, time, or number format. If the
Control Panel is showing the Large icons or Small icons view, choose
Region and Language. The Format tab of the Region and Language dialog
box appears as follows:
 If you
change the Format to English (United States) and click OK, many of the
operating system settings will be changed, including the change to use a
period as the decimal separator and a comma as the list separator. If
so, you can choose to have Excel use those system separators (shown
below). If you want to keep most of the settings for the current
locale, shown above as "German (Germany)," click the "Additional
settings ..." button. The Numbers tab of the Customize Format dialog box
appears as follows:
 For the
German (Germany) locale shown above, the default settings are comma for
decimal symbol and semicolon for list separator. To be able to use
Excel, the list separator must be a comma. You can select the comma
symbol from the dropdown list, or, if it is not listed, you can select
the semicolon and type a comma. Then click OK and OK.
(b) Instructions for Decimal Separator: If you select an
operating system locale like English (United States), the decimal symbol
is a period, and you can specify that Excel use the system separators.
To verify, in Excel 2010, choose File > Options > Advanced > (Editing
options) Use system separators, and be sure the box is checked as shown
below.
If you prefer to use an operating system locale that does not use a
period as the decimal symbol, you must still use the period as the
decimal separator in Excel when you are modifying and redrawing tree
diagrams with TreePlan. To do so, uncheck the box for Use system
separators, select the symbol in the decimal separator edit box, type a
period, and click OK. After you finish using TreePlan to redraw the tree
diagram, you may change the decimal separator.
Summary:
TreePlan requires a period as the decimal separator and a comma as the
list separator.
(a) The list separator is an operating system setting. It can be set to be
a comma either by choosing a compatible locale or by customizing any
locale.
(b) The decimal separator can be set to be a period by a
combination of the operating system locale setting and Excel's "Use
system separators" setting.
|
