Question

Clipboard lignment Styles Cells 2012 Total Sales by Agent anuory February Morch Aprit Mayune Frends Bonus $3,671 $ 2,973 4,017 $ 5,040 4,963 5,497 4 Collins1,5499 2,267 301 4.0 3,798 2,996 6,94B 4,205 5,523 6,597 5,147 6 McDonnough 2.003 4,190 3,196 1,975 2,936S 3,498 4,038$ 2,0645 7,015 6,832 $5,971 $5,497 8 Shannon 1,12$ 866 2,109 3,3854,078S3,598 Avcry 11 Task 3 11Create Line sparklines in column H 15in the Summary sheet to display Insert an IF function in column I that displays $600 bonus if an agent's average sales are greater than the average of all sales for the six months. Use two nested AVERAGE functions in the logical test araument of the IF function to make the comparison. Use cell refcrencc for Bonus. six month trends for each agent Shaw the high paint in each

Answer 1

Step for Trends

Step 1: Select the data range for the sparklines (For Avery, select range = B3 to G3)

Step 2: On the Insert tab, click Sparklines,

Step 3: For Set high point, click on sparklines and then select the design tab and thereafter click on "High Point".

Result view

- Home nert Page Layout Formulas Data Review V s Cut aCopy Format Painter C AutoSum Fill ClearFilter Select в 1 프· ー·タ, 습. 困Merge & Center. .:8 Conditional Format . Cell Formatting as Table Styles ǐE E론 $, % , Insert Delete Format Sort &Find & Clipboard Font Alignment Cells dting 2012 Total sales by Agent 1 January -June 2 anuaryFebruary March April May June Trends Bonus 3 Avery 4 Collins 5 Kinsley 3,67 2,973 $ 4,017 $5,040$ 4,963$ 5,497 S 1,549 $992 $ 2,267 $ 3,201 $ 4,008 3,798 2,996 6,948 4,205 $ 5,523 $ 6,597 $ 5,147 6 McDonnough 2,003 $ 4,190 $ 3,496 $ 1,975 $ 2,936 3,498 600 600 $ 4,038 $ 2,064 $ 7.015 $ 6,832 $ 5,971 $ 5,497$600 7 Ross 8 Shannon $ 1,172$866 $ 2,109 $ 3,385 $ 4,078 3,598 10 hd TTT Sheet1 18. Sheet1 19, Sheet1 20. Sheet1 21/Sheet122 :4<トト1 Ready Sheet1 23 Sheet1 24 Sheet1 25 Search the web and Windows 15-02-19

Formula view

- Home nert Page Layout Formulas Data Review V 義Cut aCopy Σ AutoSum , Fill в 1 프· ー·タ, 습. 困Merge & Center. .:8 Conditional Format . Cell Formatting as Table Styles Paste ǐE E론 $, % , Insert Delete Format Sort &Find & Format Painter Clipboard ClearFilter Select Font Alignment Styles Cells dting 1 2 Trends Bonus 3IF(AVERAGE(B3:G3)PAVERAGE($B$3:$G$8),600,"") IF(AVERAGE(B4:G4) AVERAGE(SB$3:$G$8),600,"") - :F(AVERAGE(B5:G5)>AVERAGE($BS3:SG$8),600,"") IF(AVERAGE(B6:G6) AVERAGE(SB$3:$G$8),600,"") IF(AVERAGE(B7:G7)>AVERAGE(SB$3:$G$8),600,"") IF(AVERAGE(B8:G8) AVERAGE(SB$3:$G$8),600,"") 4 10 hd HTT Sheet1 18. Sheet1 19, Sheet1 20. Sheet1 21/Sheet122 :4<トト1 Ready Sheet1 23 Sheet1 24 Sheet!25 Search the web and Windows 15-02-19