Question

H I J K L M N O P Q R S T U V 2012 Total Sales by Agent A B C D E F G 2012 Total Sales by Agent January - June January February March April May June Avery $ 3,671 $ 2,973 $ 4,017 $ 5,040 $ 4,963 $ 5,497 Collins $ 1,549 $ 992 $ 2,267 $ 3,201 $ 4,008 $ 3,798 Kinsley $ 2,996 $ 6,948 $ 4,205 $ 5,523 $ 6,597 $ 5,147 McDonnough $ 2,003 $ 4,190 $ 3,496 $ 1,975 $ 2,936 $ 3,498 Ross $ 4,038 $ 2,064 $ 7,015 $ 6,832 $ 5,971 $ 5,497 Shannon $ 1,172 $ 866 $ 2,109 $ 3,385 $ 4,078 $ 3,598 Trends Bonus Bonus $ 600 Task 5 Task 6 Create Line sparklines in column H in the Summary sheet to display six-month trends for each agent. Show the high point in each sparkline. Insert an IF function in column I that displays a $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 argument of the IF function to make the comparison. Use cell reference for Bonus.

Answer 1

Trends Bonus 6,000 Agent Avery Colins Kinsley Mcdonnough Ross Shannon January 3,671 1,549 2,996 2,003 4,038 1,172 February March 2,973 4,017 992 2,267 6,948 4,205 4,190 3,496 2,064 7,015 866 2,109 April May June 5,040 4,963 5,497 3,201 4,008 3,798 5,523 6,597 5,147 1,975 2,936 3,498 6,832 5,971 5,497 3,385 -4,078 ...3,598 6,000 6,000

Below is excel worksheet with formula :

Agent	January	February	March	April	May	June	Trends	Bonus
Avery	3671	2973	4017	5040	4963	5497		=IF(AVERAGE(B5:G5)>AVERAGE($B$5:$G$10),6000,0)
Colins	1549	992	2267	3201	4008	3798		=IF(AVERAGE(B6:G6)>AVERAGE($B$5:$G$10),6000,0)
Kinsley	2996	6948	4205	5523	6597	5147		=IF(AVERAGE(B7:G7)>AVERAGE($B$5:$G$10),6000,0)
Mcdonnough	2003	4190	3496	1975	2936	3498		=IF(AVERAGE(B8:G8)>AVERAGE($B$5:$G$10),6000,0)
Ross	4038	2064	7015	6832	5971	5497		=IF(AVERAGE(B9:G9)>AVERAGE($B$5:$G$10),6000,0)
Shannon	1172	866	2109	3385	4078	3598		=IF(AVERAGE(B10:G10)>AVERAGE($B$5:$G$10),6000,0)

The table data range is A4 to I10.

For inserting trend in excel sheet :

1. Click on the Insert tab in excel

2. Please select one of the options in spark line

We had selected Columns

Line Column Win/ Loss Sparklines

3. Give the data Range in dialogue box

4. Once Sparkline appears in cell, please go to that cell and select "design tab" & once selected following parameter appears & from that select "High Point". High point appears as bar marked as "red"

✓ High Point Low Point Negative Points First Point Last Point Markers