 |
 |
 |
 |
 |
|
|
||||||||||||
|
#1
09-09-2005, 07:05 PM
|
|||
|
|||
|
I THOUGHT I knew what I was doing
I am trying to build a chart that regresses two sets of gross revenue
numbers, based on anticipated price sensitivity. In other words: Right now, I move 100 pieces a month. Sales price is $75 each. So, gross rev is $7500. I want to show what will happen if I raise my price to $100, or $125, knowing that there will be falloff. I want my X axis to be units sold - left to right - 100, 90, 80, 70, 60 I want my Y axis to be total revenue, at each price point. So, I want two lines: Line 1 (100, $10,000), (90, $9000), (80, $8000), (70, $7000) and (60, $6000) Line 2 (100, $12,500), (90, 11,250), (80, $10,000), (70, $8,750), and (60, $7,500) No matter how I set my datasheets, and run the wizards, I get an X Axis of 1, 2, 3, 4, 5 And my Y axis is all over the place - the first line has the right values, and the second line is a weird combo. This is SO basic, but my brain is frozen, and it's Friday night and I want to do this and go home! Thanks 
|
|
#2
09-11-2005, 01:33 AM
|
|||
|
|||
|
do you mean as per the attached? I didn't add the original $75 row, but that should be easy enough as another series.
Quote:
|
|
#3
09-11-2005, 11:05 PM
|
|||
|
|||
|
Re: I THOUGHT I knew what I was doing
Thanks. Love to try it. But I got an error message - "Invalid attachment
specified" "Bryan Hessey" wrote: > > do you mean as per the attached? I didn't add the original $75 row, but > that should be easy enough as another series. > > > RJB Wrote: > > I am trying to build a chart that regresses two sets of gross revenue > > numbers, based on anticipated price sensitivity. > > > > In other words: > > > > Right now, I move 100 pieces a month. Sales price is $75 each. So, > > gross rev > > is $7500. > > > > I want to show what will happen if I raise my price to $100, or $125, > > knowing that there will be falloff. > > > > I want my X axis to be units sold - left to right - 100, 90, 80, 70, > > 60 > > > > I want my Y axis to be total revenue, at each price point. > > > > So, I want two lines: > > > > Line 1 > > (100, $10,000), (90, $9000), (80, $8000), (70, $7000) and (60, $6000) > > > > Line 2 > > (100, $12,500), (90, 11,250), (80, $10,000), (70, $8,750), and (60, > > $7,500) > > > > > > No matter how I set my datasheets, and run the wizards, I get an X Axis > > of > > 1, 2, 3, 4, 5 > > And my Y axis is all over the place - the first line has the right > > values, > > and the second line is a weird combo. > > > > This is SO basic, but my brain is frozen, and it's Friday night and I > > want > > to do this and go home! > > > > > > Thanks > > > +-------------------------------------------------------------------+ > |Filename: BookChart.zip | > |Download: http://www.excelforum.com/attachment.php?postid=3801 | > +-------------------------------------------------------------------+ > > -- > Bryan Hessey > ------------------------------------------------------------------------ > Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 > View this thread: http://www.excelforum.com/showthread...hreadid=466415 > >
|
|
#4
09-12-2005, 07:05 AM
|
|||
|
|||
|
RE: I THOUGHT I knew what I was doing
I am somewhat of a novice, but have been battling with line charts myself in
recent weeks so this may be of help. Select your two sets of y-values only and run the chart wizard as per usual. Choose line chart. You should get two series with categories 1 to 5 along the bottom, as you do at the moment. Your y-axis should look ok. Right click the chart, choose source data and edit the category(x-axis) labels box at the bottom of the window. Select a range of cells to represent your x-values, 100,90,80,70,60 etc. You may also wish to Format Axis | Scale and deselect 'Value (Y) axis crosses between categories'. HTH "RJB" wrote: > I am trying to build a chart that regresses two sets of gross revenue > numbers, based on anticipated price sensitivity. > > In other words: > > Right now, I move 100 pieces a month. Sales price is $75 each. So, gross rev > is $7500. > > I want to show what will happen if I raise my price to $100, or $125, > knowing that there will be falloff. > > I want my X axis to be units sold - left to right - 100, 90, 80, 70, 60 > > I want my Y axis to be total revenue, at each price point. > > So, I want two lines: > > Line 1 > (100, $10,000), (90, $9000), (80, $8000), (70, $7000) and (60, $6000) > > Line 2 > (100, $12,500), (90, 11,250), (80, $10,000), (70, $8,750), and (60, $7,500) > > > No matter how I set my datasheets, and run the wizards, I get an X Axis of > 1, 2, 3, 4, 5 > And my Y axis is all over the place - the first line has the right values, > and the second line is a weird combo. > > This is SO basic, but my brain is frozen, and it's Friday night and I want > to do this and go home! > > > Thanks > >
|
|
#5
09-12-2005, 11:05 AM
|
|||
|
|||
|
RE: I THOUGHT I knew what I was doing
Thanks. I thought I licked this like 15 years ago on my Mac... But still
having same problems. I tried what you suggested. What is fouling me up is that when I do THAT, Excel treats each data point as if it is the same "distance" on the X-axis as everything else. Let me try to explain: Let's say my graph is six inches long.(!) I have six data points, which are: 10, 20, 30, 40, 50, 60 So, on the graph, you would expect each point on the X-axis to be one inch apart. But let's say I have 10, 20, 30, 50, 55, 60. You would still expect the graph to be six inches long, and you would expect 10 to be one inch from 20, 20 should be one inch from 30, 30 should be TWO INCHES from 50... 55 should be HALF AN INCH from 50, and HALF an inch from 60. But what's happening with my graph is it is still treating those six data points as "equal". So each point is one inch apart. Which gives me a wacky slope, and certainly doesn't show what I want!!! In high school, long before we had personal computers, I could have drawn this on graph paper in less than half the time it has taken me to type this post! Argh! "rmellison" wrote: > I am somewhat of a novice, but have been battling with line charts myself in > recent weeks so this may be of help. > > Select your two sets of y-values only and run the chart wizard as per usual. > Choose line chart. You should get two series with categories 1 to 5 along the > bottom, as you do at the moment. Your y-axis should look ok. > > Right click the chart, choose source data and edit the category(x-axis) > labels box at the bottom of the window. Select a range of cells to represent > your x-values, 100,90,80,70,60 etc. You may also wish to Format Axis | Scale > and deselect 'Value (Y) axis crosses between categories'. > > HTH > > "RJB" wrote: > > > I am trying to build a chart that regresses two sets of gross revenue > > numbers, based on anticipated price sensitivity. > > > > In other words: > > > > Right now, I move 100 pieces a month. Sales price is $75 each. So, gross rev > > is $7500. > > > > I want to show what will happen if I raise my price to $100, or $125, > > knowing that there will be falloff. > > > > I want my X axis to be units sold - left to right - 100, 90, 80, 70, 60 > > > > I want my Y axis to be total revenue, at each price point. > > > > So, I want two lines: > > > > Line 1 > > (100, $10,000), (90, $9000), (80, $8000), (70, $7000) and (60, $6000) > > > > Line 2 > > (100, $12,500), (90, 11,250), (80, $10,000), (70, $8,750), and (60, $7,500) > > > > > > No matter how I set my datasheets, and run the wizards, I get an X Axis of > > 1, 2, 3, 4, 5 > > And my Y axis is all over the place - the first line has the right values, > > and the second line is a weird combo. > > > > This is SO basic, but my brain is frozen, and it's Friday night and I want > > to do this and go home! > > > > > > Thanks > > > >
|
|
#6
09-12-2005, 11:05 AM
|
|||
|
|||
|
Re: I THOUGHT I knew what I was doing
Instead of a line chart, create an XY (Scatter) chart, and select one of
the subtypes with lines. RJB wrote: > Thanks. I thought I licked this like 15 years ago on my Mac... But still > having same problems. > > I tried what you suggested. What is fouling me up is that when I do THAT, > Excel treats each data point as if it is the same "distance" on the X-axis as > everything else. > > Let me try to explain: > > Let's say my graph is six inches long.(!) I have six data points, which are: > 10, 20, 30, 40, 50, 60 > > So, on the graph, you would expect each point on the X-axis to be one inch > apart. > > But let's say I have 10, 20, 30, 50, 55, 60. > > You would still expect the graph to be six inches long, and you would expect > 10 to be one inch from 20, 20 should be one inch from 30, 30 should be TWO > INCHES from 50... 55 should be HALF AN INCH from 50, and HALF an inch from 60. > > But what's happening with my graph is it is still treating those six data > points as "equal". So each point is one inch apart. Which gives me a wacky > slope, and certainly doesn't show what I want!!! > > In high school, long before we had personal computers, I could have drawn > this on graph paper in less than half the time it has taken me to type this > post! > > Argh! > > "rmellison" wrote: > > >>I am somewhat of a novice, but have been battling with line charts myself in >>recent weeks so this may be of help. >> >>Select your two sets of y-values only and run the chart wizard as per usual. >>Choose line chart. You should get two series with categories 1 to 5 along the >>bottom, as you do at the moment. Your y-axis should look ok. >> >>Right click the chart, choose source data and edit the category(x-axis) >>labels box at the bottom of the window. Select a range of cells to represent >>your x-values, 100,90,80,70,60 etc. You may also wish to Format Axis | Scale >>and deselect 'Value (Y) axis crosses between categories'. >> >>HTH >> >>"RJB" wrote: >> >> >>>I am trying to build a chart that regresses two sets of gross revenue >>>numbers, based on anticipated price sensitivity. >>> >>>In other words: >>> >>>Right now, I move 100 pieces a month. Sales price is $75 each. So, gross rev >>>is $7500. >>> >>>I want to show what will happen if I raise my price to $100, or $125, >>>knowing that there will be falloff. >>> >>>I want my X axis to be units sold - left to right - 100, 90, 80, 70, 60 >>> >>>I want my Y axis to be total revenue, at each price point. >>> >>>So, I want two lines: >>> >>>Line 1 >>>(100, $10,000), (90, $9000), (80, $8000), (70, $7000) and (60, $6000) >>> >>>Line 2 >>>(100, $12,500), (90, 11,250), (80, $10,000), (70, $8,750), and (60, $7,500) >>> >>> >>>No matter how I set my datasheets, and run the wizards, I get an X Axis of >>>1, 2, 3, 4, 5 >>>And my Y axis is all over the place - the first line has the right values, >>>and the second line is a weird combo. >>> >>>This is SO basic, but my brain is frozen, and it's Friday night and I want >>>to do this and go home! >>> >>> >>>Thanks >>> >>> >> -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html
|
|
#7
09-12-2005, 11:05 AM
|
|||
|
|||
|
RE: I THOUGHT I knew what I was doing
The problem your having sounds eerily familiar! I've been having all sorts of
issues with line chart axes and what-not! The problem you're experiencing is basically due to the fact that a line chart plots the x-axis as categories rather than numerical values. The sort of thing you may want to use one for is if you were plotting sales against days of the week perhaps, so that your x-axis is mon,tues,wed etc. If you have numerical values on the x-axis, a line chart will still plot them as categories, so that each value in the range of cells is plotted as its own category, equally spaced from adjacent categories. The easiest way around this is to plot your data as an XY scatter graph. If you have your x-values (units sold) 100,90,75,60,30 in A1:A5, then your revenue based on unit cost of $100 in B1:B5, and based on say,$75 in C1:C5, just select all three columns, run the wizard and choose XY scatter | sub-type 'lines without markers'. This should give you the desired line plots. If you want to show the number of units sold decreasing, select format x-axis, scale tab, and check the box 'values in reverse order'. You may then need to play around with a secondary y-axis and hide the other one if you want the y-axis back on the left hand side. (Select one of the lines, right click, format data series, axis tab, check secondary axis. Make sure the scale is the same for both then clear the right hand axis.) Hope that works for you.... "RJB" wrote: > Thanks. I thought I licked this like 15 years ago on my Mac... But still > having same problems. > > I tried what you suggested. What is fouling me up is that when I do THAT, > Excel treats each data point as if it is the same "distance" on the X-axis as > everything else. > > Let me try to explain: > > Let's say my graph is six inches long.(!) I have six data points, which are: > 10, 20, 30, 40, 50, 60 > > So, on the graph, you would expect each point on the X-axis to be one inch > apart. > > But let's say I have 10, 20, 30, 50, 55, 60. > > You would still expect the graph to be six inches long, and you would expect > 10 to be one inch from 20, 20 should be one inch from 30, 30 should be TWO > INCHES from 50... 55 should be HALF AN INCH from 50, and HALF an inch from 60. > > But what's happening with my graph is it is still treating those six data > points as "equal". So each point is one inch apart. Which gives me a wacky > slope, and certainly doesn't show what I want!!! > > In high school, long before we had personal computers, I could have drawn > this on graph paper in less than half the time it has taken me to type this > post! > > Argh! > > "rmellison" wrote: > > > I am somewhat of a novice, but have been battling with line charts myself in > > recent weeks so this may be of help. > > > > Select your two sets of y-values only and run the chart wizard as per usual. > > Choose line chart. You should get two series with categories 1 to 5 along the > > bottom, as you do at the moment. Your y-axis should look ok. > > > > Right click the chart, choose source data and edit the category(x-axis) > > labels box at the bottom of the window. Select a range of cells to represent > > your x-values, 100,90,80,70,60 etc. You may also wish to Format Axis | Scale > > and deselect 'Value (Y) axis crosses between categories'. > > > > HTH > > > > "RJB" wrote: > > > > > I am trying to build a chart that regresses two sets of gross revenue > > > numbers, based on anticipated price sensitivity. > > > > > > In other words: > > > > > > Right now, I move 100 pieces a month. Sales price is $75 each. So, gross rev > > > is $7500. > > > > > > I want to show what will happen if I raise my price to $100, or $125, > > > knowing that there will be falloff. > > > > > > I want my X axis to be units sold - left to right - 100, 90, 80, 70, 60 > > > > > > I want my Y axis to be total revenue, at each price point. > > > > > > So, I want two lines: > > > > > > Line 1 > > > (100, $10,000), (90, $9000), (80, $8000), (70, $7000) and (60, $6000) > > > > > > Line 2 > > > (100, $12,500), (90, 11,250), (80, $10,000), (70, $8,750), and (60, $7,500) > > > > > > > > > No matter how I set my datasheets, and run the wizards, I get an X Axis of > > > 1, 2, 3, 4, 5 > > > And my Y axis is all over the place - the first line has the right values, > > > and the second line is a weird combo. > > > > > > This is SO basic, but my brain is frozen, and it's Friday night and I want > > > to do this and go home! > > > > > > > > > Thanks > > > > > >
|
|
#8
09-12-2005, 05:05 PM
|
|||
|
|||
|
RE: I THOUGHT I knew what I was doing
That is EXACTLY what I needed - the "Scatter" instead of the "Line". Duh!
Thanks, both of you! Love the 12-second solution after screaming all day Friday! rjb "rmellison" wrote: > The problem your having sounds eerily familiar! I've been having all sorts of > issues with line chart axes and what-not! > > The problem you're experiencing is basically due to the fact that a line > chart plots the x-axis as categories rather than numerical values. The sort > of thing you may want to use one for is if you were plotting sales against > days of the week perhaps, so that your x-axis is mon,tues,wed etc. If you > have numerical values on the x-axis, a line chart will still plot them as > categories, so that each value in the range of cells is plotted as its own > category, equally spaced from adjacent categories. > > The easiest way around this is to plot your data as an XY scatter graph. If > you have your x-values (units sold) 100,90,75,60,30 in A1:A5, then your > revenue based on unit cost of $100 in B1:B5, and based on say,$75 in C1:C5, > just select all three columns, run the wizard and choose XY scatter | > sub-type 'lines without markers'. This should give you the desired line plots. > > If you want to show the number of units sold decreasing, select format > x-axis, scale tab, and check the box 'values in reverse order'. You may then > need to play around with a secondary y-axis and hide the other one if you > want the y-axis back on the left hand side. (Select one of the lines, right > click, format data series, axis tab, check secondary axis. Make sure the > scale is the same for both then clear the right hand axis.) > > Hope that works for you.... > > > > > "RJB" wrote: > > > Thanks. I thought I licked this like 15 years ago on my Mac... But still > > having same problems. > > > > I tried what you suggested. What is fouling me up is that when I do THAT, > > Excel treats each data point as if it is the same "distance" on the X-axis as > > everything else. > > > > Let me try to explain: > > > > Let's say my graph is six inches long.(!) I have six data points, which are: > > 10, 20, 30, 40, 50, 60 > > > > So, on the graph, you would expect each point on the X-axis to be one inch > > apart. > > > > But let's say I have 10, 20, 30, 50, 55, 60. > > > > You would still expect the graph to be six inches long, and you would expect > > 10 to be one inch from 20, 20 should be one inch from 30, 30 should be TWO > > INCHES from 50... 55 should be HALF AN INCH from 50, and HALF an inch from 60. > > > > But what's happening with my graph is it is still treating those six data > > points as "equal". So each point is one inch apart. Which gives me a wacky > > slope, and certainly doesn't show what I want!!! > > > > In high school, long before we had personal computers, I could have drawn > > this on graph paper in less than half the time it has taken me to type this > > post! > > > > Argh! > > > > "rmellison" wrote: > > > > > I am somewhat of a novice, but have been battling with line charts myself in > > > recent weeks so this may be of help. > > > > > > Select your two sets of y-values only and run the chart wizard as per usual. > > > Choose line chart. You should get two series with categories 1 to 5 along the > > > bottom, as you do at the moment. Your y-axis should look ok. > > > > > > Right click the chart, choose source data and edit the category(x-axis) > > > labels box at the bottom of the window. Select a range of cells to represent > > > your x-values, 100,90,80,70,60 etc. You may also wish to Format Axis | Scale > > > and deselect 'Value (Y) axis crosses between categories'. > > > > > > HTH > > > > > > "RJB" wrote: > > > > > > > I am trying to build a chart that regresses two sets of gross revenue > > > > numbers, based on anticipated price sensitivity. > > > > > > > > In other words: > > > > > > > > Right now, I move 100 pieces a month. Sales price is $75 each. So, gross rev > > > > is $7500. > > > > > > > > I want to show what will happen if I raise my price to $100, or $125, > > > > knowing that there will be falloff. > > > > > > > > I want my X axis to be units sold - left to right - 100, 90, 80, 70, 60 > > > > > > > > I want my Y axis to be total revenue, at each price point. > > > > > > > > So, I want two lines: > > > > > > > > Line 1 > > > > (100, $10,000), (90, $9000), (80, $8000), (70, $7000) and (60, $6000) > > > > > > > > Line 2 > > > > (100, $12,500), (90, 11,250), (80, $10,000), (70, $8,750), and (60, $7,500) > > > > > > > > > > > > No matter how I set my datasheets, and run the wizards, I get an X Axis of > > > > 1, 2, 3, 4, 5 > > > > And my Y axis is all over the place - the first line has the right values, > > > > and the second line is a weird combo. > > > > > > > > This is SO basic, but my brain is frozen, and it's Friday night and I want > > > > to do this and go home! > > > > > > > > > > > > Thanks > > > > > > > >
|
 |
| Bookmarks |
New topics in Excel Charting
|
|
|
| Currently Active Users Viewing This Thread: 1 (0 members and 1 guests) | |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
