Problem: You try to run TreePlan version 1.77 or earlier, 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 in versions 1.77 and earlier 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: Use TreePlan 1.78 or later.
Before using TreePlan version 1.77 or earlier, (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, Excel 2010, and TreePlan versions 1.77 and earlier. 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 versions 1.77 and earlier. 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 versions 1.77 and earlier require 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.