Hi, first time posting so apologies if I'm not doing this correctly and I can clear up my question in some way. I searched the archives but couldn't find this question. If it already exists, please point me to it.
I'm having trouble creating a formula that finds the number of instances that a value falls between two numbers among the most recent 'n' instances of those values in a list. In the image shown, column B contains the maximum value to be counted for each row, and C2 and D2 contain the number of last instances to be queried. The numbers in the gray background are what I hope a formula can output for each cell.
To give two examples using the image: for cell C3, I'd like to find the number of times the values in the data to the right fall within the 1<=x<=2 range over the last 3 dates that a number appears on that row. The last three values on that row are 4, 2, and 3, so only the 2 falls within 1-2, so C3 should return 1 as an answer. For cell D5, I'd like to find the number of times the values in the data to the right fall within the 1<=x<=3 range over the last 6 dates that a number appears on that row. The last 6 values are 2, 3, 2, 0, 1, 3, so the 2, 3, 2, 1, and 3 fall within the 1-3 range, so D4 should return 5 as an answer.
If it helps allow for a simpler/faster formula, the maximum value to be queried (column B) would be 4.
Thank you for any guidance.
Mike
I'm having trouble creating a formula that finds the number of instances that a value falls between two numbers among the most recent 'n' instances of those values in a list. In the image shown, column B contains the maximum value to be counted for each row, and C2 and D2 contain the number of last instances to be queried. The numbers in the gray background are what I hope a formula can output for each cell.
To give two examples using the image: for cell C3, I'd like to find the number of times the values in the data to the right fall within the 1<=x<=2 range over the last 3 dates that a number appears on that row. The last three values on that row are 4, 2, and 3, so only the 2 falls within 1-2, so C3 should return 1 as an answer. For cell D5, I'd like to find the number of times the values in the data to the right fall within the 1<=x<=3 range over the last 6 dates that a number appears on that row. The last 6 values are 2, 3, 2, 0, 1, 3, so the 2, 3, 2, 1, and 3 fall within the 1-3 range, so D4 should return 5 as an answer.
If it helps allow for a simpler/faster formula, the maximum value to be queried (column B) would be 4.
Thank you for any guidance.
Mike
