Separating data from a column based on increasing or decreasing setpoints (for hysteresis)

I'm running a test through a number of temperature set points repeatedly and need to separate the data of the rising temperature segments from the falling temperatures so I can analyse the hysteresis.

I've worked out a function that allows me to do this (somewhat inelegantly) but it seems to be quite memory intensive and often results in excel crashing or getting hung up for extended periods of time on larger files (they can sometimes be up to 100-200k rows). It also cuts out the 1st and last blocks of data (but I can live with this if I have to).

I've attached a part of a data set I'm using and included the function I worked out:

=IF($C5<INDEX($C$5:$C$21679,ROWS($C$5:C5)+$E$2*($F$2*$G$2+1)),INDEX($C$5:$C$21679,ROWS($C$5:C5)+$E$2*($F$2*$G$2+1)),"-")


There has to be a better way to do this (more efficient function or a macro). I'd really appreciate any help on this.

Hi

These seemed to match your output up to the point of the #REF! error. These functions kept calculating.

E5: =IF(C5<OFFSET($C$1,ROW()-1+$E$2*($F$2*$G$2+1),0),OFFSET($C$1,ROW()-1+$E$2*($F$2*$G$2+1),0),"-")
G5: =IF(C5>OFFSET($C$1,ROW()-1+$E$2*($F$2*$G$2+1),0),OFFSET($C$1,ROW()-1+$E$2*($F$2*$G$2+1),0),"-")

I'm not sure I follow what you mean by cutting out first and last data blocks. If these functions work, then can you explain that part in more detail. Explain what should occur, where and why.

rylo

Hi rylo,

Thank you so much! That works great and makes me think of ways to improve on other parts of my workbook! (I have to admit that I completely forgot about the offset function)

What I meant about the data blocks being cut off was that the data from the first set point doesn't get sorted into either column but, after I posted it, I put more thought into it an that's quite alright. As for the last set point data, one of my first tries was doing the same to that data, but I see now that it was being sorted in my last version and in the function you provided as well.

Thank you again!

aarho

Actually, now I have a follow up question:

Would there be a way to sort the data in the same way but have the data laid out continuously (stitched together without the "-" for false statements)?

Hi

what do you mean by "laid out continuously"? Output all in the one column instead of across 2?

rylo

I mean without the gaps in data.

Basically something like:
If the function returns false, then move on to the next cell until you hit a true return, then continue on from there.

Ultimately, what I want to do is plot the two columns in the same chart, one on top of the other. So even a way to plot that ignores unwanted data points (the false returns)

Hi

Try using the na() function instead of the -. Yes it does produce the #N/A errors, but a graph doesn't pick them up - try it and see how it goes.

rylo

It's a step, but not quite qhat I was hoping for. I've attached an image of what I was trying to accomplish:

Hysteresis chart.png

I've managed to do it by making a small macro that autofilters the data and removes all zeros (I changed the "-" to 0). I'm not sure this is the bast way to do it, but it seems to be working so far.

Hi

About the only way I can think of to do that would be to have 2 x axes (yes that can be done), and have the data ranges offset - so one range would be say A1:A20, and the second one A9:A28. Don't even know if that would work or not, but give it a go and see.

rylo