Hello to all,
I am trying to count some consecutive values and I am straggling to use the proper functions. I used Frequency, column, max, match, countblank, but obviously I can't connect them properly.
Is it possible to help me on this issue?
In the image I am showing with cursors for easy explanation, how the values that I put by hand in yellow cells have been justified. I have used and XL2BB in order to make your life easier if you are kind enough to fill the functions.
In yellow cells BCD, I want to calculate what is the maximum consecutive blank cells from each column.
In yellow cells GHI, I want to calculate how many times I had at least one consecutive value from each column. So if I had 5 consecutive values, we will count it as one time.
In yellow cells LMN, I want to calculate what is the maximum consecutive values that I found from the specific value. So, It will return one number. Just count the maximum consecutive.
Thanks in advance
I am trying to count some consecutive values and I am straggling to use the proper functions. I used Frequency, column, max, match, countblank, but obviously I can't connect them properly.
Is it possible to help me on this issue?
In the image I am showing with cursors for easy explanation, how the values that I put by hand in yellow cells have been justified. I have used and XL2BB in order to make your life easier if you are kind enough to fill the functions.
In yellow cells BCD, I want to calculate what is the maximum consecutive blank cells from each column.
In yellow cells GHI, I want to calculate how many times I had at least one consecutive value from each column. So if I had 5 consecutive values, we will count it as one time.
In yellow cells LMN, I want to calculate what is the maximum consecutive values that I found from the specific value. So, It will return one number. Just count the maximum consecutive.
Thanks in advance
Book5.xlsx | ||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | |||
1 | How many MAX consecutive blank cells from (for example) B5 to B31: | 7 | 5 | 6 | Consecut | |||||||||||
2 | Values: | 1 | 2 | 3 | How many times I had at least one consecutive value: | 2 | 3 | 3 | What is the maximum consecutive? | 2 | 3 | 2 | ||||
3 | Values: | 1 | 2 | 3 | Values | 1 | 2 | 3 | ||||||||
4 | Quarter 1 | Quarter 2 | Quarter 3 | Quarter 1 | Quarter 2 | Quarter 3 | Quarter 1 | Quarter 2 | Quarter 3 | |||||||
5 | 3 | 3 | 3 | |||||||||||||
6 | 1 | 1 | 1 | |||||||||||||
7 | 2 | 2 | 2 | |||||||||||||
8 | ||||||||||||||||
9 | 1 | 2 | 1 | 2 | 1 | 2 | ||||||||||
10 | ||||||||||||||||
11 | 2 | 2 | 2 | |||||||||||||
12 | 3 | 3 | 3 | |||||||||||||
13 | 1 | 2 | 3 | 1 | 2 | 3 | 1 | 2 | 3 | |||||||
14 | 1 | 2 | 1 | 2 | 1 | 2 | ||||||||||
15 | 2 | 2 | 2 | |||||||||||||
16 | ||||||||||||||||
17 | 3 | 3 | 3 | |||||||||||||
18 | ||||||||||||||||
19 | ||||||||||||||||
20 | ||||||||||||||||
21 | 2 | 3 | 2 | 3 | 2 | 3 | ||||||||||
22 | 1 | 1 | 1 | |||||||||||||
23 | 3 | 1 | 3 | 1 | 3 | |||||||||||
24 | 2 | 2 | 3 | 2 | 3 | |||||||||||
25 | 1 | 1 | 2 | 1 | 2 | |||||||||||
26 | ||||||||||||||||
27 | 2 | 2 | 2 | |||||||||||||
28 | 1 | 3 | 1 | 2 | 3 | 1 | 2 | 3 | ||||||||
29 | 2 | 2 | 3 | 2 | 3 | |||||||||||
30 | ||||||||||||||||
31 | 1 | 3 | 1 | 3 | 1 | 3 | ||||||||||
Sheet1 |