I started out with an attempt at understanding your description, and filling in where I needed to with guesses. While I was writing those guesses in this space, the light dawned. Here's what I think you mean. (Really, it'd have been a lot easier had you included more punctuation.)
Quote:
I have some data that looks like this:
Code:
A B C D E F G H I J K
1 Date 1 2 3 4 5 6 7 8 9 10
2 Ivan 0 23 12 14 0 21 11 55 0 21
3 Susan 15 12 25 0 17 20 26 0 12 14
4 Henrik 11 0 15 15 26 35 31 0 15 12
5 Tommy 12 15 24 15 0 11 15 18 48 25
I'm trying to construct row 8 as follows: Fill in the values column by column from the above rows, starting with row 2 until and including the first 0. Fill in subsequent columns in row 8 from the row 3, again until and including a 0; then continue with the following row, and so on until you get to column 10.
Using the above data, a 0 appears immediately on Ivan's line, so line 8 would look like this:
Code:
A B C D E F G H I J K
2 Ivan 0 23 12 14 0 21 11 55 0 21
8 0
Then we take up with the next column using Susan's row, and stop with the next 0:
Code:
A B C D E F G H I J K
3 Susan 15 12 25 0 17 20 26 0 12 14
8 0 12 25 0
And so on:
Code:
A B C D E F G H I J K
4 Henrik 11 0 15 15 26 35 31 0 15 12
8 0 12 25 0 26 35 31 0
A B C D E F G H I J K
5 Tommy 12 15 24 15 0 11 15 18 48 25
8 0 12 25 0 26 35 31 0 48 25
Once you have line 8 as defined here, count the length of each sequence up to and including 0, and put the result in row 10. Like this: The first 0 occurs immediately, so the length of that sequence is 1. The next sequence is 12, 25, 0; the 0 ends that sequence, so its length is 3. Then 26, 35, 31, 0, that's 4 long; last 48 and 25, which is two long. (It doesn't end in 0, but you can presume a 0 in column L.) So row 10 looks like this:
Code:
A B C D E F G H I J K
10 1 3 4 2
Once I have row 10, I want to know how many of the values in row 10 are greater than 3—just 1 of them, in the above example.
How do I do all that?
|
Is that an accurate description of what you need?
06-02-2014, 09:10 AM
Similar Threads
Excel 2007, highlight entire row based on data found in one cell
Windows 7 64bit 
Office 2010 32bit
Hybrid Mode