I am working on a large data set that needs to be filtered based on the value of the previous two rows. The goal is: if the previous two rows are <=1.15 then return current row. So the following data round get filtered down to 1.88. I haven't been able to find any guide that digs in the weeds of how to accomplish this. Thanks!

Multiplier 2.8 6.55 1.1 1.06 1.88 1.89 2.36 8.23

CodePudding user response：

Create three extra columns.

Cell B1's formula is just =A2 (and populate it down). Cell C1's formula is similarly =B2. So these columns are just putting three adjacent cells into a convenient row.

Then fill in formula as shown in screenshot in cell D1. No need to 'pull' D1 down. The filter function automatically populates down and removes blanks.

I edited the data a bit just to get two results out of it, not only one.

CodePudding user response：

Here'a a more "manual" and "once-off" way of approaching this. How I'd do it is use the next column to filter out the values. See formula in pic attached. Then copy-paste column B by values only, then sort it to get rid of the blank spaces. For a better way, I've used the filter function in a separate answer.