vert line

 

This lesson will teach you about entering formulas and creating labels on the worksheet. Formulas are algebraic statements that can be evaluated by Mesa to return a value in a cell. The formula in one cell can refer to the value in another cell. When the value in a cell changes, the spreadsheet automatically recalculates and updates each cell that relies on a value that was changed.

The first thing that we will do is create a label or a named range called Checking. A label can be referred to in a formula by its title rather than the cells addresses. Referring to a label makes formulas more readable. Select cells A2 through G3. Open the Label Inspector by selecting the Inspector Icon on the Workbook Toolbar and choosing the Label Inspector. Type checking and press enter. Mesa will create a label called checking and assign it to cells A2 through G3.

The next step is to enter the formulas that calculate the balance in the checking account. Move to cell F2 and enter the formula =B2-C2. This formula subtracts the amount withdrawn from the amount deposited and places the result in cell F2. Next, move to cell F3, and enter the formula =F2 + B3 - C3. This formula added the current transaction to the prior balance, thus accumulating the balance in the checking account.

The following step will take you through complex formula, just to demonstrate some of the Mesa capabilities.

The next step is to sum the total amount deposited and withdrawn. Select cells B4 and C4. Type =SUM(checking[][this]) and press Shift enter This will SmartFill this formula into the selected range. The formula sums, adds up, all the values in the range checking, but constrains the range to this column. By SmartFilling the range, this formula is automatically propagated into cell C4.

The next step is to enter the formula into the column that alerts you if there is a difference between the amount in the checkbook and the amount that should be in the checkbook. Select cells G2 and G3. Enter the formula

=setcolor(setbkgcolor(if(#NOT#isempty(e2),e2-f2),if(if(#NOT#isempty(e2),e2-f2)<>0,redcolor, whitecolor)) ,if(if(#NOT#isempty(e2),e2-f2)<>0,whitecolor,blackcolor)) and press Shift-enter.

This formula sets the foreground color to white and the background to red if the value in the checkbook is different from the running total.

You have entered all the formulas into the worksheet. Please save your work. Your worksheet should be similar to Lesson3.Mesa worksheet.

 

 

Also go to:

Index help

Contents help