Microso
ft
O
ffi
ce Excel 2003
Lecture 11.3
Calculations
Dr. Abu‐Arqoub ٢
If we want to calculate the total marks for first and
second exam for each student
Calculations
Dr. Abu‐Arqoub ٣
Then, we write the name of the cell that contains the first mark for “Ruba”, which
is B3
Calculations
Dr. Abu‐Arqoub ٤
Then, we write the name of the second cell.
Note: if we have more than two cells in our calculation,
we do the same [add operation, then write the
cell name and so on]
After we finish from entering the calculation, we
Calculations
•
Now, we repeat the steps for the second student ”Ali”, but if
we have a large number of students, this way is considered a
time and effort consuming. There is a way to do that.
•
Once we finish from the first student [as we did in the
previous slides], we can do the following:
Dr. Abu‐Arqoub ٥
Calculations
Dr. Abu‐Arqoub ٦
Put the mouse cursor on the small square appeared on
the selected cell.
Then, click the left button of the mouse and keep holding
it while you are moving down as you can see. Once, you finish selecting the desired cells, release the
Calculations
Dr. Abu‐Arqoub ٧
As you can see, the calculations are done for all
students.
Note:
Paste Special
‐
Values
•
Some values in excel worksheet may be calculated as formulas. So,
copying these values to another place might not work correctly. Paste
Special
Values helps on that. It also useful when we want to copy
values without the formatting.
•
Steps:
– Copy the values [one cell or multiple cells].
– Put the cursor in the place where you want to paste these values in and click right click.
– Choose Paste Special
– Then choose Values and click OK.
•
Excel has a built
‐
in functions that may help the user to
perform some calculations.
•
Example 1:
Dr. Abu‐Arqoub ٩
Calculations using Functions
Calculates the Total for the students using the function
“Sum”.
•
Steps:
–
Select the cell where you want the result to appear in.
–
Press the button called “
fx
”
Dr. Abu‐Arqoub ١٠
Calculations using Functions
Total for Ruba
–
The following dialog will appear
Dr. Abu‐Arqoub ١١
Calculations using Functions
Select the function Functions Category: [Note: To view all the functions, you can select “All”]
–
Then a another dialog will appear.
Dr. Abu‐Arqoub ١٢
Calculations using Functions
–
Then a another dialog will appear.
Dr. Abu‐Arqoub ١٣
Calculations using Functions
–
Again, a dialog will appear and it will be filled with appropriate cell
ranges as we selected.
Dr. Abu‐Arqoub ١٤
Calculations using Functions
Then, press OK and you will find the result in the cell where you selected it at first
Note: To calculate Totals for Ali and Rami, you
can extend the same formula we did to Ruba
for both of them. [Have a look on the previous
Calculations using Functions
•
Example 2:
•
Steps:
–
Select the cell that you want to show the result in.
–
Then, press “
fx
” button.
Dr. Abu‐Arqoub ١٥
We want to calculate the average for the totals of the
students
Function Category
Calculations using Functions
Dr. Abu‐Arqoub ١٦
Press the button to select the cell ranges
Selected Cells
Calculations using Functions
Dr. Abu‐Arqoub ١٧
Now, press OK
Calculations using Functions
•
Example 3:
•
Steps:
–
Select the cell where you want the result
to appear in.
–
Click “
fx
” button.
–
Select the function “count” and
press OK.
Dr. Abu‐Arqoub ١٨
We want to calculate the number of students in the
Calculations using Functions
Dr. Abu‐Arqoub ١٩
Now, press this button to select the cells
Note that here, we didn’t select the students names
values because “count” function gives us “0” value if the selected values were not
numeric
Calculations using Functions
Dr. Abu‐Arqoub ٢٠
Now, press OK button
Calculations using Functions
•
Example 4:
•
Steps:
–
Select the cell where you want
the result to be shown.
–
Press “
fx
” button.
–
Select the function “countif”
–
Press Ok
Dr. Abu‐Arqoub ٢١
Calculate the number of students that their Totals is
above 30.
Calculations using Functions
Dr. Abu‐Arqoub ٢٢
Press Here to select the cells
Selected Cells
Calculations using Functions
Dr. Abu‐Arqoub ٢٣
Here enter the condition. The condition in our example is > 30 since we are looking for the students who
got a total over 30
Write the condition here
Calculations using Functions
•
Example 5:
Dr. Abu‐Arqoub ٢٤
The result.
So, we have one student who got a total above 30
If we said that the student is got “Pass” if he earned a total > = 30, otherwise he
will got “Fail” . We want here to write “Pass” for students who pass and “Fail” for students
who fail.
We will use the function “if” We will start with the
Calculations using Functions
•
Steps:
–
Select the cell where you want the result to appear in.
–
Press “
fx
” button.
–
Select the function “if”
–
Press Ok.
Calculations using Functions
Dr. Abu‐Arqoub ٢٦
To write the condition: Select the cell or write
the name of the cell then write the condition
[Total value>= 30]
The value if the condition gives
TRUE. [Pass] Note: the value
should be in “” The value if the
condition gives FALSE. [Fail]
Calculations using Functions
Dr. Abu‐Arqoub ٢٧
“D4“ holds the Total for “Ruba”
If the condition gives TRUE, the result will
be “Pass”
If the condition gives FALSE, the result will
Calculations using Functions
Dr. Abu‐Arqoub ٢٨
Now extend the formula for the rest
of students
Done ☺
Note:
“min” function
To find a minimum value between some values
“max” function
To find a maximum value between some values
Calculations using Functions
•
CountA
.:This function 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.
Referencing Cell in another sheet
•
Suppose we have three sheets: sheet 1, sheet 2, & sheet 3.
•
Sheet 1 & Sheet 2 have values as shown.
•
If we want Sheet 3 to have the result of mul
ti
plying the values
in Sheet 1 & Sheet 2
Referencing Cell in another sheet
Dr. Abu‐Arqoub ٣١
We go to Sheet 3 and select the cell where the result we
Referencing Cell in another sheet
Dr. Abu‐Arqoub ٣٢
Then write, Sheet1!A4 [Sheet Name]![Cell Name]
Referencing Cell in another sheet
Dr. Abu‐Arqoub ٣٣
Then write, Sheet2!B2 [Sheet Name]![Cell Name]
Then press ENTER
Chart
•
Insert
Chart
Dr. Abu‐Arqoub ٣٤
Pre-Skills 0 10 20 30 Students Names M ar k
s Firs t
Second
Firs t 20 13 15
Second 22 17 12
Ruba Ali Ram i
Chart Title
Legend
Data Table Category (X) Axis title
Chart Insertion & Column Chart
•
We will take only two types of charts: Column chart and Pie chart.
•
Example 1:
If we want to draw a column chart that shows the first and second marks
for each students
?
Dr. Abu‐Arqoub ٣٥
Header
row
Chart Type
•
Steps:
–
Insert
chart
Dr. Abu‐Arqoub ٣٦
Chart Type [Column]
Chart Sub‐Type
Chart – Data Range
Dr. Abu‐Arqoub ٣٧
Click this button to select the cells we want to draw using a
chart
Selected Cells. Note that we have selected the header
values since excel automatically understands that this
Chart – Series in
Dr. Abu‐Arqoub ٣٨
Series in Columns: The chart’s columns represent the values in the sheet’s columns and the Category (X) Axis labels
(Ruba, Ali, & Rami) represent the headers of sheet’s rows (header
column)
Series in Rows: The chart’s columns represent the values in the sheet’s rows and the Category
Chart
‐
Series
Dr. Abu‐Arqoub ٣٩
Because we select the header, excel added the
series automatically. If we didn’t select the header we should add
series manually
These values are filled automatically
by excel.
Chart – Chart Options
Dr. Abu‐Arqoub ٤٠
Chart Title
X‐AXIS
Chart – Chart Options
Dr. Abu‐Arqoub ٤١
Category (X) axis: Show or hide these
Chart – Chart Options
Dr. Abu‐Arqoub ٤٢
Lines intersects the X‐Axis
Chart – Chart Options
Dr. Abu‐Arqoub ٤٣
Show or hide the legend
The place where you want the legend to
appear
Chart – Chart Options
Dr. Abu‐Arqoub ٤٤
Show above each column a series
name
Show above each column a category
name
Show above each column its value
Show the separator between these
values
[new line, comma, space …etc]
Show above each column its legend
Chart – Chart Options
Chart – Chart Options
Dr. Abu‐Arqoub ٤٦
Show or hide the data table
If the data table is shown, show or hide
Chart – Chart Location
Dr. Abu‐Arqoub ٤٧
Save the chart in another sheet
Save the chart in this sheet under the a
specific name Click Finish Pre-Skills 0 10 20 30 Students Names M ar k
s Firs t
Second
Firs t 20 13 15
Second 22 17 12
Chart – Edit
Dr. Abu‐Arqoub ٤٨
Pre-Skills 0 10 20 30 Students Names M ar k
s Firs t
Second
Firs t 20 13 15
Second 22 17 12
Ruba Ali Ram i
Change Font, Pattern
Chart Type
To re‐select the cells
Chart Options
Change the location of the chart [move it
Chart – Edit
Dr. Abu‐Arqoub ٤٩
Change Pattern for the plot area
Change Pattern, Font and Place for the
Chart – Edit
Dr. Abu‐Arqoub ٥٠
Format the Data Series Patterns and
Chart – Edit
Dr. Abu‐Arqoub ٥١
Change the Pattern, and Font of the data
Chart – Edit
Dr. Abu‐Arqoub ٥٢
Change the Pattern, Scale, Alignment and
Chart – Scale
Dr. Abu‐Arqoub ٥٣
If you want to update any option of
these, you should uncheck the Auto checkbox beside the
Chart – Scale
Chart – Pie Chart
•
Example 2:
Draw a pie chart that shows each section in the field with its
corresponding percentage.
Chart Type
Steps:
–
Insert
chart
–
Select Pie Chart
Dr. Abu‐Arqoub ٥٦
Chart – Data Range
Dr. Abu‐Arqoub ٥٧
Select the cells
Click Next
In Pie Chart, Excel will detect automatically the correct series in. So,
Chart – Chart Options
Dr. Abu‐Arqoub ٥٨
Chart
‐
Location
Dr. Abu‐Arqoub ٥٩