I THOUGHT I knew what I was doing

[RJB]

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

 

[Bryan Hessey]

do you mean as per the attached? I didn't add the original $75 row, but
that should be easy enough as another series.

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 |
+-------------------------------------------------------------------+

 

[RJB]

Thanks. Love to try it. But I got an error message - "Invalid attachment
specified"

 

[rmellison]

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]

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!

 

[Debra Dalgleish]

Instead of a line chart, create an XY (Scatter) chart, and select one of
the subtypes with lines.

 

[rmellison]

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]

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