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

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.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

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

I don't know what your expected output should be.
Please look he
https://onedrive.live.com/?cid=9378A...121822A3%21326
for "Times"


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

I don't know what your expected output should be.
Please look he
https://onedrive.live.com/?cid=9378A...121822A3%21326
for "Times"


Regards
Claus B.

Hi Claus,

With the two formulas and the code, that certainly will be adequate.

I can see I was headed in the right direction with the formula I was attempting to get to work, but could not quite make it work.

Thanks a bunch, Claus.

Howard

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.

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


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional

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


Regards
Claus B.
--


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