How to perform caculations using database functions in this case
 

How to perform caculations using database functions which will count
the number of instances between two different times in the undernaoted
example.

Date Rate
2:41:08 PM 17475.59
2:40:53 PM 17471.12
2:40:28 PM 17465.72
2:40:13 PM 17448.77
2:40:03 PM 17440.78
2:39:48 PM 17437.58
2:39:38 PM 17435.87
2:39:23 PM 17431.73
2:39:13 PM 17429.6
2:38:58 PM 17425.05
2:38:43 PM 17431.12
2:38:33 PM 17434.67
2:38:18 PM 17428.22
2:38:08 PM 17427.22
2:37:28 PM 17435.77
2:37:13 PM 17439.49
2:37:03 PM 17441.45
2:36:48 PM 17440.69
2:36:33 PM 17445.43
2:36:23 PM 17449.32
2:36:08 PM 17469.25
2:35:43 PM 17483.85
2:35:28 PM 17487.99
2:35:18 PM 17488.58
2:35:03 PM 17504.72
2:34:48 PM 17513.39
2:34:38 PM 17519.69
2:34:23 PM 17528.89
2:33:58 PM 17532.92
In this example I would like to count the number of rates ,maximum
rate and minimum rate say between 2.33 pm and 2.38 pm

Thanks in adavance

How to perform caculations using database functions in this case
 

Assuming your data is in columns A and B, then to count them:

=SUMPRODUCT((A2:A200=--"14:33:00")*(A2:A200<=--"14:38:00"))

Maximum is given by this array* formula:

=MAX(IF((A2:A200=--"14:33:00")*(A2:A200<=--"14:38:00"),B2:B200))

and minimum by this array* formula:

=MIN(IF((A2:A200=--"14:33:00")*(A2:A200<=--"14:38:00"),B2:B200,10^100))

I've assumed your data occupies up to 200 rows - adjust these ranges
if you have more.

* With an array formula once you have typed it in (or subsequently
amend/edit it) you must commit it with CTRL-SHIFT-ENTER (CSE) instead
of the usual ENTER. If you do this correctly then Excel will wrap
curly braces { } around the formula when viewed in the formula bar -
you must not type these yourself.

Hope this helps.

Pete




On Feb 8, 12:31*pm, wrote:
How to perform caculations using *database functions which will count
the number of instances between two different times in the undernaoted
example.

Date * *Rate
2:41:08 PM * * *17475.59
2:40:53 PM * * *17471.12
2:40:28 PM * * *17465.72
2:40:13 PM * * *17448.77
2:40:03 PM * * *17440.78
2:39:48 PM * * *17437.58
2:39:38 PM * * *17435.87
2:39:23 PM * * *17431.73
2:39:13 PM * * *17429.6
2:38:58 PM * * *17425.05
2:38:43 PM * * *17431.12
2:38:33 PM * * *17434.67
2:38:18 PM * * *17428.22
2:38:08 PM * * *17427.22
2:37:28 PM * * *17435.77
2:37:13 PM * * *17439.49
2:37:03 PM * * *17441.45
2:36:48 PM * * *17440.69
2:36:33 PM * * *17445.43
2:36:23 PM * * *17449.32
2:36:08 PM * * *17469.25
2:35:43 PM * * *17483.85
2:35:28 PM * * *17487.99
2:35:18 PM * * *17488.58
2:35:03 PM * * *17504.72
2:34:48 PM * * *17513.39
2:34:38 PM * * *17519.69
2:34:23 PM * * *17528.89
2:33:58 PM * * *17532.92
In this example I would like to count the number of rates ,maximum
rate and minimum rate say between 2.33 pm and 2.38 pm

Thanks in adavance

How to perform caculations using database functions in this case
 

One way would be to use the following Array formulae

Count {=SUM((A8:A36=TIME(2,33,0))*(A8:A36<=TIME(2,38,0) ))}
Max {=MAX(IF((A8:A36=TIME(2,33,0))*(A8:A36<=TIME(2,38 ,0)),B8:B36))}
Min {=MIN(IF((A8:A36=TIME(2,33,0))*(A8:A36<=TIME(2,38 ,0)),B8:B36))}

Array formulae have to be entered or amended using Control+Shift+Enter (CSE)
not just Enter.
When you use CSE, Excel will wrap your formula in curly braces { } . Do
not type them yourself.
--
Regards
Roger Govier

wrote in message
...
How to perform caculations using database functions which will count
the number of instances between two different times in the undernaoted
example.

Date Rate
2:41:08 PM 17475.59
2:40:53 PM 17471.12
2:40:28 PM 17465.72
2:40:13 PM 17448.77
2:40:03 PM 17440.78
2:39:48 PM 17437.58
2:39:38 PM 17435.87
2:39:23 PM 17431.73
2:39:13 PM 17429.6
2:38:58 PM 17425.05
2:38:43 PM 17431.12
2:38:33 PM 17434.67
2:38:18 PM 17428.22
2:38:08 PM 17427.22
2:37:28 PM 17435.77
2:37:13 PM 17439.49
2:37:03 PM 17441.45
2:36:48 PM 17440.69
2:36:33 PM 17445.43
2:36:23 PM 17449.32
2:36:08 PM 17469.25
2:35:43 PM 17483.85
2:35:28 PM 17487.99
2:35:18 PM 17488.58
2:35:03 PM 17504.72
2:34:48 PM 17513.39
2:34:38 PM 17519.69
2:34:23 PM 17528.89
2:33:58 PM 17532.92
In this example I would like to count the number of rates ,maximum
rate and minimum rate say between 2.33 pm and 2.38 pm

Thanks in adavance