1

I have a column of 1 million numbers that were generated randomly. They range from 1 - 1000, and each have two decimal points. There are duplicates of numbers obviously.

I would like to create a function which tells me how many sequences of numbers fall within a certain range. For example:

How many sequences of 10 numbers between 56-60 are there? Or, how many sequences of 20 numbers between 56-60 are there? Or, how many sequences of 50 numbers between 1-10 are there?

Any help would be appreciated. Thank you.

Improve this question
3
  • 1
    How are you defining a "sequence"?
    – Isolated
    Commented Jan 14, 2021 at 20:02
  • By "sequence of numbers" I mean 2 or more numbers that are adjacent to each other in the list. Take for example these ten randomly numbers: 1.00, 1.11, 3.45, 54.23, 1.67, 2.88, 3.00, 34.54, 23.52, 67.78 I would like to be able to know "how many sequences of THREE numbers between 1.00 - 4.00 are there? And the answer would be 2 sequences (1.00, 1.11, 3.45... and 1.67, 2.88, 3.00). Make sense? Sorry it's so convoluted. I'm not sure the best way to ask and I've searched far and wide for the answer but can't seem to find it.
    – jonah einstein
    Commented Jan 14, 2021 at 20:52
  • Please edit your question and provide sample data and expected output. What you've tried so far would also be useful, so we don't go down already covered ground for nothing
    – cybernetic.nomad
    Commented Jan 15, 2021 at 3:48

1 Answer 1

Reset to default
0

I try to create a sample about your requirement, please confirm if this will help you.

=(SUMPRODUCT((A1:C10>H1)*(A1:C10<I1)))/H2

enter image description here enter image description here

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .