Y8 Lesson 3_ EXCEL FUNCTIONs(SUM,AVERAGE,MAX,MIN,COUNTA,COUNTIF)

Before we begin the quiz, please download the quiz source files. To answer second part of the quiz, you must insert the formulas or functions into this spreadsheet file.

Revision

Excel functions:
=SUM( ), =AVERAGE( ), =MIN( ), =MAX( ), =COUNTA ( ) , =COUNTIF ()

SUM, AVERAGE, MIN, MAX, COUNTA, and COUNTIF functions

Quiz - Part 1

Multiple Choice

To alert Excel that you are entering a formula and not text, type a(n) _________________ proceeding the formula

• &

• #

• =

• *

Multiple Choice

What function was used to find the highest data ?

• AVERAGE

• MIN

• SUM

• MAX

Multiple Choice

The function that lets you add up a range of cells or values

• AVERAGE

• SUM

• MIN

• MAX

Multiple Choice

What function was used to find the highest data ?

• AVERAGE

• MIN

• SUM

• MAX

Multiple Choice

What is the name of the bar in Excel where information appears as you enter data/formula called?

• File Bar

• Help Bar

• Formula Bar

• Window Bar

Multiple Choice

What function was used to find the lowest data ?

• AVERAGE

• MIN

• MAX

• SUM

Multiple Choice

What´s the difference between a formula and a function?

• There is no difference

• Functions are more complicated than formulas

• Formulas are designed by the user and functions are predefined in Excel

• They both start with the =

Multiple Choice

Looking at the data in the picture, which function can be used to find the youngest and oldest athlete?

• MIN & AVERAGE

• MAX & AVERAGE

• MIN & MAX

• COUNTA & MAX

Multiple Choice

The Microsoft Excel ___________function counts the number of cells in a range, that meets a given criteria.

• counta

• countif

• count

• countblank

Multiple Choice

If you want to count cells with any type of data (and not just numbers), simply replace COUNT by________ .

• countblank

• counta

• countb

• max

Quiz: Part 2

Open the Online store sales data in Excel to answer the following questions:

Fill in the Blank

What formula do you use to calculate the total revenue for laptops?

=

*

Fill in the Blank

What formula do you use to calculate the total revenue for all products?

=

(

:

)

Fill in the Blank

What is the total revenue for all products?

$

Fill in the Blank

What formula do you use to find the lowest price per unit among all products ?

=

(

:

)

Fill in the Blank

Which product has the lowest unit price among all products?

Fill in the Blank

What formula do you use to find the highest price per unit among all products ?

=

(

:

)

Fill in the Blank

Which product has the highest unit price among all products?

Fill in the Blank

What formula do you use to find the highest quantity sold among all products ?

=

(

:

)

Fill in the Blank

Which product has the highest quantity sold?

Fill in the Blank

What formula do you use to count the total number of products in the dataset?

=

(

:

)

Fill in the Blank

What formula do you use to count the number of products that were sold at price more than $50?

=

(

:

,

"

>

"

)

Fill in the Blank

How many products were sold at a price of more than $50?

Fill in the Blank

What formula do you use to calculate the quantity sold of products with fewer than 100 sales?

=

(

:

,

"

<

"

)

Fill in the Blank

How many products were sold in quantities less than 100?

Fill in the Blank

What formula do you use to calculate the number of products that falls in the "Electronics" category?

=

(

:

,

"

"

)

Fill in the Blank

How many products fall under the "Electronics" category?

Fill in the Blank

How many products were sold in quantities less than 100?

Fill in the Blank

What formula do you use to calculate the average price per unit across all products?

=

(

:

)

Fill in the Blank

What is the average price per unit across all products?

(Write your answer in 2 decimal places)

$

.

Fill in the Blank

What formula do you use to calculate the average quantity sold across all products?

=

(

:

)

Fill in the Blank

What is the number of products that were sold below the average quantity sold?

Open Ended

Exit ticket:

Imagine you have a list of student test scores. How would you calculate the percentage of students who scored above 90 using the COUNTIF function?

Poll

Do you require more practical tasks using the SUM, AVERAGE, MAX, MIN, COUNTA, COUNTIF functions?

• yes

• no