Loop in Excel 2007

[lottrup]

Hi,

I have a problem which I hope someone can help me with.
In my spreadsheet I want to count the number of cells that meets a condition. I have tried to use the COUNTIF function, but in thic case I want the condition to specify the area (ex E2:E19) and then the function should count all the cells of this kind.
To be more specific the column on which I set the condition consists of seconds (an increasing set of numbers) and I want to count the cells that go 60 seconds back.

I really hope someone can help me.

/Nana

 

[Yard]

Hi and welcome,

Sorry, but your explanation is not clear (to me). What do you want to count and what are the conditions upon which you want the count to be based?

Can you post a simple example of your data, making a clear distinction between what you have and what you want.

 

[lottrup]

Okay, I'll try again then

Shortly I want to count all the rows (observations) that that have been captured within the last 60 seconds. The seconds are in a column (Unix timestamp) and of course the number of rows that I want to count will vary as the interval of seconds between observations vary. So I want to count all the rows from a certain point (60 seconds back in time) and forward to the current cell/row.
I have 65000 observations at the time, so this could be very very helpful.

Thanks.

 

[Yard]

With your timestamps in column B, and this in cell C1 (or anywhere else you choose):

=NOW()-"00:01:00"

then use this to count the values that fall within 60 seconds in the past:

=SUMPRODUCT(--(B1:B10>=$C$1))

 

[tekkiegurl]

i got confused on the question

 

[Yard]

What question? Do you mean you don't understand my solution, or do you mean you don't understand what you want?

My solution:

Put this in cell C1:
=NOW()-"00:01:00"

Now use this formula to count how many values in the range B1:B10 are greater than equal to the value in C1 (which will always be 1 minute in the past):

=SUMPRODUCT(--(B1:B10>=$C$1))


Is that clearer?<!-- / message --><!-- sig -->

 

[lottrup]

Thank you so much, but perhaps I just don't understand your answer completely.

I guess that the NOW() function creates the current date and time, but I don't see why this is nescesary when I want to count the observations 60 seconds back in time from the timestamp in ex column B?

When you write B1:B10 in the SUMPRODUCT function - should I place this in the 10th row, and how can I make it dynamic so that it will always count all the obervations 60 seconds back in time from the timestamp?

And just because I'm new with this I would like to know what the "--" means in the SUMPRODUCT function? And also what the $ signs mean when you write $C$1?

 

[Yard]

I guess that the NOW() function creates the current date and time,

Yes it does, and the

-"00:01:00"

part subtracts one minute from the current date and time, thereby returning the time value for one minute in the past.

but I don't see why this is nescesary when I want to count the observations 60 seconds back in time from the timestamp in ex column B?

If you didn't have that, how do you expect Excel to know to what it should compare the timestamp in column B? i.e. Column B contains 12:30:46 and you want Excel to know whether to count this cell...so you must somehow tell it what you are comparing it to.

When you write B1:B10 in the SUMPRODUCT function - should I place this in the 10th row

No, you put the formula wherever you want the counted result to appear. The B1:B10 refers to the range containing all the timestamp values - you just adjust it to suit. I think you said you have ~65000 values, so maybe rather than a range, just specify the whole column using B:B (but don't expect lightning-fast calculation time!)

and how can I make it dynamic so that it will always count all the obervations 60 seconds back in time from the timestamp?

The formula is dynamic in the sense that, every time the worksheet is calculated, it will count how many timestamp values are greater than the value in C1, and the value in C1 will always equal the time it was one minute ago.

And just because I'm new with this I would like to know what the "--" means in the SUMPRODUCT function?

It converts TRUE values to 1 and FALSE values to 0, all of which are then summed to give you a total of the number of times the condition is TRUE. The condition is the

B1:B10>=$C$1

part.

So if 3 values within B1:B10 are greater than or equal to the value in C1, the formula counts 3 TRUE values, which is 3 * 1 = 3.

And also what the $ signs mean when you write $C$1?

It locks the reference to C1, so that wherever you copy your formula it will always refer to C1.



Have you actually tried implementing the formulas with unexpected results, or are you pre-judging the validity of it?

 

[lottrup]

Now I completely understand what you mean and it works perfectly
Thank you so much for your time and help!