Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
Baseline values for comparison
Hi All,
I am new to excel, so please bare with me. I have a chart that displays treatment values collected for each month. The treatment value is the Y Axis, and the dates for a specific month is the X axis. Question: I have a budgeted value for each month that is set at the begining of the year and stored in my workbook. What I want to do is display the Budgeted value as a horizontal line, whereby the user can view where the actual value in comparison to the budgeted value. I have been able to plot the budgeted value as a single point, but cannot seem to get it to display as solid bar on the graph. Any ideas? -- Carlee |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
Baseline values for comparison
create a helper column with that value in it for the number of points you
need and add that to the chart as a set of data -- John MOS Master Instructor Office 2000, 2002 & 2003 Please reply & rate any replies you get Ice Hockey rules (especially the Wightlink Raiders) "Carlee" wrote: Hi All, I am new to excel, so please bare with me. I have a chart that displays treatment values collected for each month. The treatment value is the Y Axis, and the dates for a specific month is the X axis. Question: I have a budgeted value for each month that is set at the begining of the year and stored in my workbook. What I want to do is display the Budgeted value as a horizontal line, whereby the user can view where the actual value in comparison to the budgeted value. I have been able to plot the budgeted value as a single point, but cannot seem to get it to display as solid bar on the graph. Any ideas? -- Carlee |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
Baseline values for comparison
Hi there,
What is a 'helper column', and how would i go about achieving this? I need to show the baseline value as a horizontal bar, not a veritical one. Could you assist a bit further? -- Carlee "John" wrote: create a helper column with that value in it for the number of points you need and add that to the chart as a set of data -- John MOS Master Instructor Office 2000, 2002 & 2003 Please reply & rate any replies you get Ice Hockey rules (especially the Wightlink Raiders) "Carlee" wrote: Hi All, I am new to excel, so please bare with me. I have a chart that displays treatment values collected for each month. The treatment value is the Y Axis, and the dates for a specific month is the X axis. Question: I have a budgeted value for each month that is set at the begining of the year and stored in my workbook. What I want to do is display the Budgeted value as a horizontal line, whereby the user can view where the actual value in comparison to the budgeted value. I have been able to plot the budgeted value as a single point, but cannot seem to get it to display as solid bar on the graph. Any ideas? -- Carlee |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
Baseline values for comparison
On Sun, 15 Apr 2007, in microsoft.public.excel.charting,
Carlee said: What is a 'helper column', and how would i go about achieving this? Basically, when most people want to use an Excel spreadsheet for graphing, they expect to be able to type their data into a minimum number of cells and then go and make a chart from it. Any translation from data to chart, they expect to do using the chart application. It usually doesn't work like that, bu fortunately, the tiny number of cells used still leaves an enormous number of cells available to turn the data into something that the graphing utility can use. The range of spreadsheet cells filled with formulas that turn your data into a graphable range is called, in Excel chart users' jargon, a "helper" range, because it helps the data to be graphed. I need to show the baseline value as a horizontal bar, not a veritical one. You need at least two X values and two Y values to achieve this (if you make the line an "XY (Scatter)" chart type), or as many Y values as there are values in the X range (if you make the line a "Line" or "Column" chart type. I assume you have chosen a line chart for your basic treatment data, and so we'll stick to that for the budget line. Here's what your treatment range probably looks like: Treatment 1 10 2 12 3 10 4 12 5 10 6 12 7 10 8 12 9 10 Let's say your budget is 11, so you probably typed a single "11" in a column next to the treatment data: Treatment Budget 1 10 11 2 12 3 10 4 12 5 10 6 12 7 10 8 12 9 10 This did you no good, because, being only one value, it only showed up as a single dot on the graph. What you should have is something like this: Treatment Budget 1 10 =$A$1 2 12 =$A$1 3 10 =$A$1 4 12 =$A$1 5 10 =$A$1 6 12 =$A$1 7 10 =$A$1 8 12 =$A$1 9 10 =$A$1 ....and then put "11" into the cell A1 (just an example, choose a cell that suits you). All the cells in the range will refer to A1 and have the value you choose for the budget. If the budget changes, you just type a new value in, and all the formula cells will change their value to match. Now, because the helper range is as long as the data range, it runs in a single horizontal line all the way across the chart. You may be annoyed that the line is thin, and punctuated with symbols all the way along. You can fix that by right clicking on the line, selecting "Format Series", and changing the Markers to "None" and the Line to "Thick". Then you should be set. -- Del Cotter NB Personal replies to this post will send email to , which goes to a spam folder-- please send your email to del3 instead. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Baseline | Charts and Charting in Excel | |||
VLOOKUP using another table for comparison values | Excel Worksheet Functions | |||
Looking up value that has two comparison values | Excel Discussion (Misc queries) | |||
Can I add a baseline value to a chart? | Charts and Charting in Excel | |||
How do I lookup data with two comparison values? | Excel Worksheet Functions |