Home

Insert

Draw

Page Layout

Formulas

Data

Review

Comments

Share

$$

Sensitivity

Add$-$

$\backslash$table$[[$A$,$B$,$C$,$D$,$E$,$F$,$G$,$H$,$I,J$],[$ID$,$Pay,,ID$,$Last Name,First Name,Pay,,,$],[990678,$$$84,289,,110608,$Doe,John,,,,$],[830385,$$$137,670,,253072,$Cline,Andy,,,,$],[795574,$$$190,024,,352711,$John,Elton,,,,$],[580622,$$$122,604,,391006,$Pan,Peter,,,,$],[549457,$$$111,709,,392128,$Favre,Brett,,,,$],[391006,$$$168,114,,549457,$Elway,John,,,,$],[352711,$$$89,627,,580622,$Manning,Eli,,,,$],[253072,$$$149,946,,602693,$Vick,Michael,,,,$],[611810,$$$64,757,,611810,$Woods,Tiger,,,,$],[602693,$$$71,478,,612235,$Jordan,Michael,,,,$],[110608,$$$131,505,,795574,$Stark,Tony,,,,$],[,,,830385,$Williams,Prince,,,,$],[,,,990678,$Pitt,Brad,,,,$],[,,,,,,,,,],[,,,,,,,,,],[,,,,,,,,,],[,,,,,,,,,],[,,,,,,,,,],[,,,,,,,,,],[,,,,,,,,,],[,,,,,,,,,],[,,,,,,,,,],[,,,,,,,,,],[,,,,,,,,,],[,,,,,,,,,],[,,,,,,,,,],[,,,,,,,,,],[,,,,,,,,,],[,,,,,,,,,],[,,,,,,,,,],[,,,,,,,,,],[,,,,,,,,,],[,,,,,,,,,],[,,,,,,,,,],[,,,,,,,,,],[,,,,,,,,,],[,,,,,,,,,]]$

Pay

Payband ipboard

Font

Number

Cells

Editing

Sensitivity

Add$-$in:

$\backslash$table$[[$A$,$B$,$C$,$D$,$E$,$F$,$G$,$H$,$I,J$,$K$],[$ID$,$Last Name,First Name,Pay,Pay Band,,Pay Min,Pay Band,,,$],[110608,$Doe,John,$ $131,505,$Level D$,,$$$25,000,$Level A$,,,],[253072,$Cline,Andy,$ $149,946,$Level D$,,$$$50,000,$Level B$,,,],[352711,$John,Elton,$ $89,627,$Level C$,,$$$75,000,$Level C$,,,],[391006,$Pan,Peter,$ $168,114,$Level E$,,$$$100,000,$Level D$,,,],[392128,$Favre,Brett,,$0,,$$$150,000,$Level E$,,,],[549457,$Elway,John,$ $111,709,$#N$/$A$,,,,,,],[580622,$Manning,Eli,$ $122,604,$#N$/$A$,,,,,,],[602693,$Vick,Michael,$ $71,478,$#N$/$A$,,,,,,],[611810,$Woods,Tiger,$ $64,757,$#N$/$A$,,,,,,],[612235,$Jordan,Michael,,#N$/$A$,,,,,,],[795574,$Stark,Tony,$ $190,024,$#N$/$A$,,,,,,],[830385,$Williams,Prince,$ $137,670,$#N$/$A$,,,,,,],[990678,$Pitt,Brad,$ $84,289,$#N$/$A$,,,,,,]]$Download the Excel file

a$.$ The file is located in Blackboard $>$ Content $>$ Data $>$ EA$4-$Starter$-$File.xlsx

Use VLOOKUP

a$.$ For the Pay sheet, please use the VLOOKUP function to complete column G

using the data from column A and $B.$

b$.$ If there are no matches, use IFERROR to display the word $>$ "Charity" without

quotes.

Use XLOOKUP

a$.$ For the Payband sheet, please use the XLOOKUP function to complete column E

using the data from column G and H $.$

b$.$ If there are no matches, use XLOOKUP parameters to display the word "Charity"

without quotes. Use IF and ISNUMBER functions to handle blank cells.

c$.$ HINT: Use an approximate match instead of an exact match. Select the Pay Band

where the Pay Min is lower than the individual's pay.

Answer Questions about Data and Formula Results

a$.$ Create a new sheet named "Summary" and answer the following questions

using formulas$/$functions analyzing the data from the Payband sheet.

b$.$ What is the last name of the individual with the highest salary? $($Place in

Summary!A$1)$

c$.$ What is the last name of the individual with the lowest salary? $($Place in

Summary!A$2)$

d$.$ How many individuals are in the $($Level C$+$Level D$)$ Pay Band? $($Place in

Summary!A$3)$

e$.$ How many individuals have an ID that begins with the number $8($Place in

Summary!A$4)$

f$.$ What is the sum of salaries for individuals whose first name begins with the

letter E$?($Place in Summary!A$5)$

i need teh answers$/$formulas for $2,3,$ and $4.$