3) Building formulae: sum, difference, product, quotient, average, percentage

Building formulae:

sum, difference, product, quotient, average, percentage

Other than using user defined functions, you can also define the calculations manually by typing directly into the cell or into the input line.

Sum and difference

While defining manually, the formula should begin with an ‘=’ symbol followed the calculation to be done.

For example: consider the following population data

 

If you want to add the population of the Albania (B2), Algeria (B3) and Angola (B6), and you want the answer to be displayed in the cell C6. This can be easily done by selecting the cell C6, using the SUM function and selecting the values in B2, B3, B6 which would have given the following result.

 

This can also be done by another way by entering the formula manually. Select the cell C6 and start typing the formulae as shown below. Here B2+B3+B6 will add the values in that particular cells.

Once the formulae is manually typed, press enter. This shows the sum in the cell C6 as shown below which is same as in the previous method.

But the difference can be seen in way the answer is calculated by comparing the formulae tab.

 

This way of defining formula manually has scope for not only finding the total of the values in the cell, but also to add any constant values or to do multiple level of addition. Similarly subtraction can be done by using minus (-) symbol.

For example, if you want to find the difference in the population between the country Andorra (B5) and Aruba (B10), then the formula can be defined as

 

This will return the difference in the cell C10. Directly the formula can be entered in the formula tab itself.

Product

The PRODUCT function multiplies all the numbers given as arguments and returns the product. For example, if cells A1 and A2 contain numbers, type the formula =PRODUCT(A1, A2) in any cell to multiply those two numbers together. You can also perform the same operation by using the multiply (*) mathematical operator; for example, =A1 * A2. The PRODUCT function is useful when you need to multiply many cells together. For example, the formula =PRODUCT(A1:A3, C1:C3) is equivalent to =A1 * A2 * A3 * C1 * C2 * C3.

For example, consider the following data,

 

If you want to find the total cost for each item, then need to find quantity x cost. So the formula can be entered as below:

Once the formula is typed and enter key is pressed, the output will be shown as below:

 

This product can be found manually. Incase in the above example, if you want to find the total cost for all items in the list, you need to enter the formula for every cell. Whenever the formula is same and it needs to be applied to various cell, you can simply drag and apply the same formula.

To do same task follow these steps:

Dragging formula vertically/ horizontally:

• Enter the formula in one cell and press enter.
• Select the cell in which formula is entered.
• When you put mouse over the right down corner of the selected cell, the mouse pointer changes to a plus symbol.

 

• Click at that point and drag to the cells, where the formula needs to be applied. This applies the formula to all the cells.

Similarly the formulae can be extended horizontally as well by dragging horizontally.

For example, Consider the following data listing the amount spend on each item for three days.

To find the total amount spend on each day, find the total amount for the first day.

Then by dragging horizontally and extending the formula, find the total for other two days.

 

Quotient

To find quotient, the formula should use the symbol backslash (/) instead of normal divider symbol. For example, consider the following data

If you want to find the cost per kg of the each fruit, then the formulae should be given as below:

When the formula is extended vertically, you can find the cost of per kg of each fruit.

 

Average

To find the average manually, you need to recall the steps of finding average mathematically. If you have to find the average of the given set of number say, 5,6,8,9,12 then average will be (5+6+8+9+12) /5 i.e. Sum/ no. of items. Here brackets are used to specify the order in which calculations should be carried on. BODMAS is followed here as well. So, to find average manually, the formula can be entered as:

 

Percentage

To find the percentage manually, you need to recall the steps of finding average mathematically.

For example, Consider the following data

If you have to find what percentage of the total amount has been spend on food, then you can enter the formula as (10000/Total amount)*100 (This means, 10000 is spend out of the total salary on food, if the total salary is ‘X’, then what amount is spent on food). As total amount is required here, first you need to find the sum and then find the percentage or directly can apply the formula too.

Here vertically dragging to extend the formula can’t be done since it will vary the total amount which is wrong. In case if you want to drag and find it for every item, then the formula should be entered as below: