How to perform caculations using database functions in this case

[nagaraj45]

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

 

[Pete_UK]

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

 

[Roger Govier]

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.