Module 7 Training 2022

Module 7 Training 2022
EX9726 Create a formula using the AVERAGEIF function. - ANSWER Click cell
Formulas tab, Function library, more functions, statistical, AVERAGEIF
type range, tab
type criteria, tab
type average range
click ok

EX9386 Add a calculated field to a PivotTable. - ANSWER Click cell
Pivot Table appears
Analyze tab
caluculations, fields items & set
calculated field
type Name
Formula: '=January+February+March
Click OK

EX9705 Create a formula using the HLOOKUP function. - ANSWER Click cell
Formulas tab, Function library
Insert function
click arrow
Lookup & Reference
HLOOKUP
click OK
type text

EX9818 Create a formula using the RATE function. - ANSWER Click cell
type '=RATE(c6,c7,c3,c8)
ENTER

EX9979 Create a formula using the INDEX function. - ANSWER Click cell j7
type =INDEX(A3:G17,15,3)
ENTER
row 15, column 3

EX10824 Create a formula using the MATCH function. - ANSWER Click cell
formulas tab
function library, lookup & reference
MATCH
type RELICO
type cells
type 0
click OK

EX10833 Create a nested formula using the INDEX and MATCH functions. - ANSWER
click cell

, Module 7 Training 2022
type "=INDEX(A5:J18,MATCH("M-07",C5:C18,0),6)

A5:J18 is yhe whole table
M-07 is on instructions
C5:C18 is Manager ID (next to M-07 on instructions)

EX9698 Create a formula using a nested IF function. - ANSWER click cell
click insert function button by formula bar
click arrow
logical
IF
click OK
type D5=3 in Logical_test box
TAB
type E5*.1 in Value if true box
type IF(D5=2, E5*.05,0)
OK

EX9725 Create a formula using the AND function. - ANSWER Click cell
formulas tab, function library, logical
click AND
type in Logical1 textbox
TAB
type in Logical2 textbox
Click OK

EX9727 Create a formula using the OR function. - ANSWER Click cell
Formulas tab
Function Library, Insert Function
click arrow
Logical
click OR function
OK
type Logicals in textbox
Click OK

EX10851 Create a formula using the AVERAGEIFS function. - ANSWER Click cell
type =AVERAGEIFS(
click and drag e5:e18
then type comma
click and drag c5:c18
type , "Blue",
click and drag d5:d18
type , "FIRE")

EX10860 Create a formula using the IFS function. - ANSWER Click cell