Assuming your list of numbers srtarts in A2, then in cell B2 place:
=IFERROR(MATCH(A2,A3:$A$10,0)-1,"")
and copy down.
The $A$10 should be adjusted to the bottom cell of your list.
Thanks for reading this. Im looking for a way to account intervals ( number of sells between same value(s) in one column). I need them ( intervals) to be posted in next column . Is there any formula for that? Its like column A has stream of numbers and l need to look how many cells are between every apiarence of number 26 in column A and values (number of cells between ) beeng posted in column B. For exemple, column A has numbers 26, 0, 32, 15, 5, 26, 0, 26, 8... first interval for number 26 will be of value 4 ( 4 cells between), second will be of value 1 ( just one cell between).
Im using excel 2010, windows xp.
Assuming your list of numbers srtarts in A2, then in cell B2 place:
=IFERROR(MATCH(A2,A3:$A$10,0)-1,"")
and copy down.
The $A$10 should be adjusted to the bottom cell of your list.
I would like to know, if there is any way to make it adjust itself till the bottom of the list [autoadjusting for new entries ]? And how to make it account for multiple values, instead of one only. For example if l want to formula calculate skips between entries of same group of values,where any number of same group treated as number 26 on example above. 26,27,28 for exemple
Yes, you can do something like;
=IFERROR(MATCH(A2,A3:$A$3500,0)-1,"")
where the 3500 is beyond the bottom of your list and not encroaching on anything else.
Or you can do something like:
=IFERROR(MATCH(A2,OFFSET(A3,0,0,COUNTA(A3:A$1048576)),0)-1,"")
This is much more difficult; 2 questions, is this what you're looking for (click on the thumbnail to see a larger image):
2. Would a user defined function do? This would make the entry of the formula much easier; it might be something like =GapSize($E$1:$E$3,A2:A$100000).
Last edited by p45cal; 2014-08-05 at 07:26 PM.
in B2 (with the group in E1:E3 as in the image):
=IF(ISNA(MATCH(A2,$E$1:$E$3,0)),"",MIN(IF(ISNA(MATCH($E$1:$E$3,OFFSET(A3,0,0,COUNTA(A3:A$20000)),0)),FALSE,MATCH($E$1:$E$3,OFFSET(A3,0,0,COUNTA(A3:A$20000)),0)))-1)
entered with Ctrl+Shift+Enter (aka array-entered) and copy down.
A devil to manage a formula of that length.
So a udf solution:
In B2 the normally entered formula:
=GapSize($E$1:$E$3,OFFSET(A2,0,0,COUNTA(A2:A$100000)))
and copy down.
Supported by this udf in a standard code-module:
Also in attached.Code:Function GapSize(Look_Up, SearchIn) GapSize = "" If Not IsError(SearchIn) Then If SearchIn.Rows.Count > 1 Then LookupValues = Look_Up FirstCellIsOneOfLookUpValues = False For Each LookupValue In LookupValues If SearchIn.Cells(1) = LookupValue Then FirstCellIsOneOfLookUpValues = True Exit For End If Next LookupValue If FirstCellIsOneOfLookUpValues Then GapSize = "Last Instance" GS = 2000000# SearchInValues = SearchIn.Value For Each LookupValue In LookupValues I = 0 For Each SearchInValue In SearchInValues I = I + 1 If I > 1 Then If SearchInValue = LookupValue Or I >= GS Then If I < GS Then GS = I Exit For End If End If Next SearchInValue Next LookupValue If GS < 2000000# Then GapSize = GS - 2 End If End If End If End Function
p45cal , You Sir just made my day!!!! Or entire month!!! Very big THANK YOU !!!
Bookmarks