Excel 2007 exercises: countif sumif averageif rank


countif sumif averageif rank functions

In this exercise, you will learn to highlight the rows of table based on specified conditions and use countif, max,min, sumif, averageif, and rank functions.

Excel 2007 hight cells and functions count rank countif rank exercises

1. Highlight product(s) with cheapest price
Click Home->Conditional Format->Manage rule...->New rule..
Select the last item in the list
In the formula box type:
=$C3=MIN($C$3$:C$10)

2. Highlight product(s) with highest price
Click Home->Conditional Format->Manage rule...->New rule..
Select the last item in the list
In the formula box, type:
=$C3=MAX($C$3$:C$10)

3. Calculate the total quantity of the product with Order ID=10250
In cell A11, type:
=SUMIF(A3:A10,"10250",D3:D10)

4. Count the products with Order ID=10248
In cell A12, type:
=COUNTIF(A3:A10,"10248")

5. Count the products with Order ID=10248 and their quantities are greater than 30 and less than 70
In cell A13, type:
=COUNTIFS(A3:A10,"10248",D3:D43,">30",D3:D10,"<70")

6. Count the products with their names beginning with “ch”
In cell A14, type:
=COUNTIF(B3:B10,"ch*")

7. Count the products with their unit prices > 40 and their quantities >30
In cell A15, type:
=COUNTIFS(C3:C10,">40",D3:D10,">30")

8. Calculate the average of unit prices of products with Order ID =10250
In cell A16, type:
=AVERAGEIF(A3:A10,"10250",C3:C10)

9. Use rank function to rank the products bases on their unit prices
In Cell F3, type:
=RANK(C3,$C$3:$C$10)

Syntax:
MAX(number1,number2,...)
MIN(number1,number2,...)
COUNTIF(range,criteria)
COUNTIFS(range1, criteria1,range2, criteria2…)
SUMIF(range,criteria,sum_range)
RANK(number,ref,order)



Comments




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.