14 More tools

Previous chapter [13 Excel data links] Next chapter [A Deployment guide]

This chapter explains various small features of think-cell that can make life a lot easier.

14.1

Special characters

14.2

Save and send selected slides

14.3

Changing the language

14.4

Changing fonts

14.5

Automatic case code

14.6

think-cell round

14.1 Special characters

Experienced PowerPoint users find the built-in Insert → Symbol feature inconvenient for frequent use. With think-cell installed, there is a simpler way to insert special characters.

Open the Special Characters dialog from the More menu in the think-cell toolbar.

You will find all the symbols you need on a single page, grouped by usage context. Now, with a click of the mouse, you select the desired character or symbol and the character is inserted at the cursor location.

Note: If no shape or text label is selected, the think-cell special character dialog has no effect. If you have one shape or text label selected, it will be switched to text editing mode.

14.2 Save and send selected slides

Sometimes it is useful to save parts of a set of slides. With think-cell you no longer need to duplicate the file and then delete unwanted slides. Simply select the desired slides from the outline pane on the left or from the slide sorter view, click on More from the think-cell toolbar and choose Save Selected Slides.... After the new file is created, it is immediately opened for editing.

If you want to send the selected slides by e-mail, you can also choose Send Selected Slides... and an e-mail template with attachment will be composed in your default e-mail application.

The files created by using Send/Save Selected Slides... contain a time stamp conforming to ISO 8601 in the file name to make them globally unambiguous. The file Presentation1 20110121T1556+01 slides 2-3.ppt would contain Slides 2 through 3 from Presentation1.ppt and was sent or saved on January 21, 2011 at 15:56 in time zone UTC+1. “T” separates date and time.

14.3 Changing the language

Setting the language correctly is crucial if you wish to use PowerPoint’s spell checker. think-cell adds extra options to let you change the language of the text in a slide or in a complete presentation. These options can be found in the Tools → Language... menu in PowerPoint 2003 and earlier PowerPoint versions. The equivalent menu in PowerPoint 2007 and later is found by clicking the Language menu item in the Review tab.

Simply select the desired language in the dialog and click either on Text on Slide to change the language of all text on the current slide or on All Text to change the language of all text in the presentation.

14.4 Changing fonts

Changing the font of text within a large presentation can be a lot of work. think-cell’s Replace Fonts... feature can be used to greatly simplify the task. The feature can be accessed in the Format menu of PowerPoint or the Editing group on the Home tab of the PowerPoint ribbon. It replaces PowerPoint’s Replace Fonts dialog. The drop down boxes in the dialog can be used to select the font to replace and the new font, and the font size can also be optionally adjusted. Select the scope for the changes (either Entire presentation, Current slide or Selection) and then click Apply to replace the fonts or click OK to close the dialog as well.

14.5 Automatic case code

You can add a textbox to the master slide that always shows the file name or, alternatively, file path of the presentation. If the file name is the same as the case code used to identify a project in your organization, you can use this textbox to show the case code on each slide. The content of the textbox is updated whenever the file is saved under a different name.

To add a textbox that shows the case code, follow these steps:

1.

Switch to the master slide view (menu View →Master →Slide Master).

2.

Insert a new textbox and place it on the master slide.

3.

In PowerPoint 2007 and later

(a)

Right-click the textbox and open the Size and Position dialog.

(b)

Go to the Alt Text tab and find the input area for Alternative text.

In earlier PowerPoint versions

(a)

Open the Format Text Box dialog (double-click the textbox or select Format Text Box from the context menu).

(b)

Go to the Web tab and find the input area for Alternative text.

4.

In the Alternative text input area, type

• casecode to have the filename automatically displayed in the textbox;
• CASECODE to have the capitalized filename automatically displayed in the textbox;
• casepath to have the full file path automatically displayed in the textbox;
• CASEPATH to have the capitalized full file path automatically displayed in the textbox.

As soon as you save the file, the case code shows up on the slides.

Note: The case code textbox only works on the master slide, not on individual slides.

14.6 think-cell round

When data is compiled for a report or PowerPoint presentation, rounding summations in Excel is a frequent problem. It is often desirable, but difficult to achieve, that rounded totals exactly match the total of the rounded addends. For example, consider the following table:

When the values are rounded to integers using Excel’s Format-Cell feature, the following table results. Totals which appear to be “miscalculated” are in bold:

Similarly, when Excel’s standard rounding functions are used, totals of the rounded values are calculated correctly but rounding errors accumulate and results often deviate substantially from the actual totals of the original values. The following table shows the result of =ROUND(x,0) for the example above. Totals that deviate from the original value by 1 or more are in bold:

Using think-cell round, you can achieve consistently rounded totals with minimal “cheating”: While most values are rounded to the nearest integer, a few values are rounded in the opposite direction, thus maintaining correct calculations without accumulating rounding error. Since there are many possibilities to achieve correctly rounded totals by changing values, the software picks a solution that requires the minimum number of values changed and the minimum deviation from the precise values. For example, rounding down 10.5 to 10 is preferable over rounding down 3.7 to 3. The following table shows an optimal solution for the above example, with “cheated” values in bold:

To achieve this output in your own calculation, simply select the concerned range of Excel cells. Then, click the toolbar button and, if necessary, adjust the rounding precision using the toolbar’s dropdown box.

14.6.1 Using think-cell round

think-cell round seamlessly integrates into Microsoft Excel, providing a set of functions that are similar to Excel’s standard rounding functions. You can easily apply these functions to your own data using the think-cell round toolbar buttons.

In Excel 2007 and later the toolbars have been replaced by ribbons. The think-cell round ribbon can be found in the Formulas tab.

Rounding parameters

Like the Excel functions, the think-cell rounding functions take two parameters:

x

The value that is to be rounded. This can be a constant, a formula or a reference to another cell.

n

The rounding precision. The meaning of this parameter depends on the function you use. The parameters for the think-cell functions are the same as for the equivalent Excel functions. Refer to the table below for examples.

As in Excel, think-cell round can not only round to integer values, but to any multiple. For example, if you want to represent your data in 5-10-15-... steps, simply round to multiples of five.

In Excel, you must use different functions, depending on the desired rounding precision. Examples are the ROUND and the MROUND functions, which interpret the parameter n differently. Using the dropdown box in the think-cell round toolbar, simply type in or select the desired rounding precision. think-cell round chooses the appropriate function and parameters for you.

The following table provides some examples of rounding, using the toolbar, ROUND or MROUND together with their specific n-parameter.

If the values are not displayed the way you expect them to, verify that the Excel Cell Formatting is set to General and the columns are wide enough to display all decimal places.

For optimal results with as little deviation from the underlying values as possible, you should use TCROUND wherever possible. Only use the more restrictive functions TCROUNDDOWN, TCROUNDUP or TCROUNDNEAR if you must.

Attention: You should never use nondeterministic functions like RAND() within any of the TCROUND formulas. If functions return a different value every time they are evaluated, think-cell round will make mistakes calculating values.

Layout of the calculation

The rectangular layout of the example above is only for sake of demonstration. You can use the TCROUND functions to determine the display of arbitrary summations spread across your Excel sheet. Excel’s 3-D references to other sheets and links to other files do also work.

Placement of TCROUND functions

Since TCROUND functions are meant to control the output of a cell, they must be the outermost function:

Bad:	=TCROUND(A1, 1)+TCROUND( SUM(B1:E1), 1 )
Good:	=TCROUND( A1+SUM(B1:E1), 1 )
Bad:	=3*TCROUNDDOWN(A1, 1)
Good:	=TCROUNDDOWN(3*A1, 1)

If you happen to enter something along the lines of the bad examples, think-cell round will notify you with the Excel error value #VALUE!.

14.6.2 Limitations of think-cell round

think-cell round always finds a solution for arbitrary summations with subtotals and totals. think-cell round also provides sensible solutions for some other calculations involving multiplication and numerical functions. However, for mathematical reasons, the existence of a consistently rounded solution cannot be guaranteed as soon as operators other than +, - and SUM are used.

Multiplication with a constant

In many cases, think-cell round produces good results when constant multiplication is involved, i.e., at most one of the coefficients is derived from the result of another TCROUND function. Consider the following example:

The precise calculation for cell C1 is 3×1.3+1.4=5.3. This result can be met by rounding up the value 1.4 to 2:

However, think-cell round can only “cheat” by rounding up or rounding down. Further deviation from the original values is not supported. Thus, for certain combinations of input values, no consistently rounded solution can be found. In this case, the function TCROUND evaluates to the Excel error value #NUM!. The following example illustrates an unsolvable problem:

The precise calculation for cell C1 is 6×1.3+1.4=9.2. Rounding cells A1 and B1 would result in 6×1+2=8 or 6×2+1=13. The actual result 9.2 cannot be rounded to 8 or 13, and the output from think-cell round looks like this:

Note: The Excel function AVERAGE is interpreted by think-cell round as a combination of summation and constant multiplication. Also, a summation where the same addend appears more than once is mathematically equivalent to a constant multiplication, and the existence of a solution is not guaranteed.

General multiplication and other functions

As long as the TCROUND functions are used for all relevant cells and intermediate results are connected merely by +, -, SUM and AVERAGE, the addends as well as (intermediate) totals are integrated into a single rounding problem. In these cases, think-cell round will find a solution that provides consistency throughout all cells involved, if such a solution exists.

Since TCROUND is a normal Excel function, it can be combined with arbitrary functions and operators. But when you use functions other than the ones mentioned above to connect results from TCROUND statements, think-cell round cannot integrate the components into one interconnected problem. Instead, the components of the formula will be taken as distinct problems which will be solved independently. The results will then be used as input to other formulas.

In many cases, the output from think-cell round will still be reasonable. There are cases, however, where the use of operators other than +, -, SUM and AVERAGE leads to rounded results which are far off from the result of the non-rounded calculation. Consider the following example:

In this case, the precise calculation for cell C1 would be 8.6×1.7=14.62. Since cell A1 and cell B1 are connected by a multiplication, think-cell round cannot integrate the formulas from these cells into a common problem. Instead, after detecting cell A1 as valid input, cell B1 is evaluated independently and the output is taken as a constant within the remaining problem. Since there are no further constraints, value 1.7 from cell B1 is rounded to the nearest integer, which is 2.

At this point, the “precise” calculation for cell C1 is 8.6×2=17.2. This is the problem that think-cell round now tries to solve. There is a consistent solution which requires rounding up 17.2 to 18. The result looks like this:

Note that the rounded value in cell C1, which is 18, greatly differs from the original value 14.62.

14.6.3 Troubleshooting TCROUND formulas

There are two possible error results you may come across when using think-cell round: #VALUE! and #NUM!.

#VALUE!

The #VALUE! error hints to syntactical problems, such as mistyped formulas or bad parameters. For example, the second parameter for TCROUND must be an integer value. Also, pay attention to use correct delimiters. For example, while in international Excel the formula looks like this: =TCROUND(1.7, 0), in a localized German version of Excel it must be written as: =TCROUND(1,7; 0)

Another mistake specific to think-cell round is the placement of the TCROUND function call: You cannot use a TCROUND function within another formula. Please make sure that TCROUND is the outermost function of the cell’s formula. (see Placement of TCROUND functions)

#NUM!

The #NUM! error results from numerical problems. When the output of a TCROUND function is #NUM!, this means that the problem as stated by the given set of formulas is mathematically unsolvable. (see Limitations of think-cell round)

As long as the formulas enclosed by TCROUND functions contain merely +, - and SUM, and all TCROUND statements share the same precision (second parameter), a solution is guaranteed to exist and will be found by think-cell round. However, in the following cases there is no guarantee that a consistently rounded solution exists:

• Formulas involve other operations like multiplication or numerical functions. Also, summations where the same addend appears more than once are mathematically equivalent to a multiplication.
• You use different precisions in the second parameter of the TCROUND function.
• You make frequent use of the specific functions TCROUNDDOWN, TCROUNDUP and TCROUNDNEAR.

You can try to restate the problem to get a consistent solution. Try the following:

• Use a finer precision for some or all TCROUND statements.
• Do not use TCROUND with multiplication or numerical functions other than +, - and SUM.
• Use the same precision (second parameter) for all TCROUND statements.
• Use TCROUND instead of the more specific functions TCROUNDDOWN, TCROUNDUP and TCROUNDNEAR wherever possible.

Previous chapter [13 Excel data links] Next chapter [A Deployment guide]