Programs: Microsoft Excel : Lesson 4


 

nKnow the AVERAGE, MAX, MIN, COUNT, IF, & VLOOKUP Functions

Main Objectives:
  • MAX/MIN Fuctions
  • Average Function
  • Count/Counta Functions
  • IF function
  • VLOOKUP Function.
nKnow the AVERAGE, MAX, MIN, COUNT, IF, & VLOOKUP Functions
Objective # 1 MAX & MIN

MAX & MIN
nIt will give you the maximum or minimum value from the selected cells
 
=MAX( column and row numbers) =MAX(B2:B5)
=MIN( column and row numbers) =MIN(B2:B5) as shown on the picture
Objective # 2 COUNT & COUNTA
n
COUNT - Counts the number of cells that contain numbers and numbers within the list of arguments. Use COUNT to get the number of entries in a number field in a range or array of numbers. 
Syntax:
COUNT(value1,value2, ...)
 
COUNTA - Counts the number of cells that are not empty and the values within the list of arguments. Use COUNTA to count the number of cells that contain data in a range or array.
Syntax:
COUNTA(value1,value2, ...)
nBoth exclude empty cells

 

nHowever COUNTA function includes text entries
 
Objective # 3 AVERAGE
nComputes the average for selected cells
 
You have to type in = before average(range) in order for the function to work or you could go to insert function then choose from the list

 

=AVERAGE(B2:B5) = 26

 

Objective # 4 IF
nEnables decision making within a worksheet
n
 
Three arguments condition,value if true, and value when false
=IF(B2>B3,D15,D12) therefore the value would equal 90 because the condition was true
 
Objective # 5 VLOOKUP
Assigns a entry to a cell based on a numeric value in another cell
 
This means that it is looking for the cell whose value is the highest number that is less than or equal to the value of the lookup value (number it is looking up).
nVLOOKUP(B4,$B$2:$E$7,3)
nB4 = Value to be looked up
n$B$2:$E$7 = range of the table
n3 = column number containing entry

 

 After completing the DEMO PROBLEM, you will have enough visual basis and knowledge to work on a spreadsheet alone. 

Exercises
1. The COUNT function includes text cells

True    False

 2. Empty cells and text cells are excluded in the MAX and MIN Functions

True    False

3. The SUM function tallies the number of entries in a range of cells

True    False

4. The IF function enables decision making within a worksheet

True    False

5. Instead of typing in the function you can go to insert function

True    False

6. The VLOOKUP function needs the value to be looked up, the range of calls

True    False

7. 1.What would =COUNT(A1:A4)read?

8. 2.What would SUM(A1:A4) read?

9. 3.What would =MIN(A1:A3) read?

10.4.What does the IF function do?

11. 5.What does the VLOOKUP function do?

12. 6.What would =AVERAGE(A1:A3,20)?

7.

 

[FrontPage Save Results Component]

back to lesson #3

continue to lesson #5

Back to Lessons 

Programs: Microsoft Excel: Lesson 4