G
Guest
I want to create a chart of target vs achieved. Target is annual target and
achievements in month.
achievements in month.
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
L
LeAnne
Vikas said:I want to create a chart of target vs achieved. Target is annual target > and achievements in month.
Target vs. achieved *what*? Funds? Solicitations? Widgets? Pickled
herring?
What kind of chart did you have in mind? Column? Pie? Line graph?
Combination (like maybe a horizontal line for the target and bars for
each month's "achievement")?
How are your data arranged in the worksheet? How do you want them
displayed on your chart?
You have given nothing that anyone can go on if they want to try and
help you. More info is needed.
Good luck,
LeAnne
J
Jon Peltier
Probably you want a line chart or column chart, with month names for the
category labels, and a monthly cumulative value for the Y values. For a
target, you can use these simple approaches:
Line Chart: two series, with the annual target in every cell of the
second series.
Monthly YTD Year Target
Jan 1100 15000
Feb 2400 15000
.... .... .....
Dec 15075 15000
With the cell above Jan and left of Monthly YTD blank, make a line
chart. You will get two lines in the chart. Format the target to have
lines but no markers.
Column Chart: two series, with the annual target only in the last cell
of the second series.
Monthly YTD Year Target
Jan 1100
Feb 2400
.... ....
Dec 15075 15000
You get two series, where Jan-Dec have the growing cumulative total, and
the target is out there at the end of the year.
If you want, you could use the first set of data, make a line chart,
then convert just the first series to a column chart. The line stays at
the top, while the columns grow left to right. Often mixing chart types
helps the viewer conceptualize that there are two types of data
(predicted and actual).
If you prefer a different kind of line, like a gridline that goes the
entire width of the chart, try one of these techniques:
http://peltiertech.com/Excel/Charts/AddLine.html
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
category labels, and a monthly cumulative value for the Y values. For a
target, you can use these simple approaches:
Line Chart: two series, with the annual target in every cell of the
second series.
Monthly YTD Year Target
Jan 1100 15000
Feb 2400 15000
.... .... .....
Dec 15075 15000
With the cell above Jan and left of Monthly YTD blank, make a line
chart. You will get two lines in the chart. Format the target to have
lines but no markers.
Column Chart: two series, with the annual target only in the last cell
of the second series.
Monthly YTD Year Target
Jan 1100
Feb 2400
.... ....
Dec 15075 15000
You get two series, where Jan-Dec have the growing cumulative total, and
the target is out there at the end of the year.
If you want, you could use the first set of data, make a line chart,
then convert just the first series to a column chart. The line stays at
the top, while the columns grow left to right. Often mixing chart types
helps the viewer conceptualize that there are two types of data
(predicted and actual).
If you prefer a different kind of line, like a gridline that goes the
entire width of the chart, try one of these techniques:
http://peltiertech.com/Excel/Charts/AddLine.html
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
G
Guest
I would like to have a column chart, with the annual target on y-axis. The
months would be stacked on one upon other and showing the % achieved YTD.
Lets say the target for 2004 is 2000.
Region1: Jan: 125, Feb: 175, Mar: 180, Apr: 200 .....
Region2: Jan: 128, Feb: 95, Mar: 200, Apr: 210 ....
Region3: Jan: 128, Feb: 95, Mar: 200, Apr: 210 ....
Region4: Jan: 128, Feb: 95, Mar: 200, Apr: 210 ....
The Regions must be on X-Axis, and months stacked on one upon other, with
Comparison of achieved vs target. If % achieved is shown that would be very
helpful.
months would be stacked on one upon other and showing the % achieved YTD.
Lets say the target for 2004 is 2000.
Region1: Jan: 125, Feb: 175, Mar: 180, Apr: 200 .....
Region2: Jan: 128, Feb: 95, Mar: 200, Apr: 210 ....
Region3: Jan: 128, Feb: 95, Mar: 200, Apr: 210 ....
Region4: Jan: 128, Feb: 95, Mar: 200, Apr: 210 ....
The Regions must be on X-Axis, and months stacked on one upon other, with
Comparison of achieved vs target. If % achieved is shown that would be very
helpful.
J
Jon Peltier
Vikas -
This is different than the arrangement I assumed from your original
post. Do you want Target (2000) on the Y axis, or % (100%)? I am
guessing from context you want the numbers, although if the goal is the
same for all regions, it hardly matters. Also, I am guessing you want
some kind of % achieved to date.
Here's how I'd do it. Arrange the data thus:
Rgn1 Rgn2 Rgn3 Rgn4
Jan
Feb
Mar
....
Dec
Sum
Goal
%YTD
Populate the grid with the monthly numbers. The Sum row has formulas
which total the months above. The Goal row has your 2000 target. The
%YTD has the Sum divided by the Goal, as a percentage. The top left cell
is blank.
Select this entire range except for the %YTD row but including the RGNi
headers and the Month column. Make a stacked column chart with series in
rows. Select the Goal series, choose Chart Type from the Chart menu, and
choose Line, the subtype with lines but no markers. Do the same for the
Sum series, then double click on it and format it to have neither lines
nor markers: we'll use this for our percent labels. The easiest way to
get the labels in place is to use a third party add-in, like Rob Bovey's
Chart Labeler (http://appspro.com) or John Walkenbach's Chart Tools
(http://j-walk.com); both are free and easy to install and use. Use the
add-in to apply the labels from the %YTD to the Sum series, and choose
the label position above the points.
You're done but for specific formatting of the various chart elements.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______
This is different than the arrangement I assumed from your original
post. Do you want Target (2000) on the Y axis, or % (100%)? I am
guessing from context you want the numbers, although if the goal is the
same for all regions, it hardly matters. Also, I am guessing you want
some kind of % achieved to date.
Here's how I'd do it. Arrange the data thus:
Rgn1 Rgn2 Rgn3 Rgn4
Jan
Feb
Mar
....
Dec
Sum
Goal
%YTD
Populate the grid with the monthly numbers. The Sum row has formulas
which total the months above. The Goal row has your 2000 target. The
%YTD has the Sum divided by the Goal, as a percentage. The top left cell
is blank.
Select this entire range except for the %YTD row but including the RGNi
headers and the Month column. Make a stacked column chart with series in
rows. Select the Goal series, choose Chart Type from the Chart menu, and
choose Line, the subtype with lines but no markers. Do the same for the
Sum series, then double click on it and format it to have neither lines
nor markers: we'll use this for our percent labels. The easiest way to
get the labels in place is to use a third party add-in, like Rob Bovey's
Chart Labeler (http://appspro.com) or John Walkenbach's Chart Tools
(http://j-walk.com); both are free and easy to install and use. Use the
add-in to apply the labels from the %YTD to the Sum series, and choose
the label position above the points.
You're done but for specific formatting of the various chart elements.
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______