Excel - Determining if a range of cells meets criteria

April 12, 2012 at 06:59:38
Specs: Windows XP
To give you some background, spreadsheets are data files from participants that have worn our physical activity armband for a week at a time during their pregnancy. The test file is just a few days worth of data that I wore last summer to give us some preliminary data.

While the monitor gives us a variety of outputs from heat sensors and accelerometers, for this analysis I'm primarily concerned about how many minutes of physical activity the women are doing over the week. I want to look at their accumulated activity but also the moderate-vigorous intensity activity done for a minimum of 10 minutes at a time.

Is any way to write a formula that would extract periods of activity in column that are at least 3.0 or above and last for at least 10 minutes? Each row is one minute of data. Perhaps the formula could copy values from column a into a new column if they are part of at least a 10 min consecutive series of numbers equal to or greater than 3.

For example, the rows below include one bout of activity that are at least a value of 3.0 and last for at least 10 minutes. If I could get this and similar bouts to copied into a new column, I could then total that column to get total minutes of moderate-vigorous intensity per week sustained for at least 10 minutes at a time.

1.793021202
2.855103254
4.312698841
4.513991356
5.161720276
5.682873726
5.072729588
5.416456223
5.485177517
5.239011765
5.516265392
4.474151134
4.014439583
4.05330801
4.114559174
4.142951965
3.105585814
1.333854318
2.616518021


See More: Excel - Determining if a range of cells meets criteria

Report •


#1
April 12, 2012 at 09:20:53
I don't understand how the data in your list corresponds to the words in your text.

I don't know what you mean by "Each row is one minute of data." nor do I understand how "the rows below include one bout of activity that are at least a value of 3.0 and last for at least 10 minutes."

I see a lot of numbers that are over 3 but I have no idea what you mean by "last for at least 10 minutes."

Please explain and we'll see what we can do.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
April 13, 2012 at 12:09:36
Basically in Row A you will find a list of numbers that is the amount of activity recorded for that time. Each row constitutes a new minute. So A1 is the numerical representation of minute #1. A2 is the numerical representation of minute #2. Ignore column B for now

What I am trying to accomplish is to copy rows from column A where the number is greater than or equal to 3 to column B. The kicker is that it is only when there is 10 consecutive minutes (10 rows or more) where it stays above 3 that I would want to copy. Note the example below where Rows 3 to 17 are all above 3 and there are at least 10 consecutive rows so we copy to column B. Now look at rows 20 and 21. While they are above 3, there is not a consecutive 10 rows (10 min) where the activity stayed above 3 so they are not copied. Now can you write a formula to do that??

          
             A                 B

 1      1.793021202
 2      2.855103254
 3      4.312698841      4.312698841
 4      4.513991356      4.513991356
 5      5.161720276      5.161720276
 6      5.682873726      5.682873726
 7      5.072729588      5.072729588
 8      5.416456223      5.416456223
 9      5.485177517      5.485177517
10      5.239011765      5.239011765
11      5.516265392      5.516265392
12      4.474151134      4.474151134
13      4.014439583      4.014439583
14      4.05330801       4.05330801
15      4.114559174      4.114559174
16      4.142951965      4.142951965
17      3.105585814      3.105585814
18      1.333854318
19      2.616518021
20      4.342342333
21      3.223423423
22      1.343234533


Report •

#3
April 13, 2012 at 14:01:15

Now, you've got to admit that the explanation in your second post is certainly more complete than the explanation in your OP. :-)

There is one part that is still confusing though.

"The kicker is that it is only when there is 10 consecutive minutes (10 rows or more) where it stays above 3 that I would want to copy."

When I look at your data, it appears that the "10 consecutive minutes" can be in either direction (up or down) from a given number.

e.g.:

- A3 has 9 values below it that are >3
- A17 has at least 9 numbers above it that are >3
- A13 has 1 below and at least 8 above.

Am I reading that correctly?

There is no formula that can read the list and extract the value only if it meets the "10 in a row" criteria.

It will probably needs some VBA but I want to make sure that I understand what you are looking for before I start codeing.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
April 13, 2012 at 20:55:33
You are correct. The data is collected by a monitor attached to a person. The goal is to get a count of periods of time 10 min or longer when the exercice level (>3) was up and igonore brief moments of spikes due to various reasons. I dont necessarily need to copy it to column B.....just need a total and figured that might be easiest if there was a formula.

And yes I explained it much better the next time! ha


Report •

#5
April 14, 2012 at 12:36:26

Report •

#6
April 14, 2012 at 16:48:41
It would be te total of the cells that were over 3 when they were a part of consecutive 10 or more rows.

So above it would be the total of lines A3 to A17.


Report •

#7
April 14, 2012 at 19:22:21
The sum of A3:A17 is 70.30592036

If A6 was less than 3 then the sum would 50.63463617, correct?

If A10 was less than 3 then there would nothing to sum, correct?

Can those 2 situations occur?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
April 15, 2012 at 17:08:24
Exactly. You are correct!

Report •


Ask Question