How to graph a fixed line vs individual days data in Excel
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.
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.
cyberkiwi
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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
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
ASKER
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?
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?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
ASKER
Extremely helpful - thanks so much
Thanks for the grade!