﻿ Excel 2007 exercises: countif sumif averageif rank

# 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.

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)