[Calculating Totals Between Tables In Word For Mac
Ainoha Sistek
As you may already know, the Table feature is one of the most powerful features in WordPerfect. You can use tables in a variety of situations, but most of us turn to them when we need to position text and numbers in columns. What you may not yet realize is that you can create formulas in WordPerfect tables and do some pretty fancy calculations on those numbers.
And if you can do calculations in WordPerfect tables, why turn to another program for simple tasks such as adding a series of numbers? Many users find that they construct small spreadsheets in WordPerfect and turn to Quattro Pro for larger, more complex spreadsheets.
In this tutorial, I'll show you how to create formulas in tables. I'll start with a simple formula that totals a column (or row) of numbers. Next, we'll talk about how to create formulas that calculate a value (rather than typing it in). After that, you'll learn how to create formulas using one of the 500 built-in spreadsheet functions. And finally, I'll explain how to import information in a Quattro Pro (or Microsoft Excel) spreadsheet into a WordPerfect table.
To use QuickSum, click in the cell where you want the total to appear. Press Ctrl + =, or choose Table ? QuickSum. The little blue triangle that appears in the lower right corner indicates that a cell has a formula in it.
A QuickSum formula in a column adds all the numbers in cells directly above it; a QuickSum at the end of a row adds all the numbers to the left of it. If a cell is empty, QuickSum doesn't calculate any cell above the empty one. The workaround is to make sure you have numbers in all the cells, even if some of the numbers are zero.
To create a formula in a cell, you first have to tell WordPerfect that you are creating a formula, not just typing in a bunch of cell addresses. This is done by preceding a formula with either a plus sign (+) or an equal sign (=).
Up until now, you've typed formulas "by hand". For simple formulas, it's the quickest way. The alternative is to create formulas using the Formula toolbar, which is especially helpful when you need to create formulas with the built-in functions.
When you are using the Formula toolbar, you can type the cell addresses and math operators, or you can use the mouse to click in the cells that you want to include. For example, you could type +A3*C2. Or you could type a plus sign, click in cell A3, type the asterisk, and then click in cell C2. The result is the same. I prefer the mouse method since I don't have to remember the exact cell addresses.
Creating a Link to a Quattro Pro Spreadsheet
If you are already familiar with spreadsheet programs, you may prefer to do your calculations in Quattro Pro, then copy them into WordPerfect. That's fine - more power to you!
When you're ready to copy the information over, you need to decide between a link or a simple copy. If you set up a link, the information in your document will be automatically updated whenever the spreadsheet is changed. The alternative is to create a copy of the information that is independent of the spreadsheet, so you can edit it freely without affecting the original file.
When you are ready to bring the information into WordPerfect, consider creating a named range for the cells that you want to link to. At the very least, jot down the cell addresses for the range that you want to use so you'll be ready when you set up the link.
If this seems like way too much information, just keep in mind that as long as you understand that you can do these things in a WordPerfect table, rather than in another program, you're ahead of the game. When the time comes, you can be confident that all the tools you need are right here in WordPerfect.
One of the most useful features of Excel tables are structured references. When you have just stumbled upon a special syntax for referencing tables, it may look boring and confusing, but after experimenting a bit you will surely see how useful and cool this feature is.
To add structured references to your formula, you simply select the table cells you want to refer to. The knowledge of a special syntax is not required.
Resilient and automatically updatedWhen you rename a column, references are automatically updated with the new name, and a formula does not break. Moreover, as you add new rows to the table, they are immediately included in the existing references, and the formulas calculate the full set of data.
So, whatever manipulations you do with your Excel tables, you don't have to worry about updating the structured references.
Can be used inside and outside a tableStructured references can be used in formulas both inside and outside an Excel table, which makes locating tables in large workbooks easier.
Formula auto-fill (calculated columns)To perform the same calculation in each table row, it is enough to enter a formula in just one cell. All other cells in that column are filled automatically.
How to create a structured reference in ExcelMaking a structured reference in Excel is very easy and intuitive.
For example, to find the largest number in our sample table, we start typing the MAX formula, after the opening parenthesis type "s", select the Sales table in the list, and press Tab or double-click the name.
=MAX(Sales)
Structured reference syntaxAs already mentioned, you do not need to know the syntax of structured references to include them in your formulas, however it will help you understand what each formula is actually doing.
The table name references only the table data, without header row or total rows. It can be a default table name like Table1 or a custom name like Regions. To give a custom name to your table, carry out these steps.
If your formula is located within the table it refers to, the table name is usually omitted because it is implied.
Column specifierColumn specifier references the data in the corresponding column, without the header row and total row. A column specifier is represented by the column name enclosed in brackets, e.g. [South].
Please notice that the pound sign (#) is used with all the item specifiers, except the current row. To refer to the cells in the same row where you enter the formula, Excel uses the @ character followed by the column name.
=SUM(Regions[@[South sales]], Regions[@[West sales]])
Structured reference operatorsThe following operators allow you to combine different specifiers and add even more flexibility to your structured references.
Please notice that the [#All] specifier is required in this case because the column specifier does not include the total row. Without it, the formula would return #NULL!.
Table reference syntax rulesTo edit or make structured references manually, please follow these guidelines:
A specifier that contains other specifiers should be wrapped in outer brackets. For example, Regions[[South]:[East]].
2. Separate inner specifiers with commasIf a specifier contains two or more inner specifiers, those inner specifiers need to be separated with commas.
=Regions[[#Headers],[South]]
3. Do not use quotation marks around column headersIn table references, column headers don't require quotes whether they are text, numbers or dates.
4. Use a single quotation mark for some special characters in column headersIn structured references, some characters such as left and right brackets, pound sign (#) and single quotation mark (') have special meaning. If any of the above characters is included in a column header, a single quotation mark needs to be used before that character in a column specifier.
For example, for the column header "Item #", the specifier is [Item '#].
5. Use spaces to make structured references more readableTo improve the readability of your table references, you can insert spaces in-between specifiers. Normally, it is considered a good practice to use spaces after commas. For example:
=AVERAGE(Regions[South], Regions[West], Regions[North])
Excel table references - formula examplesTo gain more understanding about structured references in Excel, let's go over a few more formula examples. We'll try to keep them simple, meaningful and useful.
The below screenshot shows all the formulas in action:
Count blanks and non-blanks in a columnWhen counting something in a specific column, be sure to output the result outside the table, otherwise you might end up with circular references and wrong results.
=SUBTOTAL(103,Sales[Jan])
Sum in an Excel tableThe fastest way to add up numbers in an Excel table is to enable the Total Row option. To do this, right click any cell within the table, point to Table, and click Totals Row. The total row will appear at the end of your table straight away.
Sometimes Excel may assume you want to total only the last column and leaves other cells in the Total row blank. To fix this, select an empty cell in the Total row, click the arrow that appears next to the cell, and then select the SUM function in the list:
Please note that this formula works only in the Total row. If you try to manually insert it in a data row, this would create a circular reference and return 0 as the result. A SUM formula with a structured reference won't work either for the same reason:
Please note that unlike SUBTOTAL, the SUM function adds up values in all the rows, visible and hidden.
Relative and absolute structured references in ExcelBy default, Excel structured references behave in the following way:
In situations when you need a combination of relative and absolute table references, there is no way to copy the formula and keep table references correct. Dragging the formula will change the references to single columns, and copy/pasting shortcuts will make all the references static. But there are a couple of simple tricks to get around!
Supposing you want to add up the sales numbers for a specific product for 3 months. For this, we enter the target product name in some cell (F2 in our case) and use the SUMIF function to get the total of Jan sales:
795a8134c1