3.2.4 Functions; sum, average, min, max, IPMT, NPV, PMT, PV, VLOOKUP, HLOOKUP, COUNT, IF, sumif

Spreadsheet function is a predefined formula that performs calculations using specific values in a particular order. All spreadsheet programs include common functions that can be used for quickly finding the sum, average, min, max, IPMT, NPV, PMT, PV, VLOOKUP, HLOOKUP, COUNT, IF and sumif  for a range of cells. In order to use functions correctly, you’ll need to understand the different parts of a function and how to create arguments to calculate values and cell references.

Sum

1. Using a spreadsheet in Addition

In reference to the table below, let’s try to calculate how many apples, plums and total fruit sold during the week:Go to cell B7 and type =SUM(A2:A6) this will add the numbers of apples.

• To start, move to the first row.
• Each formula in a spreadsheet starts with =
• Enter = and select the first cell you want to add. Notice how the cell reference appears in the formula

• Now type + and then select the second cell you want to add
  • Press Enter or tab. The formula disappears and is replaced by the value.

   

   

   

  Try changing the number in one of the original cells (apples or plums) you should see the value in total update automatically.

  You can type each formula individually, but it also possible to cut and paste or drag formulas across a range of cells.

  Copy the formula you have just written (using ctrl + c ) and paste it into the cell below (using ctrl + v ), you will get the sum of the two numbers on the row below.

  Alternatively click on the lower right corner of the cell (the blue square), and drag the formula down to the bottom of the column. Watch the ‘total’ column update.

   

  Average

  This function determines the average of the values included in the argument. It calculates the sum of the cells and then divides that value by the number of cells in the argument.

  1. Select the cellthat will contain the function. In our example, we’ll select cell C11
     1. Enter the cell rangefor the argument inside parentheses. In our example, we’ll type (C3:C10). This formula will add the values of cells C3:C10 and then divide that value by the total number of cells in the range to determine the average.
     2. Press Enteron your keyboard. The function will be calculated, and the result will appear in the cell. In our example, the average price per unit of items ordered was 15.93.

     Your spreadsheet will not always tell you if your function contains an error, so it’s up to you to check all of your functions.

     Max and Min

     The Excel MAX function can tell you what the largest numeric value is in a range of cells. The opposite of MAX is the MIN function, which returns the smallest numeric value in a range of cells.

     .

   

   

   

   

   

   

   

   

  Type the equals sign (=) and enter the desired function name. In our example, we’ll