|
<< Click to Display Table of Contents >> Using Advanced Expressions |
  
|
|
<< Click to Display Table of Contents >> Using Advanced Expressions |
|
With Advanced Expressions there is no base required. The cell names are used to represent the values their paragraphs contain. Here is the example table again:
The Columns
In this example, the columns, between the two “Leave this line blank >” rows, have each been named. In the Year 1 column, the cells outlined in red have all been named “Y1”. The same cells in the Year 2 column have been named “Y2” and so on up to Y10. The expression in the Year 1 SUBTOTAL cell is simply Y1, since this name represents the sum of all cells with the same name. The Year 2 SUBTOTAL expression is Y2 and so on through Year 10.
The Rows
In the example table, the rows are not named for two reasons:
1.In this table the number of columns is fixed. The user will not be adding any more years since this is a 10 year cost table.
2.Cells cannot have two names. Cells will take the most recent name they are given. Naming the rows would have overwritten the column names, leaving the SUBTOTAL line all zeros. Instead, in the 10 Year Total column, each of the cells uses the simple expression left(2,3,4,5,6,7,8,9,10,11).
Grand Totals
So what about the Grand Total, the cell bordered in green. If you recall, we cannot include in an expression a cell that already contains an expression. So naming the 10 Year Total column will not help and using the left(2,3,4,5,6,7,8,9,10,11) expression to add up the SUBTOTAL line will also not work, since they do not contain any numbers. Instead, we need to think about what we are trying to achieve. Essentially, we want a total of all the cells in the body of the table, not either of the subtotals. Since these cells are named by column we can add all of the columns together to get the answer. The expression would be Y1+Y2+Y3+Y4+Y5+Y6+Y7+Y8+Y9+Y10.
Order of Operations
Expressions will follow normal order of operations. For instance if we wanted to take the grand total and multiply it by 5% to get the sales tax, we could use the expression (Y1+Y2+Y3+Y4+Y5+Y6+Y7+Y8+Y9+Y10)*(5/100). Just remember, there is usually more than one way to get the answer.