Managing Three Variables

[supafreak]

I was just drawing up a spreadsheet (in iWork Numbers actually, though I have Excel '08) in preparation to buy a new car and ran into a problem that I have run into but have been unable to overcome in the past. How would I chart, and then graph, MPG, gas price, and distance driven such that I come out with a value that is equal to [(GasPrice/MPG)(DistanceDriven)]?

Thanks

[davesexcel]

Attach a sample workbook

[FDibbins]

If you have all that data in a range, then just do what you said, in a 4th column, and graph that column?

A1=Gas Price
B1=MPG
C1=Distance Driven
D1=value to plot =A2/B2*C2

or am i missing something here? lol

[supafreak]

Ahhh... you made me open Excel. I made a rough, incomplete recreation of what I had in Numbers... it took five minutes just to remember how to define absolute references and even then I couldn't get the auto-complete feature to work properly (if you can figure out how to auto-complete the MPG vs price chart please tell me). Anyways, if you click on 1500 or 1650 towards the bottom you'll see the final value I want. I don't know how to chart three variables, so obviously the data is not arranged correctly.

Thanks

p.s. you guys have the most obnoxious overcomplicated file upload tool I've ever seen

[supafreak]

A1=Gas Price
B1=MPG
C1=Distance Driven
D1=value to plot =A2/B2*C2

Then I would have to write out every possible combination of MPG, Gas Price, and Distance Driven. With the 19 MPGs, 11 prices, and 6 distances I am considering, that would make for (19 x 11 x 6) = 1,254 rows of data. If you want to type it out, I'll give you the specifics... (you guys need that tongue out smiley)

I'm also not sure a graph of that would work out well.

Thanks for the reply though, I hadn't considered what you suggested and it might end up being the only way.

[supafreak]

anybody???

[supafreak]

Really no one? I didn't think this was that complicated...

[davesexcel]

try this
=INDEX($B$2:$B$20,MATCH(26,$A$2:$A$20,0),MATCH(3,$B$1:$L$1,0))*B24

[supafreak]

Thanks, that solves my problem about quickly filling in the calculations. However my original problem still exists... I would appreciate any suggestions, thanks!

[supafreak]

Still no love?

[supafreak]

Baaaaaaaaahhhhhhhhhhgggggggggggoooooooooooddddddaaaaaaaaaaaammmmmmmmmmmnnnnnnnnnnnnnn.

[shg]

I'm not sure what you need that big table for, and no idea what you want to plot ...

Please Login or Register  to view this content.

[supafreak]

Ok, now how do I make a surface graph of that? I'm trying to set up the data in such a way that I can make a surface graph with MPG on the y-axis, distance driven on x-axis, and resultant cost on the (vertical) z-axis.

[shg]

Considering that you have MPG across the top, and gas price down the side, the body of the table would be cost for (one single) number of miles driven.

For that, you can use the formula I gave you.

[supafreak]

Ok, thanks for the help.