Help charting peak values when the X-axis values are not the same

Hi all,

This is my first post on this forum. I've been using Excel 2003 for the past few years and I have been stumped on this problem for the past couple of years.

I have a spreadsheet I made for a vehicle application -- you enter dynamometer measurement information in RPM and torque, then the gear ratios, vehicle weight, tire size, etc and it will generate a series of scatter plots showing the full-throttle acceleration available through the vehicle speed range (basically translates the RPM domain to the MPH domain) as well as a few other things. I've spent a lot of time making it complete and nice looking.

The end result is a graph like the one titled "erics" that I attached. The one titled "c160" is another graph generated that compares two different motors with different transmission ratios.

The wheel torque (proportional to acceleration) is plotted in one line for each gear ratio.

I want to make a one-line plot of just the PEAK values -- in other words, I want it to plot 1st gear until the 1st gear line crosses the 2nd gear line (indicating that 2nd gear now produces more torque than first because the engine's power is dropping off) and then I want it to plot the 2nd gear line until that crosses the third gear line, etc.

I've been trying to figure out ways to do this literally for years. The problem as far as I can tell is that the X-axis indices are not constant -- the MPH values associated with each torque or acceleration value are not the same from gear to gear.

In college they never taught us Excel (we were taught Matlab, but I wanted this sheet to be usable by anyone) so i don't know much about macros and VBA and what not. If anyone can suggest something, please do -- even if you just know the proper tool to solve the problem, just let me know what it is and I'll take it from there. Any help is appreciated! Thanks!!
-Ben

PS: Sure is a hassle taking pictures out of Excel graphs, I end up saving it as an html document and stealing the linked gif images that are generated.

Thanks for the replies.

I think the intersection function will work.

I've attached a filled-out example spreadsheet. For clarification, if you look at one of the graphs titled "wheel torque" I'm basically trying to generate one line to include all the peak values at any given speed. I'm going to try the intersection function once I get some time over the holiday weekend. I don't think manipulating the data is a good solution, I'm a newbie to VBA but it sounds like it would be a huge hassle. I've tried working it out a couple of times and couldn't find a way to do it.

I made the spreadsheet several years ago but I got bored recently and decided to build a new version of it to require fewer operator changes to do what it is already doing. This brings me to another question: when plotting a data series on a scatter plot, how do I get it to not plot any value that is not entered? Currently the spreadsheet takes one set of values and plots it and then derives a second set of values as a function of the first and plots that one too. But the number of values in the input set varies, and the plot charts a set range of cells that, if the cells they take their data from are not filled in, show up as zeros in the graph.

For example, if you pull the formula down in the "Power" column of the first two sheets, look at the "Dyno" graph for the corresponding engine -- the red line will go to zero and look sloppy. I have to manually erase every cell that does not have a corresponding input (torque) value, even though I've used an IF statement to cause its output to be "" when there is no input value. I'm trying to make this as easy as input (torque values, gear ratios, tire dia., vehicle mass, redline) and output (graphs formatted correctly without any data formatting or manipulation) because I want it to be usable by others who are not excel nerds like me. :p

Sorry if that sounds confusing... I can clarify if needed... and thanks again for the help!!

Ben

Originally Posted by bcol

I've attached a filled-out example spreadsheet.
Ben

Have you?
(now it tells me the message is too short to post, hope this sentence will sort that)

Wow, that's strange, hopefully it will work this time. Sorry and thanks for your patience. :p