Chart Help

[jedilefty]

Hello,

I'm having trouble creating a chart from a table similar to the one below. I need 2 Goal lines, one for the Internal Goal and the 2nd for the website Goal. If possible I'd like the bars to be green if the Actual Average is less than the Internal Goal, Yellow if between Internal Goal and Website Goal, and Red if greater than the Website Goal. I'm trying to get the Prior week as a Dot on the graph with a data label.

Any help would be greatly appreciated!!

Finish Type	Internal Goal	Website Goal	Actual Average	Prior Week
Type #1	2	10	5	15
Type #2	2	15	11	21
Type #3	2	20	10	26
Type #4	2	20	6	12

 

[awoohaw]

Mr Excel 8.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
3	Finish Type	Internal Goal	Website Goal	Actual Average	Prior Week	Red Average	Yellow Average	Green Average
4	Type #1	3	10	2	15	0	0	2
5	Type #2	3	15	10	21	0	10	0
6	Type #3	3	20	15	26	0	15	0
7	Type #4	3	20	25	12	25	0	0
8
9
10
11
12
13
14
Sheet9

Cell Formulas
Range	Formula
F4:F7	F4	=IF(D4>C4,D4,0)
G4:G7	G4	=IF(AND(D4>=B4,D4<=C4),D4,0)
H4:H7	H4	=IF(D4<B4,D4,0)

 

[awoohaw]

I did not put the data label on the prior week dot. But you can add that. The prior week is an uncolored line with colored markers.

 

[awoohaw]

you also may not need the secondary axis now. for some reason i needed it when i was building it.

 

[jedilefty]

you also may not need the secondary axis now. for some reason i needed it when i was building it.

Thank you for your help on this. Can you please list the steps on how you built this chart?

 

[jedilefty]

Thank you for your help on this. Can you please list the steps on how you built this chart?

I have attempted to build this chart and seem to almost have it completed. My struggle right now is to have the bars change color based on if they are either below the internal goal line, in between the internal and website goal and above the website goal.

 

[awoohaw]

1. Create the chart combo (line-column combo chart), only for your intial four series. (if you add all at once you may not be able to get the colored columns overtop of the AVERAGE column.
2. Update the chart so that there are only 3 lines and 1 column (Actual Average).
3. Edit the Actual Average Data series so that the overlap is 100%, and Gap is 100%.
4. Make the additional columns for GREEN/YELLOW/RED. See the formulas above.
5. Do steps 6,7,8 for each color average separately, (6,7,8 for green, 6,7,8 for yellow, 6,7,8 for red)
6. Select data and add the additional data series and change overlap and gap both to 100% each time,
7. Change the fill color according to your series desire
8. Do either of these: select "no border color" or match the border color to your fill.
9. Compare your lines and columns. make sure that the number scales are appropriate. If they are not, you may want to turn on "secondary" axis for the items that don't align with the left axis. Then makes the axis minimums and maximum values match. Then you can probably turn off secondary axis.
10. Click the "Prior Week" line. besure your line markers are on, add marker labels, then select "no color" for the the line.
11. Do what ever else you want to do.
12. NOTE: If you want you may probably be okay with dropping the AVERAGE column from the chart. I did not do that, but this column is covered by the other three all the time.

 

[awoohaw]

For clarification, in step 4, when I say create the columns... I mean the calculations in the data source table. The columns for the chart are created in subsequent steps.

 

[jedilefty]

1. Create the chart combo (line-column combo chart), only for your intial four series. (if you add all at once you may not be able to get the colored columns overtop of the AVERAGE column.
2. Update the chart so that there are only 3 lines and 1 column (Actual Average).
3. Edit the Actual Average Data series so that the overlap is 100%, and Gap is 100%.
4. Make the additional columns for GREEN/YELLOW/RED. See the formulas above.
5. Do steps 6,7,8 for each color average separately, (6,7,8 for green, 6,7,8 for yellow, 6,7,8 for red)
6. Select data and add the additional data series and change overlap and gap both to 100% each time,
7. Change the fill color according to your series desire
8. Do either of these: select "no border color" or match the border color to your fill.
9. Compare your lines and columns. make sure that the number scales are appropriate. If they are not, you may want to turn on "secondary" axis for the items that don't align with the left axis. Then makes the axis minimums and maximum values match. Then you can probably turn off secondary axis.
10. Click the "Prior Week" line. besure your line markers are on, add marker labels, then select "no color" for the the line.
11. Do what ever else you want to do.
12. NOTE: If you want you may probably be okay with dropping the AVERAGE column from the chart. I did not do that, but this column is covered by the other three all the time.

Thank you for your help! The charts turned out pretty nice!!