Lesson 4: Write Formulas using the Wizard Function

Write Formulas using the wizard function

In Excel ’97 , you can click the button with the red arrow and temporarily hide the dialog box to input data in the cell. This works much faster. You can also drag the dialog box to the bottom.

The table above shows a worksheet with the names of the representatives in column A and the number of units sold by them per item in columns B, C, D, E, F.
In column G the total is already calculated with the help of the “Sum” function.
In column H we can calculate the average using the Wizard functions:

Select cell H3
Click on the “Wizard” function button on the Toolbar.
In the dialog box, select your category ‘statistical’ (Statistical).
In the function box, choose the name of the statistical function “average” (Average).
Click OK.
A dialog appears.
A dialog box with an already selected range appears. This is not accurate, as it includes the total which is not our requirement.
You can change this with any of the following three methods:
1. Type the range itself.
2. Drag the dialog box down and select the cells with the mouse.
3. Click on the button with the red arrow next to the first input .
4. Select the cells with the mouse and press the button with the red arrow.
Click OK.
The required average is calculated immediately.
To calculate averages for other items, drag the fill handle down.

In cell B7, we calculate the number of representatives who have sold the article:

Place the cursor in cell B7
Click on the Wizard Function button on the Toolbar .
In the dialog box, choose the function category(Statistical).
In the function box, choose the statistical function ‘count’ (Count).
Click ‘OK’ (OK).
Select cells B3 to B6 if it has not been selected.
Click OK.
The worksheet calculates the number.
Then drag the fill handle to the right to increase the numbers.

In the column I, we will do the assessment with the logical function ‘if’.
If the average is greater than or equal to 6, I wish the rating to be ‘good’, and if the average is less than 6, I wish the rating to be ‘weak’.

Place the cursor in cell I3 and click on the wizard function in the toolbar.
In the dialog box, choose the function category ‘logical’ (Logical).
In the function name box, choose ‘If’ (IF) and press ‘OK’ (OK).
In the first box, the logical test, you have to fill in the evaluation criteria, in this example if the average is larger or smaller than the number 6.
So type ‘H3> = 6’.
Right click in the box that Excel gives this logical test value ‘true’.
Press the tab key, in the “Value if true ‘(Value if true), type the rating of “good”.
Press the Tab key.
In the “Value if false ‘(Value if false) type the rating ‘weak’.
Press ‘OK’.
The worksheet is completed with the assessment.
Drag the fill handle down to fill the list.

formula
= IF (H3> = 6, “good”, “weak”)

As always, the formula begins with an = sign, then followed by the function name IF, and the arguments between the paranthesis, three arguments in this case, which are separated by a coma.
The first argument H3> = 6 is a logical test.
The second and third arguments are text provided by the wizard function which are automatically placed in quotes.
As you can see , in the logical test H3> = 6 has a comparison operator. The comparison operators are used to compare values in the cells .
The result of such comparison is always TRUE or FALSE. (True or False).

The nesting of functions

We can make more categories in the assessment column I, if we try better:
If assessment is 8 or more, we wish to evaluate it as ‘excellent’
from 6.5 to 8, we evaluate as “good”
from 5 to 6.5, we evaluate as ‘weak’
less than 5 is ‘insufficient’

Place the cursor in cell J3 and click on the wizard function
Choose the logical function ‘IF’ (IF)
Click ‘next’ (OK)
In the “logical test” (Logical test) Type H3> = 8 and click the tab key
In the “true value” (Value if true) Type: ‘excellent’ and click on the tab key
In the box “False Value”” (Value if false), we don’t fill in anything, but press the “if” button that appears next to the formula bar.
In the “logical test” (Logical test) Type H3> = 6.5 and click on the tab key
In the “true value” (Value if true) Type ‘good’ and click on the tab key
In the “value if false ‘(Value if false), we don’t fill in anything, but press the “If ” button next to the formula bar again
In the “logical test” (Logical test) Type H3> = 5 and click the tab key
In the “true value” (Value if true) Type: weak and click the tab key
In “value if false”(Value if false) Type: insufficient
Click OK

Naming Ranges

A group of cells in a spreadsheet is called a range. A range is identified by a cell reference for the first cell, followed by a colon and the referral of the last cell and are used constantly in formulas.

For ex. in = SUM (B2: B11)
B2: B11 is a Range

You can also name a range, assigning a name to a range is very simple:

Select the range B1: B11
Click in the Name box at the top left in the formula bar and type in: cost
Press Enter

A second way:

Select the range A1: D11
Choose ‘Insert’ from the menu bar.
Choose the command ‘name’.
Select the submenu ‘create’ (Create).
Dialog “create a name” appears.
You notice that the names in the top row are listed and the name of the selected row is already selected, ie Excel the names of column titles are listed as names.
Click Ok.
You can check by clicking the name box in the formula bar.

If you are in the Name field of the formula bar, click a name, and immediately the range is selected.
You can move to a range in your worksheet very fast with the F5 key.
Instead of cell references, you can now use names in formulas:
The calculation in cell D2 is now very easy: = income-cost
You can once again copy the calculations by dragging the fill handle.

In cells D13 and D14 we want the minimum and maximum balance calculation:

Select cell D13 and click wizard-function
Select function category in the box, select the ‘statistical’ and choose the function names in function MAX
Press next
Click in the box ‘number1’ and enter therein the name: balance
Click Finish
The value appears in cell D13

We use a different method to calculate the minimum value:

We place the cursor in cell D14
Type: = MIN (balance)
Press Enter

Relative and absolute cell reference

relative cell reference
= B1 * B2
when copying a relative cell reference the formula is adjusted automatically

absolute cell
= B1 * $B$2
when copying an absolute cell reference the formula is not adjusted
a $ sign is placed in front of the cell

Awesome!
You've completed Lesson 4 START NEXT LESSON

Lesson 4

Office 97 - Excel