how to find last occurrence of repeated value, average, and count how many times it occurred in a column

[jimblimm]

The setup

column b
S
S
S
D
D
S
S
S
D
D
D
D

column b is the column I get my values from a1 would be a helper column that's numeric that indicates days

Column a
1
2
3
4
5
6
7
8
9
10
11
12


I need a formula that will tell me how many times the S REPEATED 3 times REFERENCE TO column a, i need the average times S repeats 3 times, and I need the last time S repeated 3 times in reference to column a (the helper row). Note column a goes up to 8500 values

The answer to the first question should be 2, because S repeated
Itself 3 times twice. Reference B1: b8500
I think the average should be 6,

And last occurrence answer should be 8 via column a1: a8500, that's the last time S repeated 3 times



Please help been struggling to figure this one out..

 

[jimblimm]

thaaannnnxxxxx a million that works perfect.....now is there anyway to get the average amount of time the "s" repeats x amount of times

 

[jimblimm]

Domenic, using the same concept, how do you suppose i get the current count of repeats on the "s" value.....using your last diagram, the answer to my question would be 4 assuming the are no values in the cells under e12 yet. but there will be a formula in e13 that returns a blank cell if a condition isnt met, but will return "s" if a condition is

 

[Domenic]

thaaannnnxxxxx a million that works perfect.....

You're very welcome!

now is there anyway to get the average amount of time the "s" repeats x amount of times

Based on the sample data that I provided, what would you expect as the answer?

Domenic, using the same concept, how do you suppose i get the current count of repeats on the "s" value.....using your last diagram, the answer to my question would be 4 assuming the are no values in the cells under e12 yet. but there will be a formula in e13 that returns a blank cell if a condition isnt met, but will return "s" if a condition is

Try...

=LOOKUP(2,1/(FREQUENCY(IF($B$2:$B$13=D2,ROW($B$2:$B$13)),IF($B$2:$B$13<>D2,ROW($B$2:$B$13)))>0),FREQUENCY(IF($B$2:$B$13=D2,ROW($B$2:$B$13)),IF($B$2:$B$13<>D2,ROW($B$2:$B$13))))

...confirmed with CONTROL+SHIFT+ENTER.

 

[jimblimm]

Let's say the array is B1:b8500. I want the average of how many times the "s" repeats 3 times. I guess it would be B1/ number of times the "s'" was drawn right?

 

[Domenic]

Sorry, still unclear...

 

[jimblimm]

What if I wanted the second lookup to be something other than "s". Example, "s" then
"d".

S
D
S
S
S
D

The last formula works perfect if value repeats itself. Just cant figure out how to adjust it. I now want it to still count how many times the d follows s
S
D

 

[jimblimm]

Or even

S
D
T

Example

S
D
D
S
D
T
S
T
S
D
T

The count of sdt would be 2 because in the above list iT showed twice in that order.
So could you help with a formula that could count SD OR SDT EVEN SDTC

S
D

S
D
T

S
D
T
C

 

[Domenic]

Or even

S
D
T

Example

S
D
D
S
D
T
S
T
S
D
T

The count of sdt would be 2 because in the above list iT showed twice in that order.
So could you help with a formula that could count SD OR SDT EVEN SDTC

S
D

S
D
T

S
D
T
C

New thread here...

http://www.mrexcel.com/forum/showthread.php?p=3153466#post3153466