Module 1
Introduction to Excel
Review of Some Basic Excel Functions
Aim: By the end of the module, the students should be able to:
1. Identify common terms when using excel
2. use simple functions in excel
3. use the if statement for logic criteria
4. use a reference table for computing
5. create a simple graph
Theory: NA
Microsoft Office Excel is a spreadsheet application written and distributed by
Microsoft for Microsoft Windows and Mac OS X. It features calculation, graphing
tools, pivot tables and a macro programming language called VBA (Visual Basic
for Applications).
Spreadsheets are made up of
1. columns
2. rows
3. Their intersections are called cells
In each cell there may be the following types of data
1. text (labels)
2. number data (constants)
3. formulas (mathematical equations that do all the work)
SOME EXCEL FUNCTIONS
1. The SUM FUNCTION takes all of the values in each of the specified cells
and totals their values. The syntax is:
=SUM(first value, second value, etc)
2. The AVERAGE FUNCTION finds the average of the specified data.
(Simplifies adding all of the indicated cells together and dividing by the
total number of cells.) The syntax is as follows.
=Average (first value, second value, etc.)
3. Max (Maximum). This will return the largest (max) value in the selected
range of cells. Blank entries are not included in the calculations of the Max
Function. Text entries are not included in the calculations of the Max
Function.
4. Min (minimum). This will return the smallest (Min) value in the selected
range of cells. Blank entries are not included in the calculations of the Min
Function. Text entries are not included in the calculations of the Min
Function.
5. Count. This will return the number of entries (actually counts each cell that
contains number data) in the selected range of cells. Blank entries are not
counted. Text entries are NOT counted. And CountA. This will return the
number of entries (actually counts each cell that contains number data OR
text data) in the selected range of cells. Blank entries are not Counted. Text
entries ARE Counted.
6. The IF function will check the logical condition of a statement and return
one value if true and a different value if false. The syntax is
=IF (condition, value-if-true, value-if-false)
=IF(F4<0, E4, C4) or =IF(F4<0.0001, “stop”,”continue”)
7. The VLookup function searches for value in the left-most column of table
array and returns the value in the same row based on the index_number. The
syntax is:
=VLookup( value, table_array, index_number, not_exact_match )
The Chart Wizard
Excel has a chart program built into its main program. The Chart Wizard will step
you through questions that will (basically) draw the chart from the data that you
have selected.
Machine Problem:
1. Encode the following in MS Excel
QUIZES Grade Table
TOTAL FINAL
NAME Q1 Q2 Q3 Q4 QUIZES EXAM SCORE GRADE REMARKS 0 10 0.0
1 MAT 50 50 50 50 11 11 74 0.5
2 MARK 65 90 88 54 75 75 77 1.0
3 LUKE 77 56 78 97 78 78 80 1.5
4 JOHN 34 100 100 76 81 81 84 2.0
5 ACTS 23 8 47 100 85 85 88 2.5
6 ROMAN 100 98 89 100 89 89 92 3.0
7 CORITH 90 68 80 88 93 93 96 3.5
8 THES 36 90 89 80 97 97 100 4.0
9 COLO 78 100 89 76 97
GRADE COMPUTATION USE TABLE
SCORE= (75% TOTAL QUIZES/400 + 25% FINAL EXAM)*0.7+30
ANALYSIS
MAXIMUM
MINIMUM
AVERAGE
TOTAL STUDENTS =
2. For the grade table, 75 is really 74.5.
3. The total is computed using summation (Σ) of all the quizzes. Each quiz has
a maximum total points of 100.
4. The score is computed as the 75* total quizzes/400+final exam*.25 (75% of
quizzes+25% of final exam.
5. For score that are less than 74 must be in red. Use “conditional formatting”,
then” highlight cells”, then” less than” and lastly “custom format” change
automatic to red.
6. The grade is computed using a the vlookup function.(=vlookup,target cell,
reference table is anchored, column to set)
7. Use IF function for the remarks (=IF(grade=0.5,”fail”,”pass”)
8. Use max, min and average for the analysis and count for the total students
9. Make a graph of the score using Chart wizard and xy scatter.