excel chart: plot high and low data only

[raycom]

Hi,

I have a 46000 rows of data. I want to make a chart, but only want to plot the highest and lowest data points in the data set. How do I get Excel to automatically find and plot only the highest and lowest data points?

Thanks,

dave

 

[raycom]

Make another column with (D) where D1 =

=IF(C1="yes",B1,#N/A)

Plot column A on the X-axis, plot column D on the Y-axis.

Excel won't plot #N/A errors and will skip the ones with 'no'.


Edit: Re-read. This is a solution áfter you found a way to find the 'highs' and the 'lows' of course.

It's finding the highs and lows that is time-consuming. I don't yet have a yes/no column in my 45000 lines of data!

 

[Andrew Poulsom]

Assuming your data is in A:B, starting in row 2, in C2 enter:

=OR(ISTEXT(B1),AND(B2>B1,B2>B3),AND(B2 < B1,B2 < B3))


and copy down. Now AutoFilter column C for TRUE and your chart should plot only the visible cells (check the setting under Tools|Options|Chart).

 

[dafan]

It's finding the highs and lows that is time-consuming. I don't yet have a yes/no column in my 45000 lines of data!

This should get you started:
http://www.mrexcel.com/forum/showthread.php?t=58655&highlight=peak+trough

 

[raycom]

Assuming your data is in A:B, starting in row 2, in C2 enter:

=OR(ISTEXT(B1),AND(B2>B1,B2>B3),AND(B2 < B1,B2 < B3))


and copy down. Now AutoFilter column C for TRUE and your chart should plot only the visible cells (check the setting under Tools|Options|Chart).

Andrew,

That formula worked and filtered out all the false rows .....

But when I selected the filtered range for my chart I got this message:

"Microsoft Office Excel cannot create or use the data reference because it is too complex. Try one or more of the following:

* Use data that can be selected in one contiguous rectangle
* Use data from the same sheet"

All my data is from one sheet so I guess Excel doesn't like the amount of it I have. I didn't get the 32000 row limit error message though! I tried selecting less than 10000 rows and it didn't help.

Is there a way of automatically deleting the rows of data containing the "False" cells?

Thanks, Dave

 

[Andrew Poulsom]

Try copying the visible cells to another sheet (in chunks if necessary) and plotting that.

 

[raycom]

Try copying the visible cells to another sheet (in chunks if necessary) and plotting that.

Okay, I finally managed it. I had to break the data into smaller chunks to copy it to a new sheet due to the complexity message cropping up again, but i finally got there.

45000 lines of data is reduced to 18000 lines. That's makes it just about manageable I hope.

Thanks for all your help.

Dave

 

[tusharm]

Use an "advanced filter" to copy the records of interest to another range on the same worksheet. I don't know if there is some limitation on an advanced filter and 40,000+ records but if it works it will be a lot easier to implement.

Okay, I finally managed it. I had to break the data into smaller chunks to copy it to a new sheet due to the complexity message cropping up again, but i finally got there.

45000 lines of data is reduced to 18000 lines. That's makes it just about manageable I hope.

Thanks for all your help.

Dave