Tables let you organize data in rows and columns of cells. Tables can contain text or graphics and can be formatted. You can use tables to present lists, schedules, financial data, comparisons, and summaries.
Charts can enhance the presentation of data in a document. You can use data charts to display comparisons, trends, and statistics, or you can use organization charts to display an organization's structure.
In this section, you'll learn about
creating tables
exporting tables to spreadsheets
selecting and moving table components
setting and inserting rows and columns in tables
joining and splitting table cells
deleting tables and table components
sorting data in tables
navigating named tables, table components, or floating cells
locking table cells
displaying table gridlines and formula indicators
adding diagonal lines to table cells
rotating table cell contents and skewing cells
adjusting table column widths
creating table header rows and adjusting table rows
applying fills, lines, and color to tables
applying borders to tables
creating table styles
changing text alignment
applying and customizing numeric formats
naming tables
using operators
entering formulas in tables
calculating formulas in tables
creating charts
For more information about table functions, see "Reference: Using tables and charts."
Creating tables
A table consists of rows, which run horizontally, and columns, which run vertically. Rows and columns intersect to create cells. A cell is like a small editing window in which you can insert text, graphics, numbers, or formulas. The cells in a table are labeled alphabetically from left to right and numerically from top to bottom.
The following image shows a table with: 1) rows and 2) columns.
You can create a table anywhere in a document window. When a table is in a graphics box, text can flow around the table.
You can create a floating cell, which is a single table cell that exists outside of a table. A floating cell can contain formulas, text, or numbers and can be linked to other parts of the document or to other documents to present a snapshot of information, such as a sales total.
You can create a table by importing a spreadsheet or database from applications like Quattro Pro, Lotus 1-2-3, and Microsoft Excel. You can also export a table to these applications.
You can also create a table by converting text. Tables can also be converted into text.
To create a table
1 Click Table Create.
2 Type values in the following boxes:
Columns
Rows
3 Click Create.
You can also
Create a table in a graphics box Enable the Drag to create a new table check box, click Create, and drag a rectangle anywhere in a document.
Create a floating cell In the Create area, enable the Floating cell option.
Tips
You can also create a table by clicking the Table QuickCreate button on the toolbar and dragging to specify the number of rows and columns for the table.
If the Shadow Cursor is enabled, you can create a table by dragging a rectangle and clicking Table in the QuickMenu.
To create a table by importing a spreadsheet or database
1 Click Insert Spreadsheet/database Import.
2 Choose a format from the Data type list box.
3 Choose Table from the Import as list box.
4 In the Filename box, type the folder and name of the file you want to import.
If you want to search for the file, click the Browse button and choose the drive and folder where the file is stored.
Notes
When you import a spreadsheet workbook, WordPerfect imports only the first sheet.
A spreadsheet's functions will not work in WordPerfect after you import the spreadsheet.
To convert text to a table
1 Select tabular text.
2 Click Table Convert.
3 In the Text delimiters area, enable one of the following options:
Commas- converts each comma in the text to a column
Tabs -converts each tab in the text to a column
Paragraphs-converts each paragraph mark in the text to a column
User defined-convert each defined item in the text to a column
If you enable the User defined option, you can type an item in the User defined box.
You can also
Make the column size match the size of the text In the Format area, enable the Size column to fit option.
Make the columns equal size In the Format area, enable the Equal column widths option.
Choose a table format Click SpeedFormat.
Note
Tables converted from tabular text may contain extra rows or cells. For information about joining extra rows or cells, see "Joining and splitting table cells." For information about deleting extra rows or cells, see "Deleting tables and table components."
Tip
You can also convert a table to text. Select the table cells you want to convert, click Table Convert, and enable the option you want to use.
To convert parallel columns to a table
1 Select parallel columns.
2 Click Table Convert.
3 In the Text delimiters area, enable the Parallel column option.
Exporting tables to spreadsheets
You can export a table created in WordPerfect to a spreadsheet application, such as Quattro Pro, Lotus 1-2-3, or Microsoft Excel. If more than one table exists in the WordPerfect document, each table becomes one sheet in the spreadsheet application, with the first table becoming sheet one, the second table becoming sheet two, and so on.
To export a table to a spreadsheet
1 Click File Save as.
2 Choose the drive and folder where you want to save the file.
3 Type a filename in the Filename box.
4 Choose a file type from the File type list box.
5 Click Save.
Notes
When you want only one table in a document exported, you have to select the table before exporting it.
When you export a table, only the values and text in each cell are exported. Any formulas and functions within the cells are lost.
Selecting and moving table components
You must select a table or table component before you can perform a procedure, such as changing the number of rows or columns in the table or copying the format of one table cell to another. You can also select table components by using QuickSelect or Row/column indicators.
You can copy, cut, and paste table components. You can copy the format of a table cell and apply the formatting to another cell or group of cells. Formatting includes attributes, such as line style, line color, or fill, but does not include cell contents, such as text or formulas.
To select a table
1 Click anywhere in a table.
2 Click Edit Select Select table.
To select a table or table components by using QuickSelect
To select Do the following
A cell In the table, move the pointer until it changes to an arrow pointing upward or to the left, and click.
A row In the table, move the pointer until it changes to an arrow pointing to the left, and double-click.
A column In the table, move the pointer until it changes to an arrow pointing upward, and double-click.
The entire table In the table, move the pointer until it changes to an arrow pointing upward or to the left, and triple-click.
Tip
You can also select a cell by clicking in a cell and pressing SHIFT + F8.
To select rows, columns, or tables by using Row/column indicators
To select Do the following
A row Right-click in a table, and click Row/column indicators. Click the row indicator next to the row (for example, 1, 2, and so on).
A column Right-click in a table, and click Row/column indicators. Click the column indicator above the column (for example, A, B, and so on).
The entire table Right-click in a table, and click Row/column indicators. Click the rectangle in the top-left corner of the row and column indicators (the cursor becomes a double-headed corner arrow).
Tip
You can also select rows, columns, and tables by clicking Table Row/col indicators.
To copy table components
1 Select a range of cells, a row, or a column.
2 Click Edit Copy.
3 Enable one of the following options:
Selection-copies only the selected cells
Row-copies the selected row
Column-copies the selected column
Cell-copies the selected cell to the Clipboard, or down, or to the right a specified number of times
4 Click where you want to paste the cells, row, or column.
5 Click Edit Paste.
Tips
If you want to move a selected range of cells, a row, or a column, click Edit Cut.
You can also copy table components by selecting a range of cells, a row, or a column, right-clicking, and clicking Copy.
To copy the format of a table cell
1 Click a formatted cell.
2 Click Format QuickFormat.
A check mark beside the menu command indicates that the command is enabled.
3 Enable the Selected table cells option.
4 Click OK.
The pointer changes to the QuickFormat paintbrush cursor.
5 Click the cells you want to format.
Tip
You can disable QuickFormat by clicking a cell and clicking Format QuickFormat.
Setting and inserting rows and columns in tables
You can set the number of rows and columns in a table. You can also insert columns or rows in a table. New columns or rows have the same formatting as the column or row in which the cursor is positioned.
To set the number of rows and columns in a table
1 Select a table.
2 Click Table Format.
3 Click the Table tab.
4 In the Table size area, type values in the following boxes:
Columns
Rows
Tip
If you add columns and you want all the columns to be the same width, select a row and click Table Equal column widths.
To insert a column or row in a table
1 Right-click where you want to insert the column or row, and click Insert.
2 Enable one of the following options:
Columns-lets you specify the number of columns you want to insert
Rows-lets you specify the number of rows you want to insert
3 Enable one of the following options:
Before-inserts new columns or rows before the row in which the cursor is positioned
After-inserts new columns or rows after the row in which the cursor is positioned
Tips
If you insert a column, enable the Keep column widths the same check box to prevent the existing columns from changing width when the new column is added.
You can also insert a row by clicking the Insert row button on the property bar.
Joining and splitting table cells
You can join or split tables and table cells. Except for individually formatted cells, the second table acquires the formatting of the first table. When you join selected cells, the formatting of the top left cell is used for all the joined cells. You can also split any table horizontally into two tables. When you split table cells, each selected cell divides in two. The new cells have the same format as the original cell.
To join table cells
1 Select the cells you want to join.
2 Click Table Join Cell.
Tip
You can also join table cells by clicking Table Join QuickJoin and selecting the cells you want to join.
To join two tables
1 Click View Reveal Codes.
2 In the Reveal Codes window, delete all hard return codes and any other codes or text between the [Tbl Off] code of the first table and the [Tbl Def] code of the second table.
3 Click in the first table.
4 Click Table Join Table.
To split a table cell
1 Click in a cell.
2 Click Table Split Cell.
3 Enable one of the following options and type a value:
Columns-splits a table cell into columns
Rows-splits a table cell into rows
Tips
You can also split table cells by clicking the QuickSplit row button, the QuickSplit column button, or the QuickSplit columns and rows button on the property bar, and clicking in a table cell. When the QuickSplit row button, the QuickSplit column button, or the QuickSplit columns and rows button appears pressed, you can also hold down SHIFT to toggle to the QuickJoin tool.
You can toggle back and forth between splitting cells and rows by clicking the QuickSplit row button or the QuickSplit column button, and holding down ALT.
To split a table
1 Click where you want to divide a table.
2 Click Table Split Table.
3 Click View Reveal Codes.
4 In the Reveal Codes window, place the cursor between the [Tbl Off] code of the first table and the [Tbl Def] code of the second table, and press ENTER a few times to separate the two tables.
Deleting tables and table components
You can delete a row, column, or an entire table. If you want to maintain the table structure, you can delete the contents only or formulas only. When you delete a row, column, or an entire table, you can delete the data, the structure, or only the formulas.
To delete a table row or column
1 Select a row or column, and click Table Delete.
2 Enable one of the following options:
Rows-lets you specify the number of rows to delete
Columns-lets you specify the number of columns to delete
If you want to delete only the contents of a row or column, enable the Cell contents only option.
To delete a table formula without deleting cell contents
1 Click in the cell containing a formula and click Table Delete.
2 Enable the Formulas only option.
To delete the contents or structure of an entire table
1 Select a table.
2 Click Table Delete.
3 Enable one of the following options:
Entire table-deletes the contents and structure
Table contents only-deletes the contents
Formulas only-deletes the formulas
Tip
You can also delete table contents by selecting a table, right-clicking, and clicking Delete.
Sorting data in tables
You can sort data in an individual column, in several columns, or in an entire table. For example, you can sort a list of names alphabetically, or you can sort a list of historical dates chronologically.
To sort data in a table column
1 Select a column.
2 Click the Sort table button on the property bar.
3 Choose one of the following options:
Alpha ascending-sorts data alphabetically from A to Z
Alpha descending-sorts data alphabetically from Z to A
Numeric ascending-sorts data numerically from lowest number to highest number
Numeric descending-sorts data numerically from highest number to lowest number
Dates ascending-sorts data chronologically from the oldest date to the most recent date
Dates descending-sorts data chronologically from the most recent date to the oldest date
Note
If you use header rows in a table, they will not be sorted with the rest of the data in the table.
Tip
You can sort more than one column at a time by selecting a group of columns or the entire table.
Navigating named tables, table components, or floating cells
You can go to named tables, table components, or floating cells in the active document.
To go to a named table
1 Click Edit Go to.
2 Choose Table from the Go to what list.
3 Choose a table from the Select table list box.
4 Click Go to.
You can also
Go to a table component Choose a table component from the Cell/range list box.
Go to a floating cell Choose a floating cell from the Cell/range list box.
Tip
You can also go to a named table, table component, or floating cell by clicking Table Names.
Locking table cells
Locking a table cell prevents changes to the cell data. You can also disable a locked cell temporarily to edit the data in a cell.
To lock a table cell
1 Select a cell.
2 Click Table Format.
3 Click the Cell tab.
4 In the Cell attributes area, enable the Lock cell to prevent changes check box.
To disable all locked table cells temporarily
1 Select a table.
2 Click Table Format.
3 Click the Table tab.
4 Enable the Disable locks in all cells check box.
Displaying table gridlines and formula indicators
When a document contains many tables or heavily formatted tables, you can use table gridlines to make the document display faster.
Table gridlines are dimmed, dotted lines that replace lines and fill in a table. However, tables print with the lines and fill styles you have selected. You can choose to always display table gridlines.
You can also display the table formula indicators as blue markers in the bottom corner of a table cell.
To display table gridlines
Click View Table gridlines.
A check mark next to the Table gridlines command indicates that the table gridlines are displayed.
To display table gridlines by default
1 Click Tools Settings.
2 Click Display.
3 Click the Document tab.
4 In the Show area, enable the Table gridlines check box.
To display table formula indicators
1 Click Tools Settings.
2 Click Display.
3 Click the Document tab.
4 In the Show area, enable the Table formula indicators check box.
Adding diagonal lines to table cells
You can add diagonal lines to a table cell. Diagonal lines divide the cell visually but do not split the cell in two. You can place data, such as dates and times, on both sides of the line in the cell.
To add diagonal lines to a table cell
1 Select a cell or a group of cells, and click Table Format.
2 Click the Cell tab.
3 In the Draw diagonal line in cell area, enable one of the following options:
Upper right to lower left
Upper left to lower right
Both
Note
The line style and color of the diagonal line is the same as the line style and color of the table lines; however, you cannot draw diagonal lines in some line styles, such as double lines.
Rotating table cell contents and skewing cells
You can rotate the contents of a table cell counterclockwise. Rotated text is placed in a text box.
You can skew (slant) the top row or the left or right column of a table. You can also change the skew angle and specify other skew options.
The following image shows several examples of skew table presets.
When you change the skew of the top row, you can square the edge of the first or last column to create a box effect. This prevents the cells from extending beyond the edge of the table.
To rotate table cell contents
1 Select a cell.
2 Click Table Format.
3 Click the Cell tab.
4 Choose one of the following from the Rotate list box:
90 degrees
180 degrees
270 degrees
Note
When you rotate a table cell containing a formula, the formula is permanently replaced by its result.
To edit rotated table cell contents
1 Right-click the contents in a rotated cell, and click Content.
2 Click Edit.
3 In the Text Box Editor, edit the text.
4 Click File Close.
Note
When you edit rotated text, the Text Box Editor displays the text in a separate editing window.
Tip
You can also open the Text Box Editor by clicking the rotated cell contents.
To skew cells in a table
1 Select a table.
2 Click Table Format.
3 Click the Skew tab.
4 Choose the cells you want skewed from the Skew settings list.
5 Click More.
6 From the Skew rows list box, choose one of the following:
None-lets you leave the top row unskewed
Top-lets you specify the skew angle for the top row
7 From the Skew columns list box, choose one of the following:
None-lets you leave the columns unskewed
Left column-lets you specify the skew angle for the left column
Right column-lets you specify the skew angle for the right column
You can also
Create a 90-degree angle with the first column and the top row Enable the Square first column check box.
Create a 90-degree angle with the last column and the top row Enable the Square last column check box.
Join the edge of the first or last column with the top row Enable the Join corners check box.
Modify the skew angle of the top row, left column, or right column Type a skew angle in the appropriate Angle box.
Notes
The Square first column and Square last column check boxes are available only if the top row is skewed. The Join corners check box is available only if the left or right column is skewed.
You may need to increase the row height of a skewed row to accommodate the text. For information about changing the height of a row, see "To change the height of a table row."
Tip
To prevent text from slanting, disable the Skew text check box.
Adjusting table column widths
You can specify an exact table column width, change unequal table columns so that they have equal widths, or change table column widths automatically.
To specify an exact table column width
1 Select a column.
2 Click Table Format.
3 Click the Column tab.
4 In the Column width area, type a value in the Width box.
Tip
Enable the Always keep width the same check box to maintain the current column width regardless of changes in the width of other columns or changes in the settings of left and right column margins.
To change unequal table columns to have equal widths
1 Select two or more cells from adjacent columns.
2 Click Table Equal column widths.
To adjust table column widths automatically
1 Click in a table.
2 Click Table Size column to fit.
Note
The Size column to fit option changes a table column to the width of the widest text in the column cells.
Creating table header rows and adjusting table rows
You can create a table header row. A header row displays at the top of the page each time the table spans a page break.
You can allow a table row to continue across pages. You can also change the height of a table row and the number of text lines per row, and you can align a table between the left and right page margins.
To create a table header row
1 Select a row or several rows.
2 Click Table Format.
3 Click the Row tab.
4 Enable the Header row check box.
To continue a table row across pages
1 Select a row or several rows
2 Click Table Format.
3 Click the Row tab.
4 Enable the Divide row across pages check box.
To change the height of a table row
1 Select a row or several rows.
2 Click Table Format.
3 Click the Row tab.
4 Enable one of the following options:
Single line only-sizes the row to fit one line of text, and hides extra text
Multiple lines-expands the row to fit more than one line of text
5 Enable one of the following options:
Automatic-lets you specify a height based on text height
Fixed-lets you specify a row height measurement
Note
When you specify a fixed row height, the row stays at that height no matter how much text you insert in any cell in the row. As a result, some text might not display in a cell.
Tip
You can also change a row's height by pointing to the top or bottom line until it changes into a double arrow, and dragging.
To set the horizontal position of a table
1 Select a table.
2 Click Table Format.
3 Click the Table tab.
4 From the Table position on page list box, choose one of the following options:
Left-aligns the table to the left of the page
Right-aligns the table to the right of the page
Center-centers the table on the page
Full-aligns the table to fit across the page
Note
If the table is not sized to fit the page margins, the table size remains unchanged when the margins change. To specify that a table resizes with the margins, choose Full from the Table position on page list box.
Tip
You can also set the horizontal position by choosing From left edge from the Table position on page list box and typing a value.
Applying fills, lines, and color to tables
You can change the fill, line style, and line color of a cell, a group of cells, or an entire table. WordPerfect lets you create contrast between cells, rows, and columns by applying an alternating fill to a table. A fill style consists of a fill type and foreground and background colors. A fill type can be either a pattern, such as a checkerboard or fill percentage, or a gradient, which displays a progression between two colors that follows a linear, radial, conical, or square path.
You can apply an alternating fill to a table to create contrast between cells, rows, and columns.
The following image shows a table with an alternating fill applied.
A line style can include several lines together, such as a double line or triple line, and the lines can vary in thickness and style. A line style consists of lines that surround the selection (outside lines) and lines within the selection (inside lines). You can edit the two types of lines separately.
To change the fill of a table cell
1 Select a cell.
2 Click Table Borders/fill.
3 Click the Cell tab.
If you want to change the fill of a group of table cells, enable one of the following options in the Apply lines to area:
Outside of current selection-lets you change attributes for the group of cells as if it were one cell
Each cell in selection-lets you change attributes for each selected cell in the group of cells
4 Click a fill style on the Fill palette.
5 Click a fill color on the Foreground or Background palettes.
You can also
Change the line style of a table cell Click a line style on the Left, Right, Top, Bottom, or Outside line palettes.
Change the line color of a table cell Click a line color on the Color palette.
To change the fill of all cells in a table
1 Select a table.
2 Click Table Borders/fill.
3 Click the Table tab.
4 Click a fill style on the Pattern palette.
5 Click a fill color on the Foreground or Background palettes.
You can also
Change the line style of all table cells Click a line style on the Line palette.
Change the line color of all table cells Click a line color on the Color palette.
Note
A fill style is set to None by default. You must choose a fill style for the foreground and background colors to take effect.
To apply an alternating fill to a table
1 Select a table, and click Table Borders/fill.
2 Click the Table tab.
3 From the Type list box, choose one of the following options:
Rows
Columns
Both
4 In the Fill area, click a fill style on the Pattern palette.
5 In the Alternating fill area, click a fill style on the Pattern palette.
6 In the Fill area, click a fill color on the Foreground or Background palettes.
7 In the Alternating fill area, click a fill color on the Foreground or Background palettes.
8 In the Fill area, type the number of columns or rows for each fill style in the Span count box.
9 In the Alternating fill area, type the number of columns or rows for each fill style in the Span count box.
Applying borders to tables
You can add a border style to a table. You can choose one of the border styles provided with WordPerfect or create a custom border with line style, line color, drop shadow, and fill options, then name that style so that you can apply it to other tables in your document. You can also set a standard look for all tables in a document by defining a table style as the default setting. The line, fill, and border settings you create will be the default for other tables in this and other documents that use the same template.
To apply a border to a table
1 Select a table.
2 Click Table Borders/fill.
3 Click the Table tab.
4 Click a border style on the Border palette.
To create a table border style
1 Select a table, and click Table Borders/fill.
2 Click the Table tab.
3 Click Styles.
4 In the Graphics styles dialog box, click Create.
5 In the Create border style dialog box, type a name for the style in the Style name box.
6 Enable one of the following check boxes:
Left
Right
Top
Bottom
All
7 Click a line style on the Line style palette.
8 In the Border options area, click a line color on the Color palette.
9 In the Drop shadow area, choose a shadow type, color, and width.
Tip
You can also to edit a table border style by choosing a style in the Graphics styles dialog box and clicking Edit.
To customize a table border style for the current table
1 Select a table.
2 Click Table Borders/fill.
3 Click the Table tab.
4 Click a border style on the Border palette.
5 Click Custom.
6 In the Customize border dialog box, enable one of the following check boxes:
Left
Right
Top
Bottom
All
7 Click a line style on the Line style palette.
8 In the Border options area, click a line color on the Color palette.
9 In the Drop shadow area, choose a shadow type, color, and width.
To set a standard look for tables
1 Select a table, and click Table Borders/fill.
2 Click the Table tab.
3 Click a border style on the Border palette.
4 In the Table border area, click a color on the Color palette.
5 Click a line style on the Line palette.
6 In the Default cell lines area, click a line color on the Color palette.
7 In the Fill area, click a fill style on the Pattern palette.
8 In the Fill area, click a fill color on the Foreground or Background palettes.
9 Click Default settings.
Creating table styles
WordPerfect lets you create a table style by using the formatting of a table from a document. When you save a table style, you can associate it with a specific template or with the active document only, or you can save it as a separate file. Saving a table style as a separate file is useful if you plan to use the table style with a different template or on a different computer.
You can also format a table by using a preset style. For example, you can give a table a new look by applying a business or decorative style.
You can rename and retrieve a table style that you have created and saved as a file; however, you cannot rename the preset table styles provided with WordPerfect.
You can also delete a table style that you have created and saved; however, you cannot delete the preset table styles provided with WordPerfect.
To format a table using a preset style
1 Select a table.
2 Click Table SpeedFormat.
3 Choose a table style from the Available styles list box.
4 Click Apply.
To create a table style
1 Create a table that contains at least three rows and three columns.
2 Apply any text formatting, lines, borders, and fill that you want for the table style.
3 Select the table, and click Table SpeedFormat.
4 Click Options Setup.
5 In the Table style settings dialog box, enable the Current document option.
If you want to save a table style to a specific template, enable one of the following options:
Default template-associates the table style with the default template
Additional objects template-associates the table style with an additional objects template
6 Click OK.
7 Click Create.
8 In the Create table style dialog box, type a name for the new style.
To save a table style as a separate file
1 Create a table that contains at least three rows and three columns.
2 Apply any text formatting, lines, borders, and fill that you want for the table style.
3 Select the table, and click Table SpeedFormat.
4 Click Options Save as.
5 Type a path and filename in the Filename box.
To rename a table style
1 Select a table.
2 Click Table SpeedFormat.
3 Choose a table style from the Available styles list box.
4 Click Options Rename.
5 Type a new name in the Filename box.
To retrieve a table style
1 Select a table.
2 Click Table SpeedFormat.
3 Click Options Retrieve.
If you have styles saved in different locations, click Options Setup, and click the location of the styles you want to retrieve.
4 Type a path and filename in the Filename box.
To delete a table style
1 Select a table.
2 Click Table SpeedFormat.
3 Choose a table style from the Available styles list box.
4 Click Options Delete.
Changing text alignment
WordPerfect lets you change the alignment of text in a cell, column, or table, and you can adjust the amount of white space around text in a cell. You can also customize how numbers are displayed and printed in a table or floating cell, and you can change the placement of decimal points in numbers.
To change the alignment of text in a cell
1 Select a cell, and click Table Format.
2 Click the Cell tab.
3 Choose an alignment from the following list boxes:
Horizontal
Vertical
You can also
Change the alignment of text in a row In the Properties for table format dialog box, click the Row tab. Choose an alignment from the following list boxes:
Top
Bottom
Change the alignment of text in a column In the Properties for table format dialog box, click the Column tab. Choose an alignment option from the Horizontal list box.
Change the alignment of text in a table In the Properties for table format dialog box, click the Table tab. Choose an alignment option from the Horizontal list box.
Tip
To insert a tab in a table cell, press CTRL + TAB.
Applying and customizing numeric formats
Numeric formats determine how numbers are used, displayed, and printed in a table or floating cell. For example, you can format numbers as currency, and then customize the format so that it displays with or without the currency symbol or commas. WordPerfect lets you customize the date and time format by displaying numbers, text, or both.
You can also change the placement of decimal points in a table column or in an entire table. You can specify the number of digits that are placed after the decimal point, or you can place the decimal point at a specific distance from the right margin.
To apply a numeric format
1 Select a table.
2 Click Table Numeric format.
3 Click one of the following tabs:
Cell-lets you define cell properties
Column-lets you define column properties
Table-lets you define table properties
4 Enable a numeric format option.
If you want to customize a numeric format, click Custom, and modify any options.
Note
Customizing options vary depending on the numeric format you choose.
Tips
To apply a numeric format to all new tables, click Use as default.
If you have already defined a numeric format for the current column, you can apply it to the current cell. To do this, enable the Use same format as column check box on the Cell page.
To customize a date/time format
1 Select a table.
2 Click Table Numeric format.
3 Click one of the following tabs:
Cell-lets you define cell properties
Column-lets you define column properties
Table-lets you define table properties
4 Enable the Date/time option.
5 Click Custom.
6 Choose a format from the Date/time formats list box.
Note
If you enter a two-digit year of 00 to 29, WordPerfect assumes you mean the year 2000 to 2029. If you enter a two-digit year of 30 to 99, WordPerfect assumes you mean 1930 to 1999. Use four-digit years to specify other dates.
To line up numbers on the decimal point
1 Select a table.
2 Click Table Format.
3 Click one of the following tabs:
Column
Table
4 Enable one of the following decimal alignment options:
From right margin
Digits after decimal
Naming tables
You can name tables, floating cells, and table components, such as cells, a range of cells, rows, or columns. Naming a table or table component lets you reference it quickly. It is easier to remember a name, such as "Balance," than to remember a cell address. A name can contain letters, numbers, spaces, and the symbols #, $, ?, @, or _; however, the first character must be a letter, number, or underscore (_). You can also use text in a cell to name a table or table component.
You can edit and delete the names of tables, floating cells, and table components, such as cells, a range of cells, rows, or columns.
To name a table
1 Select a table.
2 Click Table Names.
3 Choose the default table name from the Table/floating cell list box.
4 Click Edit.
5 In the Edit table name dialog box, type a name in the Name box.
To name a table component
1 Click a table component.
2 Click Table Names.
3 Click Create.
4 In the Create table name dialog box, enable one of the following options:
Cell/range-specifies the cell or range in which the cursor is positioned
Row-specifies the row in which the cursor is positioned
Column-specifies the column in which the cursor is positioned
5 Type a name in the Enter name box.
To name table components using text in a cell
1 Select a cell that contains the text you want to use.
2 Click Table Names.
3 Click Create.
4 In the Create table name dialog box, enable the Use text in current cell to name option.
5 Enable one of the following options:
Row-names the row in which the cursor is positioned
Column-names the column in which the cursor is positioned
Next cell to right-names the cell to the right of the cell in which the cursor is positioned
Next cell down-names the cell below the cell in which the cursor is positioned
To edit the name of a table component
1 Select a table component, and click Table Names.
2 Choose the name of the table component from the Cell, row, column, or range list box.
3 Click Edit.
4 In the Edit table name dialog box, type a new name in the Name box.
If you want to delete a name, click Delete.
Using operators
You can use arithmetic and logical operators in table formulas. Logical operators compare two or more numeric or text values and return a result of 1 if the statement is true or 0 if the statement is false. You can also protect your formulas from being deleted accidentally. WordPerfect includes more than 90 preset functions for mathematical, engineering, or scientific calculations. For information about each function, see "Reference: Using tables and charts."
Arithmetic operators
Use the following arithmetic operators to create formulas in tables and floating cells.
Operator What it does
+ (Addition) Adds values.
- (Negation) Makes values negative.
- (Subtraction) Subtracts the value on the right from the value on the left.
* (Multiplication) Multiplies values.
/ (Division) Divides the value on the left by the value on the right.
= (Total) Returns a total of the sums from the + operator.
% (Remainder) Returns the remainder of the value on the left divided by the value on the right.
% (Percent) Divides the value on the left of the operator by 100. Make sure that there is another operator or a separator following the Percent operator, or that the Percent operator is the last operator in the formula. If a percent sign (%) is between two values, it becomes the Remainder operator.
^ (Power) Raises the value on the left to the power indicated by the value on the right.
! (Factorial) Calculates the factorial of the number preceding it. Do not place an equal sign (=) following the Factorial operator; otherwise, it becomes the Not equal to operator sign.
Logical operators
You can use the following logical operators for calculations in tables or floating cells.
Operator What it means or does
0 Equal to.
> Greater than.
< Less than.
< > or != Not equal to.
>= Greater than or equal to.
<= Less than or equal to.
& (AND) When placed between two or more logical statements, returns a 1 if all the statements are true and a 0 if any of the statements are false.
! (NOT) Inverts the meaning of a function or another operator.
| (OR) When placed between two or more logical statements, returns a 1 if at least one of the statements is true and a 0 if all the statements are false.
^^ (XOR) When placed between two or more logical statements, the EXclusive OR operator (XOR) returns a 1 if one or the other but not both of the statements in the formula are true (or if an odd number of statements are true). It returns a 0 if neither or both of the statements are true (or if an even number of the statements are true).
Entering formulas in tables
WordPerfect lets you add formulas or functions to tables. You can create formulas in tables by using arithmetic and logical operators. You can then insert the formulas in tables and floating cells. For more information about arithmetic and logical operators, see "Using operators."
The cells in a table are labeled alphabetically from left to right and numerically from top to bottom. When you use a cell address, a comma is used to separate individual cell references; for example, if you want to add the contents of B1 and B4, you would use the formula =SUM(B1,B4). A colon is used to separate two references in a range; for example, to add all of the contents of B1 through B4 you would use =SUM(B1:B4). Table cell references can be uppercase or lowercase.
You can insert and copy formulas and functions in table cells and floating cells. You can also type formulas directly into cells if you precede the initial symbols, functions, or numbers with + or =. However, it is best to enter formulas directly only if you know how to use functions and you remember the names you want to reference in formulas. Formulas you enter are calculated automatically when you move the insertion point out of the cell that contains the formula. You can also ignore a cell or a group of cells during calculations.
When you enter a formula in a table cell, an indicator will display in the lower right-hand corner of the cell. You can view the formula by pointing anywhere inside the cell. You can drag the formula indicator to another cell to copy the formula. You can also change the table settings if you do not want to show the table formula indicators.
To enter a formula in a table
1 Click Table Formula toolbar.
2 Click in the Formula edit box to the right of the Accept the formula button.
3 Type cell addresses, operators, and values in the Formula edit box.
To insert the name of a table instead of a cell address, click Names on the Table formula toolbar, click a name, and click Insert.
4 Click one of the following:
Accept the formula-lets you insert the formula into the cell
Cancel changes-lets you cancel the formula or changes you have made
Notes
WordPerfect does not recognize formulas in cells in which the numeric format is set to Text.
When you insert or cancel a formula, the Formula edit mode is disabled.
Tip
You can also click the Formula toolbar button on the property bar to display the Table formula toolbar.
To create a function
1 Click Table Formula toolbar.
2 Click in the cell in which you want to place the function.
3 Click the Functions button on the Table formula toolbar.
4 Choose a function type from the Type of functions to list list box.
5 Choose a specific function from the Functions list.
A description of each function displays in the section below the Type of functions to list and Functions lists.
6 Click Insert.
7 Type cell addresses, operators, and values of the function in the Formula edit box.
If you want to nest functions, type in the values of the initial function in the Formula edit box, and do the following:
Click the Functions button on the Table formula toolbar.
Choose a function type from the Type of functions to list list box.
Choose a function from the Functions list.
A nested function can be placed in any location within the previous function.
Type cell addresses, operators, and values of the nested function in the Formula edit box.
Tip
In the Table functions dialog box, you can type the first few letters of a function to select it quickly.
To copy a table formula
1 Click in the cell that contains the formula you want to copy.
2 Click Table Copy formula.
3 Enable one of the following options:
To cell-lets you type the name of the cell in which you want to paste the formula
Down-lets you choose the direction from the list box, and type the number of times to paste the formula up, down, left, or right
Tips
You can also click the Copy formula button on the Table formula toolbar. To display the Table formula toolbar, click Table Formula toolbar.
You can also copy a table formula by dragging the table formula indicator from one cell to another.
To protect table formulas
1 Click Tools Settings.
2 Click Environment.
3 Click the Prompts tab.
4 Enable the Confirm deletion of table formulas check box.
To disable the table formula indicators
1 Click Tools Settings.
2 Click Display.
3 Click the Document tab.
4 In the Show area, disable the Table formula indicators check box.
Note
By default, the Table formula indicators box is enabled.
Calculating formulas in tables
WordPerfect lets you perform spreadsheet calculations on table data. For example, you can calculate the sum of table cell values, list and use functions and names, calculate formulas, and fill in cells with incrementing patterns.
You can add values in a table quickly, and you can ignore a cell during calculations.
You can also fill a table with incrementing numbers or dates. A pattern of values must increment or decrement based on addition or subtraction. You cannot fill in a pattern of values that increment or decrement based on multiplication or division.
You can get more information about table formula errors, including a brief explanation of any ?? and ERR error symbols that display in a table as you calculate formulas.
To calculate a formula in a table
1 Click in a table.
2 Click Table Calculate.
3 Enable one of the following options:
Calculate table-calculates the table or floating cell in which the insertion point is positioned
Calculate tables in document-calculates all tables and floating cells in the document
To add values in a table quickly
1 Click the table cell below or to the right of the cells whose total you want to calculate.
2 Click Table QuickSum.
Note
If you click in a cell instead of selecting a range of cells, the calculated sum overwrites any values, formulas, or text in the cell.
Tip
You can also add values by clicking the QuickSum button on the Table formula toolbar. To display the Table formula toolbar, click Table Formula toolbar.
To ignore a table cell during calculations
1 Select a cell, and click Table Format.
2 Click the Cell tab.
3 Enable the Ignore cell when calculating check box.
To fill a table with incrementing numbers or dates
1 Select the cells that contain the incremental or decremental pattern of values.
2 Click Table QuickFill.
Notes
You can extend the selection to include the cells in which you want to continue the pattern.
You can use Roman numerals, days of the week, months, or quarters, even if only one cell is used to establish the pattern. To repeat incremental or decremental numbers, however, you must select at least two cells with values to establish a pattern.
Tip
You can also fill a table with incrementing numbers or dates by clicking the QuickFill button on the Table formula toolbar. To display the Table formula toolbar, click Table Formula toolbar.
To get more information about table formula errors
1 Click a cell that contains an error.
2 Click the View error button on the Table formula toolbar.
Creating charts
WordPerfect lets you create and insert charts and graphs in a document. You can create a chart by using data in a table or a spreadsheet, or you can add data as you create the chart. A chart can be linked to a table so that the chart is updated whenever the table data changes. If you move the chart to a different place in the document, it maintains its association with the table.
The following image is an example of a data chart.
When you create a chart in WordPerfect, Corel Presentations menus and tools display. These menus and tools let you choose a chart type and edit the chart. You can choose from a variety of chart types, such as bar, line, and pie, and you can add chart attributes, such as a legend or frame. You can also use Corel Presentations to add text to charts; however, the text is saved as part of the chart, rather than with the document.
You can create an organization chart to display the structure of an organization or the relationships of people and positions.
The following image is an example of an organization chart.
You can edit and update charts and graphs. You can also edit the data that the chart is based on.
You can use the data in a table to create a chart containing the same information. When you create a chart from a table, the two are associated. You can specify that charts associated with tables automatically update when you make changes to the data in the tables.
For more information about charts, refer to the Corel Presentations online Help.
To create a data chart
1 Click where you want to insert a chart.
2 Click Insert Chart.
3 Click Chart Gallery, and choose a chart type.
4 In the Datasheet, click or double-click cells to edit their contents.
5 Click Chart Layout/type.
6 In the Layout/type properties dialog box, choose style and appearance options.
7 Click Chart Series.
8 In the Series properties dialog box, choose line and fill colors.
9 Click outside the chart to return to the document window.
Tips
To import data from a spreadsheet or file, click Data Import after Corel Presentations menus and tools display, and specify the filename of the document you want to import.
You can use this procedure to create a chart from a table. Create a table with the data to include in a chart and click in the table.
To insert an existing chart
1 Open the document that contains the chart using the application in which it was created (such as Corel Presentations or Quattro Pro).
2 Select the chart, and click Edit Copy.
3 In the WordPerfect document, click Edit Paste special.
To create an organization chart
1 Click where you want to insert the organization chart.
2 Click Insert Graphics Draw picture.
Corel Presentations menus and tools display, and a drawing frame displays in the document window.
3 Click Insert Organization chart.
4 Click in the drawing frame.
5 Click a chart type.
6 Click OK.
7 Double-click a box in the chart, and type a name and title to replace the placeholder name.
8 Click outside the chart to return to the document window.
Tip
You can also create an organization chart by playing the wp_org.wcm macro. Click Tools Macro Play, and double-click wp_org.wcm. For information about macros, see "Playing macros."
To edit or annotate a chart or graph
1 Right-click a chart or a graph, and click Chart object.
2 Enable one of the following options:
Edit-lets you edit the chart or graph in the active document
Open-lets you open the chart or graph to the full size of the screen
Annotate-lets you draw lines and add text to the chart or graph
Tips
For in-place editing, you can also double-click the chart or graph.
For full-screen editing, you can also hold down ALT while double-clicking the chart or graph.
To edit chart data
Double-click a chart.
Corel Presentations menus and tools display.
To Do the following
Edit the chart title Double-click the title, change the text, and select the font, size, and other attributes you want to use.
Add a subtitle Click Chart Subtitle, and type the subtitle.
Change any other chart element Right-click an item and click the Properties option.
Note
You can display the datasheet by clicking View Datasheet.
Tip
You can update the chart that is based on the table in which you have edited data by right-clicking the chart and clicking Update chart from table.
To update charts associated with tables
1 Click in a table.
2 On the property bar, click Table Calculate.
3 Enable one of the following options:
Calculate table
Calculate tables in document
4 Enable the Update associated charts check box.
Tip
To update charts associated with tables manually, right-click the chart, and click Update chart from table. Automatic calculation must be disabled to update a chart manually.
Reference: Using tables and charts
Using WordPerfect table functions
You can learn the basics of using table functions, descriptions of commonly used argument keywords, and definitions and examples of all the functions listed in the Table functions dialog box.
Each WordPerfect table function has a name and is followed by parentheses. If a function needs arguments (information), argument keywords display inside the parentheses.
For example, the CHOOSE function displays as
CHOOSE(Number, List)
in the Table functions dialog box. "Number" and "List" are the arguments of the function. The arguments are separated by a comma, and each argument is represented by a keyword that describes the type of information needed. Spaces are not needed between values, operators, and functions. Letters in addresses and functions can be uppercase or lowercase.
If a function needs no arguments, the function name is followed by empty parentheses:
COL( )
Brackets ( [ ] ) surrounding a keyword indicate that the keyword is optional and may be omitted:
CHAR([Set Number,] Number)
Do not include the brackets when entering the function.
Argument keyword descriptions
The following is a list of the most common argument keyword descriptions. You can click each one to find out more about it.
Block
Cell
Date/time
List
Value
Depreciation keywords
Growth/amortization keywords
WordPerfect table function listing
The following is a list of the WordPerfect table functions by category. You can click each one to find out more about it.
Date table functions
Mathematical table functions
Financial table functions
Logical table functions
Miscellaneous table functions
String table functions
Date table functions
Function
DATE( ) Returns the current date in the format you have selected for that cell, using Custom Date Types. If you have not selected a date format for the cell, the date will display in the document's current date format.
DATETEXT(Date) Converts Date (a date value) into text (see DATEVALUE below). The format of the date text depends on the date setting in Custom Date Types or the document's current date format (see DATE above).
DATETEXT(35711) = October 9, 1997
DATEVALUE(Text) Returns the date value for the given text string. Each date from January 1, 1900, to April 19, 65493, is assigned a consecutive number in WordPerfect. The format of the date text depends on the setting in Custom Date Types or the document's current date format (see DATE above). You can use all digits in the year or only the last two when you enter the text string.
If only two digits are given for the year, the function adds 1900 to the year. If no year is given, the function uses the current year.
DATEVALUE("6/24/96") = 35239
DATEVALUE('5-19') = 35933 if the year is 1998
You can use MDY to calculate the date value from numeric values rather than a text string or to quickly calculate the date value for the current date.
DAY([Date Value]) Returns the day of the month (a number between 1 and 31) for the given date value.
DAY(35603) = 23
35603 is the date value for June 23, 1997. For details about date values, see DATEVALUE above. If you do not include a date value, the function returns the current day.
HMS([Hour], [Minute], [Second]) Returns the specified or current time of day as a decimal number (time value). Hour must be an integer from 0 to 24, and the Minute and Second arguments must be integers from 0 to 59. HMS then converts the given time to a decimal number from 0 to 0.999988, with 0 equal to 12:00 midnight and 0.5 equal to 12:00 noon.
HMS(9,42,38) = 0.404606
HMS may be entered in one of four ways:
HMS(Hour,Minute,Second)
HMS(Hour,Minute)
HMS(Hour)
HMS( )
If you include the hour with no minute or second argument, the function assumes the minute or second values to be zero. The hour, minute, and second arguments are optional. If none of them are specified, the function returns the decimal number converted from the current computer time.
To calculate the decimal value of a time specified by a text string, use TIMEVALUE.
HOUR([Time Value]) Returns the hour of the given or current time. Only the decimal portion of a number is recognized. For details on time values, see HMS above.
HOUR(0.404606) = 9, because 0.404606 is the decimal value for 9:42:38.
The time value argument is optional. If no time value is specified, the function returns the current hour of the day based on the current computer time.
MDY([[Month, Day], [Year]]) Returns the date value of the specified month, day, and year. For details on date values, see DATEVALUE earlier in this appendix.
The month must be a number from 1 to 12, the day must be a number from 1 to 31, and the year can be any year from 1900 through 65535. If you enter a two-digit number for the year, the function adds 1900 to the number (that is, 96 is the same as 1996). Because each piece of date information is treated as a separate argument, the names or addresses of cells containing appropriate numbers can be used for the month, day, and year entries.
MDY can be entered in one of three ways:
MDY(Month,Day,Year)
MDY(Month,Day)
MDY( )
The date format depends on the settings in Custom Date Types or in the document's current date format.
Any arguments that are not included are assumed to be the current month, day, or year.
MDY(8,4,96) = 35280
MDY(8,4) = 35280 if the current year is 1996
MDY( ) = the current date value
You can use DATEVALUE to calculate the date value from a text string rather than from numeric entries.
MINUTE([Time Value]) Returns the minute (0-59) of the specified or current time. The time must be given as a decimal number. For details on time numbers, see HMS earlier in this appendix.
MINUTE(0.404606) = 42, because 0.404606 is the decimal value for 9:42:38.
The time value argument is optional. If it is not specified, the function returns the current minute based on the current computer time.
MONTH([Date Value]) Returns the number of the month for the specified date value. For details on date values, see DATEVALUE earlier in this appendix. If you do not include a date value, the number of the current month is returned.
MONTH(35280) = 8, because 35280 is the date value for August 4, 1996.
SECOND([Time Value]) Returns the second (0-59) of the given or current time. The time must be given as a time value. See HMS earlier in this appendix for details on time values.
SECOND(0.404606) = 38, because 0.404606 is the decimal value for 9:42:38.
The time value argument is optional. If it is not specified, the function returns the current second based on the current computer time.
TIME( ) Returns the current time as a text string. The time is updated each time the worksheet is recalculated.
The time format depends on the settings in Custom Date Types or the document's current date format.
TIMETEXT(Time) Returns the given time as a text string. TIMETEXT only uses the decimal portion of a number. See HMS earlier in this appendix for more information on time values.
TIMETEXT(0.404606) = 9:42am
The time format depends on the settings in Custom Date Types.
TIMEVALUE(Text) Returns the decimal value of the time indicated by the given text string. See HMS earlier in this appendix for more information on time values.
Text must be in one of the following formats:
"hour"
"hour:minute"
"hour:minute:second"
The hour must be an integer from 0 to 24, and the minute and second must be integers from 0 to 59. You can also include "am" or "pm."
TIMEVALUE("19:42:05") = 0.820891
To calculate a decimal value for a time entered as numbers rather than text, or to quickly calculate the decimal value of the current time, use HMS.
YEAR([Date Value]) Returns the year for the given Date Value. Returns the current year if no argument is included. See DATEVALUE earlier in this appendix for information about date values.
Date Value can be any integer from 1 (January 1, 1900) to 23,227,139 (October 19, 65493).
YEAR(35678) = 1997, because 35678 is the date value for September 6, 1997.
Mathematical table functions
Function
ABS(Number) Returns the absolute value of the given number. Positive numbers remain positive; negative numbers become positive.
ABS(-3) = 3
ACOS(Number) Returns the arccosine (cos-1) of the given number. The result is an angle in radians from 0 to p. Number must be greater than or equal to -1 and less than or equal to 1.
ACOS(.5) = 1.047198 (or p/3)
Angles expressed in radians can be expressed in degrees using DEGREES.
DEGREES(ACOS(.5)) = 60
ASIN(Number) Returns the arcsine (sin-1) of the given number. The result is an angle in radians from -p/2 to p/2. Number must be greater than or equal to -1 and less than or equal to 1.
ASIN(.5) = 0.523599 (or p/6)
Angles expressed in radians can be expressed in degrees using DEGREES.
DEGREES(ASIN(.5)) = 30
ATAN(Number) Calculates the arctangent (tan-1) of the given number, returning an angle in radians from -p/2 to p/2. Number can be any value.
ATAN(1) = 0.785398 (or p/4)
Angles expressed in radians can be expressed in degrees using DEGREES.
DEGREES(ATAN(1)) = 45
ATAN2(Xnumber, Ynumber) Returns the arctangent (tan-1) of the angle represented by a point with the coordinates Xnumber and Ynumber. An angle is formed between the X axis and a line drawn from the indicated point (Xnumber, Ynumber) to the origin (0,0). The arctangent is then calculated on that angle. The result is a value in the range of -p( ) to p( ).
ATAN2(3,4) = 0.927295
AVE(List) Returns the average (mean) of a list of numbers. The average of the null set AVE( ) equals NA.
AVE(15,2*10,SUM(1,2,4)) = 14
COS(Number) Returns the cosine of the given number, which must be an angle in radians.
COS(PI( )/3) = 0.5
DEGREES(Number) Converts the angle represented by the given number from radians to degrees.
DEGREES(PI( )/4) = 45
DEGREES is the inverse function of RADIANS. In other words, if DEGREES(PI( )/4) = 45, then RADIANS(45) = 0.785398 (or p/4).
EXP(Number) Calculates the base of the natural logarithm, or "e" (2.7182818), to the power indicated by the given number.
EXP(4) = 54.59815
EXP is the inverse function of LN. In other words, if EXP(4) = 54.59815, then LN(54.59815) = 4.
FACT(Number) Returns the factorial of the specified number. The factorial of a number is the product of all the integers less than and equal to that number and greater than zero. For example, the factorial of 4 is 4 * 3 * 2 * 1 = 24. The smallest value that can be used is 0, and the largest value that can be used is 170. (The factorial of 0 equals 1.)
FACT(3) = 6
Only the integer portion of a number with decimal places is used to perform the factorial function. For example, FACT(3.9) = 6, because 3! = 6.
You can also use the Factorial operator (!) to calculate factorials.
* (GRAND TOTAL) In the current column, adds the values in all cells above the formula cell that contain a TOTAL(=) calculation. (See = (TOTAL) later in this appendix.)
This function is not listed in the Table Functions dialog box and it does not require you to type a name-just type an asterisk (*) to perform a grand total. You can also quickly sum values in cells using the QuickSum feature.
For example, type an asterisk (*) in cell A40 to add the totals in the cells above A40.
INT(Number) Deletes any fractional portion of the given number, returning only the integer portion.
INT(25.77) = 25
LN(Number) Calculates the natural logarithm of the given number. The number must be greater than zero.
LN(2) = 0.693147
LN is the inverse function of EXP. In other words, if LN(2) = 0.693147, then EXP(0.693147) = 2.
LOG(Number1, Number2) or LOG(Number) Calculates the logarithm of Number1 to the base specified by Number2. The numbers must be positive. If only one number is specified, the base 10 logarithm of that number is calculated.
LOG(16,4) = 2
LOG(1000) = 3
MAX(List) Returns the largest number in the given list of numbers.
MAX(25,10,15) = 25
If you do not include any numbers (for example, MAX( )), the function returns NA.
MIN(List) Returns the smallest number in the given list of numbers.
MIN(25,10,15) = 10
If you do not include a list of numbers, MIN( ) returns NA.
MINUS(Number) Returns the negative of the given number. If the number is already negative, a positive number is returned.
MINUS(25) = -25
MINUS(-3) = 3
You can also use the Negation operator (-) to do the same thing.
MOD(Value1, Value2) Returns the remainder after dividing Value1 by Value2. The values may be negative or positive. However, Value1 must have a greater absolute value than Value2 for MOD to work properly.
MOD(6,2) = 0
MOD(7,2) = 1
MOD(-7,2) = -1
You can also use the remainder operator (%) to do the same thing. For more information, see "arithmetic operators" in the WordPerfect online Help.
PI( ) Returns the number 3.141592653589793. This is an approximation of the number p.
SIN(PI( )/2) = 1
POWER(Num1, Num2) Calculates Num1 raised to the power of Num2.
POWER(3,2) = 9
You can also use the Power operator (^) to do the same thing. For more information, see "arithmetic operators" in the WordPerfect online Help.
PRODUCT(List) Returns the product of all the numbers in the list.
PRODUCT(2,3,4,A1:B4) = 1296
The result in this example above is the product of 2, 3, 4, and all the numbers in the range A1:B4.
You can also use the Multiplication operator (*) to do the same thing. For more information, see "arithmetic operators" in the WordPerfect online Help.
QUOTIENT(List) Divides the first number in the list by the second number, divides that result by the third number, divides that result by the fourth number, and so on.
QUOTIENT(160,5,8,2.5) = 1.6
This example is calculated in the following order:
((160 / 5 = 32) / 8 = 4) / 2.5 = 1.6
You can also use the Division operator (/) to do the same thing. For more information, see "arithmetic operators" in the WordPerfect online Help.
RADIANS(Number) Converts the given number from degrees to radians.
RADIANS(90) = 1.570796 (or p/2)
RADIANS is the inverse function of DEGREES. In other words, if RADIANS(90) = 1.570796, then DEGREES(1.570796) = 90.
RANDOM([Number]) Returns a random number greater than or equal to 0 and less than 1.
You can manipulate RANDOM by using a number as the argument. If the given number is positive or there is no number argument, RANDOM returns the next random number in the sequence. If the given number is zero, RANDOM returns the previous random number again, without generating a new number in the sequence. If the given number is negative, RANDOM starts a new sequence of random numbers using the negative number to generate the starting number.
ROUND(Number, Precision) Returns the Number rounded to the decimal place specified by Precision.
Precision can be up to 15 decimal places. You can also specify a negative Precision, which rounds the number to the left of the decimal. A Precision value of -1 rounds to the nearest 10, -2 rounds to the nearest 100, and so on.
ROUND(125.388,1) = 125.4
ROUND(125.388,-1) = 130
SIN(Number) Returns the sine of the given number (an angle in radians).
SIN(PI( )/6) = 0.5
SQRT(Number) Returns the square root of the given number. The number must be positive (greater than zero).
SQRT(25) = 5
STDEV(List) Returns the standard deviation of a sample from the list of numbers.
STDEV(25,15,20) = 5
STDEVP(List) Returns the standard deviation of a population.
STDEVP(25,15,20) = 4.082483
+ (SUBTOTAL) Adds the numbers in the cells directly above the cell that contains the subtotal function.
Note that this function is not listed in the Table Functions dialog box and does not require you to type a name-just type a plus sign (+) to perform a subtotal. You can also quickly sum values in cells using the QuickSum feature. For more information, see "QuickSum" in the WordPerfect online Help.
Type + in cell A4 to add the values in the cells above A4.
SUBTRACT(List) Subtracts the second number in the list from the first, then subtracts the third number from that result, the fourth from that result, and so on.
SUBTRACT(57,22,6,18) = 11
The example above is calculated in the following order:
((57 - 22 = 35) - 6 = 29) - 18 = 11
You can also use the Subtraction operator, the minus sign (-), to do the same thing.
SUM(List) Returns the sum of the numbers in the list. You can also specify a range of cells or use the name of a range of cells, as shown.
SUM(25,10-2,3) = 36
SUM(a1:a10)
SUM(bonus)
You can also use the Addition operator, the plus sign (+), to do the same thing.
TAN(Number) Returns the tangent of the given number (an angle in radians).
TAN(PI( )/4) = 1
= (TOTAL) In the current column, adds the values in all subtotal cells above the formula (subtotal cells contain a SUBTOTAL(+) calculation). See + (SUBTOTAL) earlier in this appendix.
This function is not listed in the Table functions dialog box and does not require you to type a name-just type an equal sign (=) to perform a total. You can also quickly sum values in cells using the QuickSum feature.
Type = in cell B35 to add the subtotals in the cells above B35.
VAR(List) Returns the sample variance for a list of numbers. List must include at least two numbers.
VAR(20,22,19,23,21) = 2.5
VARP(List) Returns the population variance for a list of numbers.
VARP(68,71,67,66) = 3.5
The list must include at least one number.
Financial table functions
Function
DDB(Cost, Salvage, Life, Period) Returns the depreciation amount using the double-declining balance method of calculating depreciation.
This method accelerates the rate of depreciation, so that more depreciation is written off in early periods than in later periods. The depreciation stops when the book value of the asset reaches the salvage value. In any one period, the book value equals the cost minus the total depreciation over all previous periods.
DDB(5000,750,5,2) = 1200
In this example, the Cost of the asset is $5,000, the Life of the asset is 5 years, the Salvage value after 5 years is $750, and to find the amount of depreciation in the second year, a Period of 2 is used.
FV(Rate%, PV, Payment, Periods, [Type]) Returns the future value of an investment or loan.
The example below finds the future value of an investment with no periodic payments. This investment is a six-month certificate of deposit (CD) with a $5000 deposit and an interest rate of 12.5%, compounded daily. The following formula finds the balance of the account at the end of the six months:
FV(12.5%/365,-5000,0,365/2,0) = 5322.42
The interest rate percentage is divided by 365 because it is compounded daily. The period must also be expressed in days (365/2 equals six months). The present value (PV) is expressed as a negative number because you pay it at the beginning of the period. The result is positive because you get it back at the end of the period.
The example below finds the future value of a loan after a specified number of periods. This loan is a $90,000 mortgage for 30 years at an annual interest rate of 11.75%. The monthly payment is $908.47 and is paid at the end of the period. The following formula finds the balance of the loan after 20 years:
FV(.1175/12,90000,-908.47,20*12,0) = -63962.22
The interest rate is expressed as a decimal instead of a percentage. It is divided by 12 because it is compounded monthly. Because the rate is expressed in months, the period must also be expressed in months. The present value of the loan is a positive number because it is paid to you. The future value of the loan is returned as a negative number because it is money still owed on the loan.
IRR(List of Cashflows, Rate%) Calculates the internal rate of return for a list (series) of cash flows. The first cash flow in the series is negative, representing the initial investment. An estimate of the rate of return (rate argument) must be specified as a percentage.
IRR uses a sequence of estimates to calculate the internal rate of return. Because IRR uses approximations, you enter only an estimate for the second argument.
In this example, the IRR for Project 4 is 7.07% (shown in cell H7). The Rate% used is 14%.
NPV(List, Rate%) Calculates the net present value (the amount of money required now to produce the given cash flow in the future). The calculation is based on the specified interest rate.
Each item in the list is the cash flow required at the end of a period. The cash flows must be listed in the order the periods occur (first period, second period, and so on). The period of the interest rate and the required cash flow must be the same.
The interest rate can be entered as a decimal fraction (for example, .25) or as a percentage (for example, 25%).
In this example, the numbers in cells B3, B4, and B5 are 15000, 20000, and 25000, respectively.
NPV(B3:B5,12%) = 47,131.24
PMT(Rate%, PV, Periods, FV, [Type]) Returns the payment for a loan or investment.
The following example finds the periodic payment needed to fully amortize (eliminate the balance of) a $125,000 mortgage for 30 years at an annual interest rate of 7.25%. To find the monthly payment made at the end of the period, use the following formula:
PMT(7.25%/12,125000,30*12,0,0) = -852.72
The interest rate percentage is divided by 12 because it is compounded monthly, and therefore the period must also be expressed in months. The payment is negative because it is the amount paid to the lender, and the future value is 0 because the loan will be fully amortized. The type is 0 because payments occur at the end of the period.
The following example finds the payment required for an investment with a specific present value to obtain a specific future value. The investment is a savings account that pays an annual interest rate of 8.5% with an initial deposit of $2,000. To find the required periodic deposit made at the end of the period to obtain a balance of $3,500 in 18 months, use the following formula:
PMT(8.5%/12,-2000,18,3500,0) = -64.26
The interest rate percentage is divided by 12 because it is compounded monthly, and the period must also be expressed in months. The present value (PV) is negative because it is money that is paid into the savings account. The type is 0 because the payment is made at the end of the period. The resulting payment (PMT) is also negative because it is money that must also be paid into the savings account.
The following example finds the periodic payment for an investment to obtain a specific future value with no present value. The investment is a profit-sharing program with your company in which a set amount is deducted from your paycheck at the end of each month. Your company offers an annual interest rate of 32%, and you want to have a balance of $1,500 in one year. To find the amount you need to have deducted from your paycheck, use the following formula:
PMT(.32/12,0,12,1500,0) = -107.71
The interest rate in this example is expressed as a decimal. The rate is consistent with the period because both are expressed in months. The present value is 0, because you start with nothing. The resulting payment is negative, because you must pay it from your paycheck.
PV(Rate%, Payment, Periods, FV, [Type]) Returns the present value for a loan or investment.
The following example finds the amount of a loan that you want to fully amortize at a given interest and payment rate and in a specific number of periods. The loan is a 30-year mortgage, with an annual interest rate of 7.125%. If you want to make payments of $850 at the end of each period, you can find the amount you can borrow by using the following formula:
PV(7.125%/12,-850,30*12,0,0) = 126165.45
The interest rate percentage is divided by 12 because it is compounded monthly, and therefore the period must also be expressed in months. The payment is negative because it is the amount paid to the lender, and the future value is 0 because you want to fully amortize it. The type is 0 because payments occur at the end of the period.
The following example finds what an investment's present value would have to be to reach a specified future value at a certain payment and period. The investment is a savings account that pays an annual interest rate of 5.5%. If you want to make a deposit of $45 at the end of each month and see a return of $2,500 after 18 months, use the following formula to find the amount you need to initially deposit:
PV(.055/12,-45,18,2500,0) = -1526.68
The interest rate is expressed as a decimal. The rate is consistent with the period because both are expressed in months. Payment is a negative number because it is an amount paid into the account. The resulting present value is also negative because it must also be paid into the account. The FV is positive because it is money that you will receive from the account after 18 months.
RATE(PV, Payment, Periods, FV, [Type]) Returns the periodic interest rate for a loan or investment. The rate returned is based on the Periods. If the period is other than a year, you can adjust the result of the function to obtain the yearly rate. For example, if you use months to state the number of periods in this function, the result will be given as a monthly rate. To convert this monthly rate to a yearly rate, multiply the result by 12.
The following example finds the interest rate required for an investment to obtain the specified future value with the specified payment and periods, with no present value. If you want to invest $50 at the end of each month for 2 years (24 months) and receive a return of $1,500, use the following formula to find the necessary monthly interest rate:
RATE(0,-50,24,1500,0) = 0.018854
The period is expressed monthly (24), therefore the interest rate is also expressed monthly. Multiply the resultant rate (1.89%) by 12 to get the annual rate, approximately 22.6%. The payment is negative because it is an amount you must pay into the investment fund. The future value (FV) is positive because it will be paid to you. The type is 0 because payments occur at the end of each period.
The following example finds the interest rate necessary for a loan to be fully amortized in the specified periods with the given present value and payment. An $8,000 loan is paid over 36 months, and you want to make payments of $270 at the end of each period. To find the required interest rate, use the following formula:
RATE(8000,-270,36,0,0) = 0.01093
The period is expressed monthly (36), therefore the interest rate is also expressed monthly. Multiply the resulting rate (1.09%) by 12 to get the annual rate, approximately 13.1%. The payment is negative because it is an amount that you pay the lender. The type is 0 because payments occur at the end of each period. The present value of $8,000 is positive because it is an amount received from the lender and paid to you.
SLN(Cost, Salvage, Life) Returns the straight-line depreciation amount for one period. The straight-line method takes the amount of depreciation for an asset (cost minus salvage) and divides it evenly over the life of the asset.
SLN(5000,800,6) = 700
In this example the cost of the asset is $5,000, the life of the asset is 6 years, and the salvage value is $800 at the end of its life. The formula determines the amount of depreciation for each year.
SYD(Cost, Salvage, Life, Period) Returns the sum-of-years digits depreciation amount for the specified period. This method accelerates the rate of depreciation, so that more of the asset is depreciated at the beginning of the asset's life than at the end.
SYD(5000,800,6,4) = 600
In this example, the cost of the asset is $5,000, the life of the asset is 6 years, and the salvage value is $800. The period is 4 to find the depreciation amount for the fourth year.
TERM(Rate%, PV, Payment, FV, [Type]) Returns the term (number of periods) for a loan or investment.
The example below finds the number of periods required for an investment to obtain the specified future value with the given interest rate and payment and with no present value. You will obtain $3,000 by depositing $50 at the end of each month in a savings account that pays a yearly interest rate of 8.5%. To find the number of periods (months) it will take to accumulate $3,000, use the following formula:
TERM(8.5%/12,0,-50,3000,0) = 50.2
The interest rate percentage is divided by 12 because it is compounded monthly, and therefore the term is also months (50.2 months). The payment is negative because it is an amount to be paid into the investment. The future value is positive because it is an amount you will receive from the investment. The type is 0 because payments occur at the end of the period.
The example below finds the number of periods needed for a loan to fully amortize with the specified interest rate, present value, and payment. The loan's present value is $8,000 and you want to make payments of $230 at the end of the period. If the current interest rate is 11.5%, use the following formula to determine the length of the loan:
TERM(.115/12,8000,-230,0,0) = 42.5
The interest rate is expressed as a decimal and is divided by 12 because the period is monthly. Payment is a negative number because it is an amount you will pay to the lender. The present value is positive because it is an amount you will receive from the lender. The resultant term is expressed in months.
Logical table functions
Function
AND(List) Returns the logical AND of the given list of numbers. A list of logical statements is placed between the parentheses. If all the statements are true, the number 1 (true) is returned. If any of the statements are false, 0 (false) is returned.
AND(45>10,10+5=16,5<7) = 0
In the above list, the statement "10+5=16" is false, so 0 is returned.
You can also use the And operator (&) to do the same thing. For more information, see "arithmetic operators" in the WordPerfect online Help.
FALSE( ) Returns the logical value 0 (false). Use FALSE with functions that require a logical value of 0 (false), such as IF.
IF(B4<=100,True( ),False( )) = 0 when B4 contains a value greater than 100.
IF(Condition, Val1, Val2) Returns Val1 or Val2, depending on whether the condition is true or false. The condition can be any logical statement. If the statement is true, Val1 is returned; if it is false, Val2 is returned.
A value may be a number; text string; cell address or name; a function, such as NA, FALSE, or TRUE; or another formula.
IF(2<3,'True',NA( )) = True
IF(5*5<=0,25,10/2) = 5
ISERR(Cell) Returns the number 1 (true) if any formula error ("??" or "ERR") is found in the given cell. If no error is found, 0 (false) is returned.
ISERR2(Cell) Returns the number 1 (true) if a real formula error ("??") is found in the given cell. If no real formula error is found, returns a 0 (false).
A real formula error is one that originates in the given cell and produces a "??" error message in the cell. An error ("ERR") that results from referencing another cell containing an error is not a real formula error.
For example, if 2.5/0 is entered in cell F10 and F10+1 is entered in cell F11, a "??" message displays in cell F10 and an "ERR" message displays in cell F11. In this case, ISERR2(F10) returns 1 and ISERR2(F11) returns 0.
ISNA(List) Determines if a cell or range is empty (or "NA"). The list may have one or more cell references. If the cells referred to in the list have no entries, the number 1 (true) is returned to the formula cell. If even one entry exists, 0 (false) is returned.
ISNA(tax,B11:B20,A5) = 0
In this example, the cell named "tax" and cell A5 have no entries, but there is an entry in the range B11:B20, so 0 is returned.
ISTEXT(List) Determines whether the cells referred to in the list contain text. If at least one of the cells in the list contains text and none of the cells contain numbers, 1 (true) is returned; if even one cell contains a number, 0 (false) is returned.
ISTEXT(A4,company,E5:G9) = 1
In this example, 1 is returned, because cell A4, the cell named "company," and four of the cells in the range E5:G9 contain text, and none of the cells contain a number.
ISVALUE(List) Determines whether the cells referred to in the list contain numbers. If at least one of the cells in the list contains a number and none of the cells contain text, 1 (true) is returned; if even one cell contains text, or if all the cells are empty, 0 (false) is returned.
ISVALUE(C6,interest,D22:H26) = 0
In this example, cell C6 contains text, so 0 is returned.
NA( ) Returns the NA (not available) value-an empty cell.
IF(2>3,'True',NA( )) = an empty cell
NOT(Number) Returns the number 1 to the formula cell if the given number equals zero; otherwise, 0 is returned.
NOT(25) = 0
NOT(2*0) = 1
You can also use the Not operator (!) to do the same thing. For more information, see "arithmetic operators" in the WordPerfect online Help.
OR(List) Returns the logical OR of the given list of numbers. A list of logical statements is placed between the parentheses. If at least one of the statements is true, the number 1 is returned to the cell. If all of the statements are false, 0 is returned.
OR(45>10,10+5>16,5>7) = 1
Even though two of the statements are false, the statement "45>10" is true, so 1 is returned.
You can also use the Or operator (|) to do the same thing. For more information, see "arithmetic operators" in the WordPerfect online Help.
SIGN(Number) Returns -1 if the number is negative, 1 if the number is positive, and 0 if the number is 0.
SIGN(-2) = -1
SIGN(2) = 1
TRUE( ) Returns the logical value of 1 (true).
IF(B5>200,True( ),False( )) = 1 if B5 contains a value greater than 200.
Miscellaneous table functions
Function
BLOCK(Col1, Row1, Col2, Row2) Returns a list of values from a range of cells in the table. Col1 and Row1 numbers specify one corner of the range, and Col2 and Row2 specify the opposite corner. Because this function returns a list of values rather than a single result, it must be used inside a function that expects a list, such as AVE, CONCAT, or SUM. BLOCK( ) cannot be used to reference another table.
The following example illustrates the use of BLOCK for the table shown below.
CONCAT(BLOCK(2,2,2,6)) = ABCDSummum
CONCAT returns the concatenated text strings from Col 2, Row 2 (cell B2) through Col 2, Row 6 (F2).
CELL(Col, Row) or CELL(Text) Returns the value in the cell specified by the given column and row numbers, or by the text string that names the cell.
These examples assume that cell B3 contains the number 15.
CELL(2,3) = 15
With CELL(Text), the text string specifies the cell address. For example, if cell H1 contains "B3," then
CELL("H1") = 15
CELL( ) cannot be used to reference another table.
CHOOSE(Number, List) Finds the value or string in List that is specified by Number. If Number is negative, the search goes right to left.
CHOOSE(2,10,11,12) = 11
CHOOSE(-1,"ten",11,"twelve") = twelve
SUM(CHOOSE(1,A1:B2,C1:C3)) = total of cells A1 through B2
In the first example, the number "11" is the second (2) value in the list. In the second example, the text string "twelve" is the first value from the right end of the list (-1). In the third example, CHOOSE returns the first (1) range A1:B2 to SUM.
COL( ) Returns a number representing the column where the function is entered. Column A is "1," column B is "2," column C is "3," and so on.
If COL( ) is entered in cell B10, 2 is returned.
COUNT(List) Returns a count of all values (numeric and text) in the given List. If the list includes a range, each cell in the range that contains a value is counted. If a cell is NA, it is not counted.
COUNT(2,'Total',A3,D4:D10) = 7
In this example, "7" is returned because 2 is a value, 'Total' is the name of a cell that contains a value, A3 contains a value, and four of the cells within the range contain values.
INDEX(Block, Col, Row) Returns the number or text from the cell indicated by the column and row offsets within the given range. Use positive or negative numbers to refer to a column and row in the range. Positive numbers refer to columns from the left to right beginning with the first column of the specified range, and refer to rows from the top down beginning with the first row of the specified range. Negative numbers refer to columns from the right to left beginning with the last column in the range (column -1), and refer to rows from the bottom up beginning with row -1.
INDEX(B3:F12,2,-3)
In this example, the column and row numbers refer to the cell at the intersection of the second column from the left and the third row from the bottom of range B3:F12 (which is cell C10).
LOOKUP(Block, Value, [Offset]) Looks up a value (text or a number) in a block of cells in a table. Depending on how the table is set up, either the first column or the first row of the block is used as the index. If there are more rows than columns in the block, LOOKUP uses the first column as the index; if there are more columns than rows, the first row is used. For LOOKUP to work properly, the entries in the index column (or row) must be in numeric order or in alphabetic order if you are using text strings.
The first argument of the function must be the name or coordinates of a range of cells. The range must include the index column (or row) and any portion of the table you want the function to look through. The second argument may be a number or a text string, depending on whether the table's index column (or row) consists of numbers or text strings. The third argument is optional and is discussed below.
With the range and index value entered, LOOKUP looks through the index column (or row) for the given value. When the value is found, the function goes to the last column of that row (if using an index column) or the last row of that column (if using an index row), then it returns the number or text found there. The terms "last column" and "last row" refer to the last column or row in the specified range. If the given index value is not found, LOOKUP finds the closest value that is numerically or alphabetically lower in value. You can use the LOOKUP direction indicators ( < and > ) in the value argument to specify the direction (lower or higher) in which you want the function to look when an exact match is not found. For example, LOOKUP(A12:E20,117>) specifies that 117 or the next highest value is returned.
If the value in LOOKUP is lower in value than the first value in the index column or row and you do not include the LOOKUP direction indicator, nothing is returned.
LOOKUP is useful when you do not know the exact index value or if you only need an approximate value returned from the table. If you want the table value returned that corresponds to an exact index value, use MATCH.
In the example below, cell A1 contains the function LOOKUP(A4:E12,117).
Because there are more rows than columns in the specified block of the table, the first column is used as the table index. The function returns the value (3045) found in the last column of the row containing the lookup value (117).
If the function contained 116 as the index value in the example above, LOOKUP would have returned the value (659) in the last column of the row that begins with the closest lower value (113).
If you want the function to return the value of a cell other than the last cell in the column or row, use the optional offset argument. The offset argument may be zero, a positive number, or a negative number. An offset number of 0 returns the value in the index column (or row). A positive offset number (n) returns the value in the nth column to the right of the index column or the value in the nth row below the index row. A negative offset number (-n) returns the value in the nth column to the left of the index column or the value in the nth row above the index row.
In the following example, 3 is entered as the offset number.
LOOKUP(A4:E12,117,3)
The value found in the third column to the right of the index column (African Adventure) is returned.
LOOKUP2(Block, Xvalue, Yvalue) Looks up a value in a table. LOOKUP2 is similar to LOOKUP, except that the value to be returned is specified by X and Y coordinates. The first (top) row of the specified range becomes the "X-axis," and the first column becomes the "Y-axis." Like the index column or row in LOOKUP, the X row and Y column must be in numeric or alphabetic order for LOOKUP2 to work properly. You can, however, have an X row of numbers and a Y row of text values or vice versa.
The X and Y values may be numbers or text strings, depending on whether the X row and Y column contain numbers or text strings. After entering LOOKUP2, the value in the table below the X value and across from the Y value is returned to the formula cell.
If the exact X and/or Y values are not found in the X row and/or Y column, the function goes to the closest value that is numerically or alphabetically lower in value. You can use the LOOKUP direction indicators ( < and > ) to indicate in which direction you want the function to look when the exact value is not found. If you want only the table value returned that corresponds to the exact X and Y values given, use MATCH2.
If the value in LOOKUP2 is lower in value than the first value in the index column or row and you do not include the LOOKUP direction operator, nothing is returned.
MATCH(Block, Value, [Offset]) Looks up a value in a table. MATCH is used in the same way as LOOKUP, except the specified value must match exactly a value in the index column or row. Otherwise, no value is returned. The only other difference is that the index column or row of a range used with MATCH does not need to be in alphabetic or numeric order.
MATCH2(Block, Xvalue, Yvalue) Looks up a value in a table. MATCH2 is used in the same way as LOOKUP2, except that the specified X and Y values must match exactly an X and Y value in the X row and the Y column. Otherwise, no value is returned. The only other difference is that the index column or row of a range used with MATCH2 does not need to be in alphabetic or numeric order.
ROW( ) Returns the number of the row in which the function is entered.
ROW( ) = 3 if the function is entered in cell B3.
String table functions
Function
CHAR([Set Number], Number) Returns a text string of one character, corresponding to the ASCII or WordPerfect character set code given as the number. If no set number is given, character set 0 (ASCII characters) is used.
CHAR(65) = A
CHAR(1,23) = ▀
CODE(Text) Returns the WordPerfect character set code for the first character in a text string.
CODE("Alphabet") = 65
CONCAT(List) Combines or concatenates all text values in List into a single string. When you reference other cells, the function combines the displayed text, numbers, or formula results of those cells in the string. If you want spaces included in a concatenation, they must be inside quotation marks.
CONCAT("Total"," ","Costs") = Total Costs
CURRENCY(Number) Converts the given number to a text string in currency format. This function allows a number (after it has been converted) to be included in a text string.
CURRENCY(36) = $36.00
FIND(Text1, Text2) Returns the position of the first occurrence of the first text string (Text1) in the second text string (Text2). The position is returned as a number with position 1 being the first character. If Text1 was not found in Text2, 0 (false) is returned.
FIND is not case sensitive, meaning a lowercase letter will match an uppercase letter and vice versa.
FIND("Hat","shatter") = 2
FIND("me","shatter") = 0
In the first example, the text string "Hat" is found beginning at the second position in the second text string "shatter." The text string "me" in the second example is not found in the text string "shatter," so 0 is returned. FIND counts each character in a text string, including spaces, punctuation marks, and so on, as a position.
LEFT(Text, Count) Returns the first Count characters in a text string.
LEFT("New York",3) = New
You can also give the count as a negative number. For example, LEFT("New York",-3) returns all the characters in the text string except the last three (New Y).
LENGTH(Text) Returns the number of characters or length of a text string.
LENGTH("home") = 4
LOWER(Text) Returns the given Text string with all uppercase letters converted to lowercase.
LOWER("THE Teacher") = the teacher
MID(Text, Position, Count) Returns part of a text value to the formula cell. The function returns the portion of the text string beginning at the specified position, for the specified number (Count) of characters. The first character is position 1.
MID('winter',1,3) = win
You can also give the position and count as negative numbers. If you give a negative number for the position, it indicates a position the specified number of characters to the left of the last character in the text. For example, MID("winter",-3,2) returns te. The position of -3 starts the count 3 characters to the left of the last character (at t). The count value of 2 then begins at t and counts two characters, so the result is te.
If you give a negative number for the count, MID returns all the characters starting with the character specified by Position and ending Count characters from the last character. For example, MID("winter",3,-1) returns nte. The position value of 3 will start the count on the third character (at n). The result is everything from n to the end of the text, excluding the number of characters specified by the count value (the r), so the result is nte.
PROPER(Text) Returns each word in the given text string with the first letter in uppercase and the rest in lowercase. With PROPER, a word is considered any group of characters preceded by a space or a period.
PROPER("my DOG") = My Dog
REPEAT(Text, Count) Returns the given Text string duplicated Count times.
REPEAT("top",3) = toptoptop
REPLACE(Text1, Position, Count, Text2) Replaces part of Text1, starting at the Position indicated, takes out Count characters, then inserts Text2. (The first letter of Text1 is position 1.)
REPLACE("national",4,3,"ur") = natural
If Count is the same length as Text1, you can replace all of Text1 with Text2.
By using 0 (zero) for Count, you can insert new text into Text1.
If you specify a position just beyond Text1, you can add text onto the end of Text1.
If you do not type any text for Text2, you can delete part of Text1.
RIGHT(Text, Count)RIGHTText Count Returns the last Count characters in a text string.
RIGHT("house",3) = use
You can also give the count as a negative number. For example, RIGHT("house",-3) returns all the characters in the text string except the first three (se).
TEXT(Number, [Precision]) Converts a number to a text string. The Precision argument determines how many digits are displayed after the decimal point and should be a number from 0 to 15.
TEXT(12.472,2) = 12.47 (as a text string)
You can also use -1 as the precision argument to convert the number to a text string that displays all necessary digits (as many as 15).
TRIM(Text) Returns the text string with no leading, trailing, or consecutive spaces.
TRIM(" Here I am ") = Here I am
UPPER(Text) Returns the text string with all letters in uppercase.
UPPER("toTAL Costs") = TOTAL COSTS
VALUE(Text) Converts the text value to a number, if possible, and returns the number as the result.
