CCNC/CCNC Module 4/Working with Formulas and Functions/Arithmetic formulas

Arithmetic Formulas

The main purpose of a spreadsheet is to automate numeric work and to store the calculations and their results for later use. At the heart of any numeric work are the calculations themselves. Calculations are done within cells, based on a formula that you have provided. The most basic formulas are those that involve the arithmetic operations of addition, subtraction, division and multiplication. In Calc, these are written as follows: Addition = 7+8 Subtraction = 7–8 Multiplication = 7*8 Division = 7/8 Note that the signs that Calc uses for multiplication and division are different from the ones you are used to. Nonetheless, the actual operations are the same. Note too the = sign that begins each formula. We will discuss that in more detail later. <kaltura-widget kalturaid='eagfp3yfuj' size='L' align='R'/>

Using formulas to do calculations

The statements above are formulas (e.g. =7+8) and it is you the user who creates them. Calc does an operation on your formula and this yields the resulting calculation. Every calculation that is done in Calc is based on a formula, and the formula itself relies on a function to actually do the calculation. We will go into functions in more detail later. To begin, you enter the formula in the appropriate cell. Recall that cell data may be entered directly into the cell or in the input line on the formula bar. Either way, the entry is displayed in both places. In order to indicate that you wish to do a calculation in a cell, you preface the cell entry, with the = sign. The entry is therefore written in one of the formats given earlier. (e.g. =7*8). Note that the = sign that begins the forumla Is an Indication that Calc must treat the cell entry as a calculation, and that it must perform the relevant arithmetic operation on the formula..

Now do it

Select the cell in which you wish to make an entry

Enter = 7 + 8

This appears in both the cell and the input line

Click on Accept on the Formula bar.

Calc displays the result of the calculation in the selected cell

Select any cell and do the calculations for the other three formulas using the values 7 and 8.

Note that while the result is displayed in the cell, the formula is still visible in the input line. You need to distinguish between the contents of the cell (the formula you see in the input line) and what the cell actually displays (the result of the calculation).

If all we did was simple arithmetic, we would not need Calc: a calculator would do. However, suppose we have a situation in which we want to calculate the amount to be paid as simple interest. We would need three cells, as shown in the screen below. The entry in the first cell is the capital, in the second, it is the interest rate applied to that capital. In the third we enter the formula that will yield the amount of interest to be paid. That formula multiplies the contents of the first cell with the contents of the second. The result of a multiplication operation is sometimes referred to as the product.

SCREENSHOT HERE

The contents of cell C2 are: =A2*B2

Notice that the formula bar contains the actual contents of a cell. The result of the calculation is displayed in the cell.

Now suppose we change the contents of cell B2, without making any change to C2, and press Enter as shown in the following screen:

SCREENSHOT HERE

The value in C2 automatically changes to reflect the changed value in B2. This tool is called automatic recalculation.

When you save a spreadsheet, the formulas are displayed so that they can be reused at a later stage.

Arithmetic operations using the values of cells are written down in the same way as arithmetic operations between values.

Addition =A2+B2

Subtraction =A2-B2

Multiplication =A2*B2

Division =A2/B2

More complex calculations are carried out using the normal rules of arithmetic.

Suppose cell A2 contains the value 12, B2 the value 6 and C2 the value 8. The following table illustrates how these can be combined into arithmetic formulas.

Cell entry expression	Equivalent arithmetic expressiion	Cell display
=A2+B2-C2	12+6-8	10
=A2+B2*C2	12+6*8	60
=(A2+B2)*C2	(12+6)*8	144
=A2/B2-C2	12/6-8	0-2
=A2/B2+A2*C2	12/6+12*8	98

Tip: When you do arithmetic calculations that combine two or more of the different operators, remember that there is a specific order in which these operations must be done. The order is as follows:

1. Work out the contents of brackets,

2. Carry out the multiplications and divisions

3. Do the additions and subtractions

Check this in the calculations in the table above.