Count entries in the column translating to minutes.

  • Thread starter Thread starter L. Howard
  • Start date Start date
L

L. Howard

Date and time in column A. (one minute intervals)
Decimal values in column B.

Value in F1 = -0.02 (will change for other queries)

Using this formula in C pulled down I get the 1's.

=IF(B2>$F$1,1,"")


10/12/2014 7:00 -0.0012567 1
10/12/2014 7:01 -0.1432241
10/12/2014 7:02 -0.1288761
10/12/2014 7:03 -0.0087432 1
10/12/2014 7:04 -0.0137432 1
10/12/2014 7:05 -0.0157432 1
10/12/2014 7:06 -0.0091433 1
10/12/2014 7:07 -0.1288761
10/12/2014 7:08 -0.8711239
10/12/2014 7:09 -1.8711239
10/12/2014 7:10 -0.1288761
10/12/2014 7:11 0.1288761 1
10/12/2014 7:12 0.0304561 1
10/12/2014 7:13 0.037056 1

What formula can I use by itself OR in conjunction with =IF(B2>$F$1,1,"") to show in either column C or D the number of minutes the B values were greater than the F1 value.

Where the above would show times of:

7:00 = 1 min
7:03 to 7:06 = 3 min
7:11 to 7:13 = 2 min

I intend to use the workable formula in a macro to do a few thousand entries.
That I believe I can do if I can find what formula to use.

Howard
 
Hi Howard,

Am Fri, 26 Dec 2014 18:49:12 -0800 (PST) schrieb L. Howard:
What formula can I use by itself OR in conjunction with =IF(B2>$F$1,1,"") to show in either column C or D the number of minutes the B values were greater than the F1 value.

Where the above would show times of:

7:00 = 1 min
7:03 to 7:06 = 3 min
7:11 to 7:13 = 2 min
Click to expand...

in C1 try:
=IF(B1>$F$1,1,"")
in C2 try:
=IF(AND(B2>$F$1,ISNUMBER(C1)),C1+1,IF(B2>$F$1,1,""))
and copy down

For everything else you need VBA


Regards
Claus B.
 
Hi Howard,

Am Fri, 26 Dec 2014 18:49:12 -0800 (PST) schrieb L. Howard:
What formula can I use by itself OR in conjunction with =IF(B2>$F$1,1,"") to show in either column C or D the number of minutes the B values were greater than the F1 value.

Where the above would show times of:

7:00 = 1 min
7:03 to 7:06 = 3 min
7:11 to 7:13 = 2 min
Click to expand...

I don't know what your expected output should be.
Please look here:
https://onedrive.live.com/?cid=9378...#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for "Times"


Regards
Claus B.
 
Hi Howard,

Am Sat, 27 Dec 2014 02:37:14 -0800 (PST) schrieb L. Howard:
With the two formulas and the code, that certainly will be adequate.
Click to expand...

I made another VBA solution on a new sheet.
Please tell me what output you want.


Regards
Claus B.
 
I made another VBA solution on a new sheet.
Please tell me what output you want.


Regards
Claus B.
--
Click to expand...


Hi Claus,

The Times2 version is excellent!

I believe the intent is to understand how many minutes the value in F1 is exceeded. And the Times2 code does exactly that.

Thank you again.

Howard
 
Back
Top