Introduction: Target Graph in Excel Using Polar Chart

By

This Instructable is a proof of concept rather than a "this will work for every aspect imagianable". Please be inspired by it and tweak it to fit your specific use.

I made this for our company to show order delivery precision. The old one which showed the hit rate in percent was just plain useless because if we missed the delivery date by one day (even ahead of time) the system would report it as a miss. We had around 70-80% hit rate but 99% if we calculated 1 day ahead or late. Thus this target graph would be alot more useful and is not making everyone think that the delivery is aweful.

Attachments

Step 1: Make Your Data

We need for this graph to work to have the time difference (or whatever you want to display) to be in always positive so I use the absolute "=ABS()" formula to make this happen.

Step 2: Insert a Polar Chart

As you see the order names are showing for every row and thus making the graph ugly looking. We'll remove them by doing... (next step).

Step 3: Filter Really Off Deliverys

We make a new column that evaluates the difference. If the difference is above a given number the order name is shown. I use "=IF(...)". We then have to reassign the values to this column (shown in the second and third picture).

Step 4: Remove Lines Etc.

Right click on all the chart elements (lines etc.) and use "no line" and "no filling" etc.

Step 5: Use Picture As Break Points

Right click on the graph break points and remove the lines between them. Then use the "Bullet_30x28.png" as a picture for the break points.

"Bullet_30x28.png" found in "Pictures.zip" in the introduction step...

Step 6: Use a Background Image As Target

Right click on the graph area and use the picture "Target_800x800.png" as a background picture.

"Target_800x800.png" found in "Pictures.zip" in the introduction step...

Step 7: Extra Tweak (scatter Bullet Holes a Little)

By using this formala for the "Difference In Days" column the "bulls eye" shots will scatter just a little bit.

=IF(ABS(..)=0;1/RANDBETWEEN(1;10);ABS(..))