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 | ||||||||||||||||
