Excel: List intervals between two values
I need to count in a list the interval between the same value.
Example list,
1-2-3-1-4-5-1-6-7-8-9-7-8-1
So the answer must be for the value 1 the spaces are 2,2,6.
thank you
Answer:
Array Formula in cell E2:
=SMALL(IF($A$1:$A$14=$C$2, MATCH(ROW($A$1:$A$14), ROW($A$1:$A$14)), ""), ROW(A1)+1)-SMALL(IF($A$1:$A$14=$C$2, MATCH(ROW($A$1:$A$14), ROW($A$1:$A$14)), ""), ROW(A1))-1
How to create an array formula
- Select cell E2
- Type above array formula
- Press and hold Ctrl + Shift
- Press Enter once
- Release all keys
How to copy array formula
- Select cell E2
- Copy (Ctrl + c)
- Select cell range E3:E5
- Paste (Ctrl + v)
How to remove #num errors
=IFERROR(SMALL(IF($A$1:$A$14=$C$2, MATCH(ROW($A$1:$A$14), ROW($A$1:$A$14)), ""), ROW(A1)+1)-SMALL(IF($A$1:$A$14=$C$2, MATCH(ROW($A$1:$A$14), ROW($A$1:$A$14)), ""), ROW(A1))-1,"")
Download excel file
List interval between two values.xls
Related posts:
What values are missing in List 1 that exists i List 2?
Create a list of distinct values from a list where an adjacent cell value meets a criteria in excel
Use a drop down list to search and return multiple values
List five smallest numbers, excluding zeros
Excel: Look for values containing a lookup value and return multiple values horizontally
Recommended categories
A more condensed version
=SMALL(IF($A$1:$A$14=$C$2,ROW($A$1:$A$14)),ROW(A1)+1)-SMALL(IF($A$1:$A$14=$C$2,ROW($A$1:$A$14)),ROW(A1))-1
Sam,
Thanks!!
My intention was to create an array from 1 to n. n is the total number of cells in the cell reference.
MATCH(ROW(cell_ref), ROW(cell_ref))
I'm trying to return a value of an adjacent cell when a certain condition is met. Such as if a number is less than or greater than another value in another cell and return the value of the cell next to it.
Jesse,
Download excel *.xlsx file
Jesse.xlsx
Hi Oscar,
I have a list of numbers, let's say: 15,10,5,0, and I have a single value, let's say 6. I want to colour the interval where this single value falls. I used the frequency function to indicate the interval where it falls and then will apply conditional fomatting. I was wondering if there is a more elegant and simple way around it?
Thank you,
Aleksandra