Excel 2007 exercises:count counta countif sum and if functions


count counta countif sum and if functions

Excel 2007 exercises: count, sum, if formula

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)



Comments

Stephanie comment

 Stephanie

Is there an excel worksheet for all these assignments and solutions? That is what is missing


2018-06-03
Aamir Raza comment

 Aamir Raza

Very Good, need some critical question we face in interviews.


2017-05-16



This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.