Need help graphing an equation

[NPeek]

Can Excel graph the following equation? If so, how would I go about
doing this?

z=((.03y+.15)x+150.75)x

Basically, I have a different cost formula for each of 4 different
suppliers. All have a different twist, but all contain the same 2
variables (x and y) and result in a single value (z). I need to be
able to graph the equations so that I can visually present where the
costs intersect.

 

[Bernard Liengme]

What you have showed us is an equation for a surface. There would be no
intersection.
Try to give us the actual problem without assuming the z-function.
For example, you might have an equations
y1 = 2x+ 5 for manufacturing cost where x is products made, and
y2 = 3x-6 for profit where x is units sold.
A plot of these would cross at x = 11
But note we would plot TWO functions not ONE

best wishes

 

[David Biddulph]

I think the OP was trying to suggest that there were 4 different z
functions.

Unfortunately, it is unlikely that drawing surface plots in Excel will
easily give a clear view of the lines of intersection of the surfaces. I
normally avoid surface plots like the plague; is it even possible to draw
more than one surface on the same plot?

 

[Bernard Liengme]

Maybe we should point him/her towards Solver.
best wsihes

 

[Jon Peltier]

is it even possible to draw more than one surface on the same plot?

No

- Jon

 

[Del Cotter]

Can Excel graph the following equation? If so, how would I go about
doing this?

z=((.03y+.15)x+150.75)x

Think how you'd go about doing it with a calculator, a pen and a sheet
of grid paper. You'd choose some values of x and y, calculate z, and
mark the calculated z positions on the grid paper. Then you'd join the
points up with lines.

For efficiency, you might do all the calculating first, writing the
numbers in a table, then use the table values to plot the points on the
grid paper.

Doing it with a spreadsheet is just the same process. Make the table
using the spreadsheet cells, then use the table to make the chart.

The formula for your z column in this case will be

= ( ( 0.03*Y1 + 0.15 ) * X1 + 150.75 ) * X1

where "X1" and "Y1" are cell references.

Basically, I have a different cost formula for each of 4 different
suppliers. All have a different twist, but all contain the same 2
variables (x and y) and result in a single value (z). I need to be
able to graph the equations so that I can visually present where the
costs intersect.

Four cost formulae means four z columns, which is simple enough. The
search for an intersect between two lines is trickier, because each of
your equations is one equation in three unknowns (x, y and z), making
them equations of *surfaces*, not lines. That means they won't have
intersection points, but intersection lines.

If you had only two different suppliers to compare, I'd suggest graphing
four lines of z against x for four values of y, for each of two
suppliers, and joining the four points of intersection to show the line.

But the intersection line will have variable y, making it hard to
interpret on a graph of z against x. Plus, you'll need six different
graphs to compare every pairwise combination of four suppliers.

Have you considered using algebra instead of graphing for the first
step? Then you can plot the six lines as x against y on a single graph,
neglecting z, which I assume you don't care so much about except that
it's what's equal at the crossover point. If you really care about z,
you can label the points, but then I'd use six graphs, not one, to avoid
cluttering the space up.

 

[Del Cotter]

No

Not on the same Excel Surface Chart type, but it's easy on the
ever-versatile Scatter Chart. Here's the equation plotted for one
surface in the x-z plane:

http://www.branta.demon.co.uk/excel/eqsurf.xls

I wouldn't normally show the y=1, y=2 lines etc., as they're basically
vertical gridlines, but I thought they gave the surface a pleasantly
net-like quality. Adding a second surface is trivial, and adding the
line of intersection between two surfaces is a matter of calculation.

With a bit more calculation it should be possible to arrange to show
only the surface that is highest (or lowest) in a given region, to give
a "phase diagram" of lowest price suppliers for a given combination of x
and y. In other words it would be a surface graph of lowest z, plotted
on the x-y plane.

 

[Jon Peltier]

I've used this approach before, and in fact, for most purposes it is as
"valid" as using a surface chart. Many times the different dimensions are
not physical dimensions, and often they are not continuous but discrete, so
using separate lines on a line or XY chart is completely appropriate, and
has the benefit of being easier to read.