Norton University Computer II
Computer II
Counting Numeric Cells
To count only the numeric cells in a range named Data.
Syntax
=COUNT (Data) or COUNT (value 1, value 2, ......)
Cells that contain a date or a time are considered to be numeric cells. Cells that contain a
logical value (true or false) are no considered to be numeric cells.
Counting Nonblank Cells
COUNTA function to return the number of nonblank cells in a range named Data:
Syntax
=COUNTA (Data) or COUNTA (value1,value2, ……)
The COUNTA function counts cells that contain values, text, or logical values (true or false).
Getting a Quick Count or Sum
1. Selected range.
2. The Status Bar (displays the sum of the values in the selected rang).
3. You can right click the text the Auto Calculate display to bring up a menu with some
of other option.
Microsoft Excel 2003 1
Norton University Computer II
Counting Cell by Using the COUNTIF Function
COUNTIF function is useful for single-criterion counting formulas.
Syntax
=COUNTIF (range, criteria)
The COUNTIF function takes two arguments:
1. range: The cell or range that contains the values.
2. criteria: The logical criteria that to include a particular cell in the count.
Conditional Sums Using a Single Criterion
The SUMIF function is very useful for single-criterion sum formulas.
Syntax
=SUMIF (range, criteria, sum_range)
The SUMIF function takes three arguments:
1. range: The range containing the value to include a particular cell in the sum.
2. criteria: An expression to include a particular cell in the sum.
Microsoft Excel 2003 2
Norton University Computer II
3. sum_range: The range that contains the cells you want to sum.
Conditional IF
The If conditional specify evaluates to True and another value if it evaluated to False.
Syntax
=IF (logical_test,value_if_true,value_if_false)
The IF function takes three arguments:
1. logical_test: argument sets up some kind of equality (A=B), inequality (A<>B), or
comparison (A>B, A<=B, and so on) that either is the case (true) or is not (false).
2. lalue_if_true: argument tells Excel what to calculate or input when the logical test is
found to be true.
3. lalue_if_false: argument tells the program how to proceed or what to input when the
logical test turns out to be false.
Microsoft Excel 2003 3
Norton University Computer II
Creating a Chart
Another useful feature of spreadsheet programs is that they allow you to display your
data in the form of graphs and charts very easily. In Excel, charts can be placed anywhere in a
workbook, and can be easily moved and modified.
1. First select the cells containing the data you want to display.
2. Then click on the Chart Wizard icon from the button on the Standard toolbar.
3. After you have made your selections by clicking on them, select Next to proceed
to the next stage.
4. Then click Series Tab
Microsoft Excel 2003 4
Norton University Computer II
5. Click in the boxes for Name and Values, and then select a worksheet range; or
type the name and values in the boxes.
6. Selected Category (X) axis to Displays data on the selected axis as the default
category (x) axis.
7. Select Next to proceed to the next stage.
8. Select Next to proceed to the next stage.
9. Click As new sheet, and then type a name for the new chart sheet in the As new
sheet box to place the chart on a new chart sheet.
Microsoft Excel 2003 5
Norton University Computer II
10. Click As object in, click a sheet name in the As object in box, to place the chart
on a worksheet.
11. Click Finish.
Microsoft Excel 2003 6
Norton University Computer II
Sorting Worksheet Data
Excel’s Sort feature makes it easy to rearrange the records or even the fields in your table of
data or data list.
When defining the key fields or rows for a sort, you can specify either an ascending or
descending sort order for its data:
• When you specify ascending order (which is the default), Excel arranges text in A-to-
Z order and values from smallest to largest.
• When you specify descending order, Excel reverses this order and arranges text in Z-
to-A order and values range from largest to smallest.
Sorting records in a data list
1. Selects all the Records in the List
2. Choose Data
3. Sort to open the Sort dialog box shown
4. In the Sort By drop-down list, select the name of the field you want used as the
Primary Key in sorting the records.
5. If you want to sort the records in Descending order using the Primary Key, click the
Descending option button.
Microsoft Excel 2003 7
Norton University Computer II
6. If the Primary Key field contains Duplicates and you want to specify how these
records are to be sorted, select the name of the field to Sort By in the first Then By
drop-down list.
7. When you finish defining all the keys you need to use in sorting the records in your
data list, click OK.
Product Function
Multiplies its Arguments.
Syntax
PRODUCT (number1, number2, ...)
Number1, number2, ... are 1 to 80 numbers that you want to multiply.
Microsoft Excel 2003 8
Norton University Computer II
SQRT Function
Returns a positive square root.
Syntax
SQRT(number)
Number is the number for which you want the square root.
POWER Function
Returns the result of a number raised to a power.
Syntax
POWER(number,power)
Number is the base number. It can be any real number.
Power is the exponent to which the base number is raised.
Microsoft Excel 2003 9
Norton University Computer II
AVERAGE Function
Returns the average of the arguments.
Syntax
AVERAGE(number1,number2,...)
Number1, number2, ... are 1 to 80 numeric arguments for which you want the average.
MAX Function
Returns the largest value in a set of values.
Syntax
MAX(number1,number2,...)
Number1, number2, ... are 1 to 30 numbers for which you want to find the maximum value.
Microsoft Excel 2003 10
Norton University Computer II
DATEDIF Function
DATEDIF is a Function that calculates the number of days, months, or years between two
dates.
Syntax
=DATEDIF ( Start_date,End_Date,Unit code)
Data List Filtering
Using AutoFilter enables you to display only the records that you want to see. When
AutoFilter mode is on, you can filter the data by selecting values.
Using auto filtering
1. AutoFilter on, position the cell pointer somewhere in one of the cells of the list
Microsoft Excel 2003 11
Norton University Computer II
2. Choose Data Menu
3. Filter ➪ AutoFilter
Using advanced filtering
1. Before you can use the advanced filtering feature, you must set up a criteria range.
2. A criteria range is a designated range consists of at least two rows.
a. All field names from the list.
b. The other rows consist of your filtering criteria.
3. Choose Data Menu
4. Filter ➪ Advanced Filter
5. Select
Microsoft Excel 2003 12
