﻿ Excel 2007 exercises:count counta countif sum and if functions

# Excel 2007 exercises:count counta countif sum and if functions

## count counta countif sum and if functions

1. Calculate the total score of each student In cell G3 write the formula:
=SUM(D3:F3) Press Enter and Click to drag it down until the last cell of the data sheet

2.Display the word "Fail" if the student failed and "Pass" if the student passed in Mention column.
Students are considered failed if their total is less than 30. Otherwise, they pass.
In cell H3 write the formula:
=IF(\$G3<30,"Fail","Pass") Press Enter and Click to drag it down until the last cell of the data sheet

3. Count the number of students who failed
In cell A13 write "# of failed students:"
In cell B13 write the formula: =countif(\$H3:\$H10,"Fail") Press Enter

4. Count the number of students who passed In cell A14 write "# of passed students.
In cell B14 write the formula: =countif(\$H3:\$H10,"Pass") Press Enter

5. Calculate the percentage of students who failed
In cell A15 write "% of failed students"
In cell B15 write the formula: =B13/counta(\$H3:\$H10) Press Enter Format the cell in percentage

6. Calculate the percentage of students who passed In cell A16 write "% of passed students"
In cell B16 write the formula: =B14/counta(\$H3:\$H10) Press Enter
Format the cell in percentage

7. Display grade letter of each student in Grade column, based on the following conditions:

75<total score<=80-->A
70<total score<=75-->B+
65<total score<=70-->B
55<total score<=65-->C+
50<total score<=55-->C
45<total score<=50-->D+
40<total score<=45-->D
35<total score<=40-->E+
30<=total score<=35-->E
total score<30 -->F

In cell I3 write the formula: =IF(AND(\$G3>75,\$G3<=80),"A",IF(AND(\$G3>70,\$G3<=75),"B+",
IF(AND(\$G3>65,\$G3<=70),"B", IF(AND(\$G3>55,\$G3<=65),"C+",IF(AND(\$G3>50,\$G3<=55),"C",
IF(AND(\$G3>45,\$G3<=50),"D+", IF(AND(\$G3>40,\$G3<=45),"D",IF(AND(\$G3>35,\$G3<=40),"E+",
IF(AND(\$G3>30,\$G3<=35),"E","F")))))))))

Press Enter and Click to drag it until the last cell of the data sheet

Syntax: SUM(number1,number2, ...)
COUNTA(value1,value2,...)
COUNTIF(range,criteria)
IF(logical_test,value_if_true,value_if_false)