Introduction to Functions

As a spreadsheet application, StarCalc is equipped with various functions that can solve almost any kind of calculation.

In order to use the functions more effectively, you should know which ones are available and how they work. A function in your application involves three issues.

The purpose of the function: what is it used for? Different functions are meant for different purposes.

The arguments used by the function: what does the function need, in order to carry its assignment; an argument (or arguments) is (are) the value (values) that the function needs to work efficiently. A function can use more than one argument. A function's arguments are mostly made in two categories, some arguments are required; in other words, if you don't provide them, the function will throw an error. Other arguments are set with default values; in other words, the application will provide their arguments if you don't.

The cell responsible for displaying the result: in what cell will the result appear (it has to appear somewhere)?

The use and implementation of a function always starts with an equal sign "=" followed by the name of the function. If you remember the name of a function and know its arguments and decide to use it, you can type = in a cell and the name of the function followed by an opening parenthesis. StarCalc will know that you are trying to use a function and will help.

Although the functions are not case-sensitive, it is a good idea to always write them in uppercase.

Mathematical Operations

Before getting into functions, let's experiment with StarCalc's use and recognition of mathematical operations.

StarCalc is aware of such algebra operations as the addition (+), the subtraction (-), the multiplication (*), and the division (/). These operations can be applied to numbers typed in a cell when performing the operation, they can be gotten from another cell.

On the Status Bar, the AutoCalculate pane is a special area that allows you to get a quick result of the most used functions in StarCalc.

The Sum

The SUM function is the most basic and probably the most popular function used in StarCalc. It is used to sum the contents of two or more cells and display the result in another cell.

The formula bar is equipped with a smart button called the Sum button. You can click an empty contiguous cell, and then click the AutoSum button. Before performing the SUM function, the computer will ask whether it found the right cells that you want to get the sum of. If the computer found the right cells, press Enter, otherwise use your mouse or your keyboard to select the cells you want to consider.

Practical Learning: Use the AutoSum

1. Open the Red Oak High School1 workbook and click Sheet1.
2. In cell A13, type Total
3. Click cell B13 to give it focus.
4. On the formula bar, click the Sum button . Notice that StarCalc selects the cells on the upper region of the selected cells; the blinking selection is the application's way of asking whether these are the cells you want to get the sum of:
    
   
5. Press Enter.
6. Click cell B13 to select it. Right-click cell B13 and click Copy
7. Click cell C13 to select it. Right-click cell C13 and click Paste. Notice that because the copy was made on a cell that had a formula, the formula, and not the content of the cell, was copied and appropriately pasted.

AutoCalculate

1. Select cells D5:D12 and observe the AutoCalculate section of the Status Bar 
2. Right-click the AutoCalculate section and click None. Notice that the AutoCalculate section displays nothing.
3. Right-click the AutoCalculate section again and click Sum. Now the AutoCalculate section displays the sum of the selected cells.

Introduction to Math and Trigonometric Functions: SUM

StarCalc is made of many functions. Whenever the sum is not involved, you will use one of the other various functions to get a particular result.

The SUM function is used to add the contents of various cells.

Practical Learning: Using the SUM Function

1. In cell D13, type =D5+D6+D7+D8+D9+D10+D11+D12 press Enter.
2. Click cell E13 and type =
3. Click cell E5 and type +
4. Click cell E6 and type +
5. Click cell E7 and type +
6. Click cell E8 and type +
7. Click cell E9 and type +
8. Click cell E10 and type +
9. Click cell E11 and type +
10. Click cell E12 and press Enter.
11. In cell F13, type =SUM(
12. Select cells F5:F12 and press Enter.
13. Click cell G13 to give it focus.
14. On the main menu, click the Insert -> Functions...
15. From the AutoPilot Functions dialog box, in the Category combo box, select Mathematical.
16. In the Function list box, scroll down and click SUM.
    
    
17. Click Next.
18. Notice that the dialog box displays a series of text boxes. Also notice that the cursor is positioned in the first text box (if it is not, click the first empty text box on the right side of the first fx button).
    Drag the title bar of the dialog box to move it so you can see the background but don't close the dialog box yet.
19. In the background, select cells G5:G12
     
    
20. On the dialog box, click OK.
21. While it still has focus, position the mouse on the lower-right corner of the selected cell until the mouse pointer becomes +
22. Click and drag in the right direction until you get to cell J13. Then release the mouse.

Statistical Functions: Average

The AVERAGE function displays the average or mean value of selected cells. It takes the sum of the cells involved (you don't need to calculate the sum first), divides the result by the number of cells involved, and displays the result.

We will use the AVERAGE function to get the average grade for each student's grade.

Practical Learning: Using the Average Function

1. In cell A14, type Average
2. In cell B14, type =AVERAGE(
3. To get the average of the first student's grade, select cells B5:B12 and press Enter.
4. Click cell C14 to give it focus.
5. On main menu, click Insert -> Function List
6. In the floating window, make sure Last Used is selected in the combo box, otherwise select it.
   On the spreadsheet, make sure cell C14 has focus. In the list box of functions, double-click AVERAGE (if you don't see AVERAGE, then in the fx combo box, select Statistical, and in the list box, click AVERAGE)
7. Select cells C5:C12 and press Enter
8. Click cell C14 to make it the default. Right-click cell C14 and click Copy.
9. Select cells D14:J14, right-click in the selection and click Paste. Notice that since the copying was done on a cell that has a function's result, only the formula was copied. Also notice the errors on cells I14 and J14; don't worry about those errors, they will find a solution soon.

Statistical Functions: MIN - MAX

The MIN (MAX) function will examine the contents of selected cells and display the content of the cell that has the lowest (highest) value.

Now we will learn about the MIN and the MAX functions.

Practical Learning: Using the MIN And MAX Functions

1. In cell I4, type Minimum and press Tab.
2. In cell J4, type Maximum.
3. Click cell I5 to give it focus.
4. In the list of functions on the floating window, if you see MIN, click it. If you don't see MIN in the list, click the arrow of the combo box and select Statistical. In the list box, scroll down and click MIN
    
   
5. With the MIN function selected, on the floating window, click the Function button
6. On the spreadsheet, select cells B5:H5 and press Enter
7. Click cell J5 to give it focus.
8. Click inside the Formula Bar type =MAX(B5:H5) and press Enter
9. Select cells I5:J5.
10. Drag their Fill Handle (the big point in the lower right corner of J5) to cell I14:J14.

Statistical Functions: COUNT - COUNTA

The COUNT function counts the number of cells that contains numbers in the range or group of cells involved in your selection.

While the COUNT function counts the cells that contain numbers, the COUNTA function counts the number of non-empty cells (regardless of what is in those cells, as long as they contain something) in the cells involved.

Practical Learning: Using the COUNT and COUNTA Functions

1. In cell C18, type Number Of Grades:
2. In cell C19, type Class Effective:
3. Click cell E18 to give focus. In cell E18, type = COUNT(
4. Select cells A5:A12 and press Enter. Notice that the result displays 0. This is because the COUNT function counts only numbers.
5. Double-click cell E18 to put it to edit mode. In the cell, select the group A5:A12 and press Delete to delete the range. From column B, select cells B5:B12 and press Enter.
6. Click cell E19 to give it focus. In cell E19, type =COUNT(
7. Select cells B4:H4 and press Enter. Notice again that the result is 0. The COUNT function is not designed to count non-numbers.
8. Click cell E19 to give it focus.
9. Click in the Formula Bar and type A between T and ( so that when you finish, the cell will have =COUNTA(B4:H4)

Logical Functions – IF

If you want to display something in a cell but conditional to another cell's content, you can use the IF function. Of course the IF function can do more than that.

The IF function will check the truthfulness or negativity of a statement. If the result is true, then it will execute the first expression; if the result is false, it will consider the second option.

We are going to use the IF function to find out whether a student passes the mark or fails the class, based on the overall average of the student's grades. We will set the mark at 14.50. If a student's average is above 14.50, then he goes to the next grade, otherwise, …

Practical Learning: Using the IF Functions

1. In cell A15, type Pass/Fail?
2. Click cell B15 to select it.
3. On the main menu, click Insert -> Function...
4. In the Category combo box, select Logical.
5. In the list of functions, double-click IF
6. Type B14 >=14.50 (in the first text box)
7. Click in the second text box and type "Pass"
8. Click in the third text box and type "Fail"
    
   
9. Click OK
10. While cell B15 still focus, drag its Handle (its lower right corner) to cell H15.
11. Click cell A16 and type Grade:
12. You can also put an IF function inside of another, this is referred to as nesting.
    In cell B16, type =IF(B14>16.5;"A";IF(B14>14.5;"B";IF(B14>12.5;"C";IF(B14>12;"D";"F"))))
13. Press Enter. Select cell B16 and drag it Handle to cell H15