How to graph a fixed line vs individual days data in Excel

GNOVAK
GNOVAK used Ask the Experts™
on
Two Questions based on the same concepts:

I have a chart in excel 2007 that graphs days of the month on the X axis and Cumlative daily Percentages on the Y axis.  It is line graph.
The Y axis is entered based on daily results (End of Day). I calculate what % increase there has been since the begining of the month.
Each month I have a target percentage (20%) that is to be reached by the end of the month. This is obtained from a cell on another worksheet
I would like to place a line starting @ 0,0 that goes diagonally to the target percentage by the end of the month.
So as the month goes along, I can visually see if I'm tracking above or below for a given day based on a theoretical straight line.
Can this be done?


Another chart is a bar chart that graphs Dollars per day, again input at the end of the day. On another Worksheet I have the ending dollar target for the month. How do I add a bar with a reference to another sheet that is accurate for each day?  i.e.  If the dollar amount for the month is $310 and there are 31 days, the first day would be $10, the second $20, The third $30....

I'm ok with VBA if it needs to be done that way.  I'm used to Access and VBA there.

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Expert of the Quarter 2010
Expert of the Year 2010

Commented:
Are you able to set up a sample workbook with the bits in place and a graph, so that we can add the line in there?
Most Valuable Expert 2011
Awarded 2010
Commented:
Hello GNOVAK

for the first question: add a data series to your chart. The series should have as many cells as there are days in your chart. Set the first cell to 0, leave the middle cells blank and set the last cell to your target value. You can do that by simply using

=Sheet2!A1

or where ever your target data is. Specify in your chart settings that empty cells should be plotted and connected with a line. This way, you'll see a line from day1 at zero to day30 at target.

I don't really understand your second question. As Cyberkiwi has suggested, post some data to work with. If need be, draw the chart with the drawing tools to illustrate what you want.

cheers, teylyn

Author

Commented:
The enclosed shows two tabs - one for Projections, one for an individual month.  The normal sheet has multiple months as tabs.
I would like to be able to have excel draw the lines that I have hand drawn on the two graphs which are dependent on the Y axis scale.
The Y axis max scale will be dependent on cells from Projections tab - see notes above graphs
Book1.xls
Most Valuable Expert 2011
Awarded 2010

Commented:
See attached.

As described above, the line is created with a data series that starts with a zero and has the target value in the last cell of the range.

I had to change the column chart to a 2D chart, since you can not combine a line chart with a 3d chart. 3D charts never add any value over 2D charts, they are just more difficult to decipher.


cheers, teylyn
Copy-of-Book1-1.xls

Author

Commented:
That looks good- just a question though.

I'm thinking of eventually distributing this and would rather not have additional columns - I would have to hide them and lock them down.  All the sheets I've done in the past were for me or one other person that understood what to do and not do :-)    Dont know if hiding and locking are just standard when give out to someone or not.  If it has to be, it has to be...just wondering.
Is there any way to get those values without creating an extra column for the line?  Could it be referenced directly somehow from the projections cell?
Most Valuable Expert 2011
Awarded 2010
Commented:
Hello GNOVAK,

The underlying data for the line has to be stored somewhere. I can understand that you don't want to use hidden columns. What you can do, though, is put the data for the lines on a separate helper sheet. Just make sure you have exactly the number of cells between the 0 and the last point, then you don't even need to replicate the X axis data on the helper sheet.

Once the line is in place in the chart, you can hide the helper sheet. This will be much harder for the average user to find, undhide and muck about with. The hidden sheet can also be protected before hiding, so they will have to try really hard if they want to foul things up.

see attached.

cheers, teylyn
Book2.xls

Author

Commented:
Thanks - I think I'll use that suggestion. I can create one sheet that has the months as columns and utilizes the same number of rows for the month.

Author

Commented:
Extremely helpful - thanks so much
Most Valuable Expert 2011
Awarded 2010

Commented:
Thanks for the grade!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial