Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a formula to perform multiple functions. | Excel Discussion (Misc queries) | |||
Can you perform Math functions with ActiveX Checkbox controls? | Excel Worksheet Functions | |||
Are there functions that perform robust statistics in Excel? | Excel Worksheet Functions | |||
perform caculations on displayed value in Excel 2000 | Excel Worksheet Functions | |||
Perform functions on the result of adding two columns | Excel Worksheet Functions |