I'm not even sure this is possible and if the answer is no, then that's ok. I'm no worse off than I was before.
My data includes Work Order #s and the $$ sold per work order hour. A helper column has an IF statement that shows me all rows where the $$ sold per work order hour is greater than $100. I need to find a range of 100 consecutive rows in those three columns that have at least 30 of the values I'm looking for. Off to the side I do a COUNTIF on the helper column and a range of row 1 to row 100 and press enter. If it doesn't come up as at least 30, I change the range to row 20 to row 120 and try again. Then repeat until the count hits at least 30 in a range of 100 rows.
Is there a formula that will do that?
I'm picturing something with a combo MAX (on the WO #), IF or COUNTIF with perhaps a FREQUENCY for good measure on the helper column but what my mind is picturing is not coming out in spreadsheet form. "it can't be done" is a perfectly acceptable answer.
My data includes Work Order #s and the $$ sold per work order hour. A helper column has an IF statement that shows me all rows where the $$ sold per work order hour is greater than $100. I need to find a range of 100 consecutive rows in those three columns that have at least 30 of the values I'm looking for. Off to the side I do a COUNTIF on the helper column and a range of row 1 to row 100 and press enter. If it doesn't come up as at least 30, I change the range to row 20 to row 120 and try again. Then repeat until the count hits at least 30 in a range of 100 rows.
Is there a formula that will do that?
I'm picturing something with a combo MAX (on the WO #), IF or COUNTIF with perhaps a FREQUENCY for good measure on the helper column but what my mind is picturing is not coming out in spreadsheet form. "it can't be done" is a perfectly acceptable answer.
