Time series #2

I have several tabs for different dates with data in it.  Colomn 
contains the time in format 13:30:30.

For my project, I require to know the number of data points in variou
time series, pls see below.

7:30:00	8:30:00
8:30:00	9:30:00
9:30:00	10:30:00
10:30:00	11:30:00
11:30:00	12:30:00
12:30:00	13:30:00
13:30:00	14:30:00
14:30:00	15:30:00
15:30:00	16:00:00

I tried countif and if function but could not come up with the results
PLS HELP.  If I have to do it manually then I am dead.....

--
mohitmahaja
-----------------------------------------------------------------------
mohitmahajan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=813
View this thread: http://www.excelforum.com/showthread.php?threadid=49267

0
12/12/2005 11:54:44 AM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
504 Views

Similar Articles

[PageSpeed] 58

Do you mean something along the lines  of

=SUMPRODUCT(--(G$2:$G$1000>=TIME(7,30,0)),--(G$2:$G$1000<TIME(8,30,0)))

etc.

Best to put the comparison times in cells and use say

=SUMPRODUCT(--($G$2:$G$1000>=A1),--($G$2:$G$1000<B1))

which can then be copied easily

-- 
 HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"mohitmahajan" <mohitmahajan.1zxe5m_1134388501.769@excelforum-nospam.com>
wrote in message
news:mohitmahajan.1zxe5m_1134388501.769@excelforum-nospam.com...
>
> I have several tabs for different dates with data in it.  Colomn G
> contains the time in format 13:30:30.
>
> For my project, I require to know the number of data points in various
> time series, pls see below.
>
> 7:30:00 8:30:00
> 8:30:00 9:30:00
> 9:30:00 10:30:00
> 10:30:00 11:30:00
> 11:30:00 12:30:00
> 12:30:00 13:30:00
> 13:30:00 14:30:00
> 14:30:00 15:30:00
> 15:30:00 16:00:00
>
> I tried countif and if function but could not come up with the results,
> PLS HELP.  If I have to do it manually then I am dead......
>
>
> -- 
> mohitmahajan
> ------------------------------------------------------------------------
> mohitmahajan's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=8130
> View this thread: http://www.excelforum.com/showthread.php?threadid=492678
>


0
bob.phillips1 (6510)
12/12/2005 12:16:01 PM
That was what Bob's formula did, did you even try it? It returns 10 using 
you sample, if not show the result you expect

-- 
Regards,

Peo Sjoblom

(No private emails please)


"mohitmahajan" <mohitmahajan.1zyl7n_1134444301.4349@excelforum-nospam.com> 
wrote in message 
news:mohitmahajan.1zyl7n_1134444301.4349@excelforum-nospam.com...
>
> No...what I am trying to do is find the number of times the data lies
> between these two parameters. For example, if the data in G colomn is
> like below:
>
> Time
> 7:32:57
> 7:48:26
> 7:55:05
> 7:57:37
> 8:02:17
> 8:08:36
> 8:14:05
> 8:19:27
> 8:21:10
> 8:25:56
> 9:08:11
> 9:09:48
> 9:19:56
> 9:29:06
> 9:32:05
> 9:47:04
> 9:53:58
> 9:55:51
> 10:01:41
> 10:06:08
> 10:10:05
> 10:23:34
> 10:29:22
> 10:41:46
> 11:00:09
> 11:09:48
> 11:16:05
> 11:20:23
> 11:25:37
> 11:28:45
> 11:34:51
> 11:41:27
> 11:47:47
> 11:52:14
>
> Now from this data I want to know how many lie in the series (as I am
> calling it) or in the time groups specified.
> 7:30:00 to 8:30:00  - In this all data points greater than or equal to
> 7:30:00 and less than 8:30:00 are to be taken into count.  Likewise for
> rest of the series....
>
>
> -- 
> mohitmahajan
> ------------------------------------------------------------------------
> mohitmahajan's Profile: 
> http://www.excelforum.com/member.php?action=getinfo&userid=8130
> View this thread: http://www.excelforum.com/showthread.php?threadid=492678
> 

0
terre081 (3244)
12/13/2005 3:46:28 AM
Ok, here is the 2nd part of the problem.....

Now I have been asked to take out data per associate hour wise.....
I have attached the sheet with the table also in which info i
required....

Pls help:confused

+-------------------------------------------------------------------
|Filename: Book2.zip                                                
|Download: http://www.excelforum.com/attachment.php?postid=4117     
+-------------------------------------------------------------------

--
mohitmahaja
-----------------------------------------------------------------------
mohitmahajan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=813
View this thread: http://www.excelforum.com/showthread.php?threadid=49267

0
12/13/2005 5:49:37 AM
I tried :

=+COUNTIF(*$A$2*:$A$664,AND($A$2,SUMPRODUCT(--($G$2:$G$1000>=K2),--($G$2:$G$1000<L2))))

but this did not work even though it did not give me any error.  The
value returned here for all time series/groups was 0.
The cell in bold is the name reference....

Pls help....


-- 
mohitmahajan
------------------------------------------------------------------------
mohitmahajan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=8130
View this thread: http://www.excelforum.com/showthread.php?threadid=492678

0
12/13/2005 8:34:16 AM
As far as I can see, there are no names associated with the times, so you
cannot get an analysis by time by name.

-- 
 HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"mohitmahajan" <mohitmahajan.1zyzka_1134462901.2985@excelforum-nospam.com>
wrote in message
news:mohitmahajan.1zyzka_1134462901.2985@excelforum-nospam.com...
>
> I tried :
>
>
=+COUNTIF(*$A$2*:$A$664,AND($A$2,SUMPRODUCT(--($G$2:$G$1000>=K2),--($G$2:$G$
1000<L2))))
>
> but this did not work even though it did not give me any error.  The
> value returned here for all time series/groups was 0.
> The cell in bold is the name reference....
>
> Pls help....
>
>
> -- 
> mohitmahajan
> ------------------------------------------------------------------------
> mohitmahajan's Profile:
http://www.excelforum.com/member.php?action=getinfo&userid=8130
> View this thread: http://www.excelforum.com/showthread.php?threadid=492678
>


0
bob.phillips1 (6510)
12/13/2005 9:01:08 AM
:) Got it....
Now what I did was

=IF(AND(G244<$N$2,G244>=$M$2),"7:30 till
8:30",IF(AND(G244<$N$8,G244>=$M$8),"13:30 to
14:30",IF(AND(G244<$N$7,G244>=$M$7),"12:30 to
13:30",IF(AND(G244<$N$6,G244>=$M$6),"11:30 to
12:30",IF(AND(G244<$N$5,G244>=$M$5),"10:30 to
11:30",IF(AND(G244<$N$4,G244>=$M$4),"9:30 to
10:30",IF(AND(G244>=$M$3,G244<$N$3),"8:30 to 9:30")))))))

I copied this and got each data point in a series and then did a pivot
on them and got hour wise time spent and hour wise data points for each
associate/team member......

Thanks Bob for your patience.....:)


-- 
mohitmahajan
------------------------------------------------------------------------
mohitmahajan's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=8130
View this thread: http://www.excelforum.com/showthread.php?threadid=492678

0
12/14/2005 7:10:24 AM
Reply:

Similar Artilces:

Function help needed urgently!!! #2
Hi anyone who can help... I have two sets of figures 1 is sales and 1 is target. Eg: Sales Target Incentive 5 5 In the Incentive column I want a formula that will calculate the Incentive payment for employees who exceed there target. The easy part is the if statement for example 10% of their sales if they exceed target: =if(sales>target,Sales*10%,0) The complicated part comes in to play when only when they hit multiples of 10 are they entitled to an incentive: If the Sales are 5 and the Target is 5 - no incentive. If the Sales are 10 and the Target is 5 - no incenti...

An advanced filtering issue #2
Hi all, Any way to use the advanced filter in order to get the needed result as in A14:F15 ? in order to show all the values of each month for the date 30-09-2005 . (please check attached sheet) Any other suggestion ? Thank you very much . +-------------------------------------------------------------------+ |Filename: question abt advanced filtering.zip | |Download: http://www.excelforum.com/attachment.php?postid=3874 | +-------------------------------------------------------------------+ -- gaftalik ----------------------------------------------------------...

Reason Code Report #2
I sell a perishable product, therefore I need to be able to track product that has perished. In the help menu, under reason codes, it states, the information is stored for use in tracking and reporting. That is why I chose this method to record dead plants. Now I need to know the cost and profit lost of these dead plants, as well, I don't want to purchase them again. So how do I get the information???? I read about a guy tracking dead fish by making the item inactive, although he was having trouble, should I change how I track this info for the future? I would greatly appreciate h...

running macros #2
Hi, I have created a macro that imports data, sorts it, and delimits it. The macro in itself works just fine, however trying to run it a second time on a different file causes it to blow up. Prior to importing the data, I clear out the old contents so it doesn't prompt me to overwrite, the re-run of the macro WILL prompt me, and rather than overwrite, it appears to insert instead, and therby causes it to trash the existing data. I'd sure appreciate any help here. Thx. Without seeing your code, it's nearly impossible to tell. In article <58725910-CF96-4589-8CF0-76ADF515E2...

Charging 1 or 2 Taxes on items automatically
I have 4 columns, A1-item, B1-tax, C1-tax, D1- Total. Some items are taxable on both, others only on 1, is there a way to automatically charge 1 or 2 taxes on the total. Thank-You B.W. It is easy: set the values in columns B and C as percents. Then D would have something like: =(1+B1+C1)*A1 With this set-up you can remove a column B or C tax just by setting the value to zero. -- Gary's Student "B.W." wrote: > I have 4 columns, A1-item, B1-tax, C1-tax, D1- Total. Some items are taxable > on both, others only on 1, is there a way to automatically charge 1 or 2 &...

XP Style button using MFC 4.2
Hi, I am using Visual C++ 6, MFC 4.2. Windows XP SP2. My problem is, how could I get the XP Style buttons on the dialog. If i dynamically create the button using CreateWindow() the button still looks same. I've tried 'Button Controls' article on MSDN but no luck. Could anyone help me? Thanks, Fahad Themes are handled by the system, and not on a button-basis. The way to give your application running on XP the themed look is to include a manifest resource. The resource can either be a resource compiled into your EXE or it can be a separate file in the same directory as your ...

CRM v1.2 Report Manager Problem
Hi all, I am having a problem with Report Manager after having reinstalled the MS CRM on the CRM Server. When I logged in using Report Manager, it stated that the "CRM folder does not exist!" I can run all other CRM services fine but with this error I cannot publish all of my reports and hence keep getting the Crystal Report Logon Error. I am stuck to this problem for so long and still have no solution to it... I tried to delete and rebuild the APS database, uninstalled CRM then go into the registry to delete all crystal related values, everything I can digged out from the internet...

Non3rd Party: Multiple Taxes (i.e. 2 Diff States)
I posted this a few days ago, and had some great response, however, I am looking for a non 3rd party solution to this issue. Does anyone have any ideas? RMS V1.3flat I am trying to establish two different Item tax structures for two different tax authorities, let's say one for MN and one for ND state. Preface: I am giving this as a real HL example. Assigning taxes on a per customer basis will not work as a solution to this issue. Please do not take it literally that I need to assign per customer based on Billing or Shipping State. Example: MN Tax has: MN State, MN City and MN C...

Outlok 2003 Word Attachments are Read Only and in 2 page view
I have a client with Outlook 2003 and when he receives an email with a word doc attached and attempts to open it (rather than save it 1st) it opens in Word as read only and shows as a split screen (2 page book view). if he saves it to the drive 1st he can remove the read only attribute and then Word opens it normally. Any Ideas? Not sure why the view is different. My OT advice - always save attachments you want to edit, and then open them. Otherwise you're using a temp copy of the file & may lose changes. Marc Seidler wrote: > I have a client with Outlook 2003 and when he recei...

Design checker question #2
I often get an "error" in design checker that says "Object is not visible." When I right-click and choose "Go to this item", nothing apparent happens. When I choose "Fix: bring object to front", nothing happens, and the error message does not go away. What am I missing here? Craig Select all, give everything a line. Does the phantom object show up? If it does, make a note where it is and do an "undo." At least you will know the area where to try to delete the object. -- Mary Sauer http://msauer.mvps.org/ "Craig Schiller" &l...

Outlook backups #2
I am trying to find out some information about outlook backups. I was under the impression that with Outlook 2003 you no longer have to close down outlook to do the backup. But I was just told by a friend that that is not true the .pst files are locked and you still can't back up no matter what software you use unless outlook is closed. Does anyone know of software that will backup outlook even if it is opened or possibly will close outlook before it backs it up? Thanks Philip There are 3rd party utilities that do this. But Microsoft produced a little addon to for personal folder ...

time calculation 08-14-07
Any Help Please. . . I am new in access, and im doing some calculations from my query design. I need to calculate two fields in a query. It is like cascading calculation that the new field is the result and that result will be added to 2nd and to 3rd row and so on. The calculation is on hh:mm in 24 hr format. please see example: OnChk OffChk On/OffDifference Total Hour 8:59 13:45 4:46 1110:57 => This is carry over figure 8:50 12:57 4:07 1115:04 7:48 9:49 2:01 1117:05 1. I got the On/OffDifference by us...

Deposit on Back Order #2
Hi , how can a take a deposit from a customer on MS Point of Sales Thanks. Ben, You can't, MSPOS doesn't support backorders, layaways, work orders, or quotes like RMS does. Here's a chart of the major differences between products; http://tinyurl.com/ngh4g -- * Get Secure! - www.microsoft.com/security You must be using Outlook Express or some other type of newsgroup reader to see and download the file attachment. If you are not using a reader, follow the link below to setup Outlook Express. Click on "Open with newsreader" under the MS Retail Management System on ...

Check links #2
Hello, I get this error message when I do a check links in Bank Managment Amount does not match with CB_Distribution FYI we just Upgraded from GP9 to GP10 sp3 Thanks ...

Visual Basic Error Message #2
After updating an application unrelated to Excel that I have been using for two years, evefry time I now start Excel (I'm using Excel 2003, by the way), I initially get a Microsoft Visual Basic error window popping up on my screen: "System Error &H8000FFFF (-2147418113). Catastrophic failure." When I click OK, a second Microsoft Visual Basic error window pops up: "Out of memory." I then click OK, the window disapears, and then everything runs just fine. All my calculations, prior work and spreadsheets and so forth, as well as new files I create are readily...

OWA Question #2
I'm running Exchange 2003 with IIS 6. Is there a less graphical version or option for people that have a dial up connection? Yes. You can use the basic version per the following: 821909 Use Exchange Server 2003 Outlook Web Access Basic with Browsers Earlier http://support.microsoft.com/?id=821909 And this should help you with setting it up: 830827 How to manage Outlook Web Access features in Exchange Server 2003 http://support.microsoft.com/?id=830827 Regards, Colby -- Please do not send e-mail directly to this alias. This alias is for newsgroup purposes only. This posting i...

Count function #2
I have in Several Columns a list of Customer Names and for each column I would like to do a Count of how many in each column but when I do =Count(B2:B33) it returns a 0 and should return 4 Does a Count not work on text? Thanks! Hi, =Counta(B2:B33) if this helps please click yes, thanks "H" wrote: > I have in Several Columns a list of Customer Names and for each column I > would like to do a Count of how many in each column but when I do > > =Count(B2:B33) > > it returns a 0 and should return 4 > > Does a Count not work on text? > > Tha...

Outlook 2003 not closing #2
When I'm done reading and sending mail I close Outlook (It's version 2003, running under Windows XP) using the 'Close' button. After going through a day of opening, checking and answering mail and then closing when done, I've noticed that Task Manager shows multiple copies of OUTLOOK.EXE running. None of them seem to be taking CPU resources, but they do have memory assigned. During the day, I don't notice any error messages. Everything seems to be working fine. There are no clues to indicate that something hasn't closed until I try to re-boot the PC. Th...

USING DATES IN FORMULAS #2
I foubd a problem with excel 2003 If you introduce a formula using dates (in spanish version) (dd/mm/yy), excel doesnt makes the calculation........... ???? Can somebody hep me In the USA version (English), I could use: =if(a1=date(2005,10,27),"It's Oct 27, 2005","it's not") to refer to a date. I don't know what the Spanish function is for =date(), though. Carlos Benavides wrote: > > I foubd a problem with excel 2003 > If you introduce a formula using dates (in spanish version) (dd/mm/yy), > excel doesnt makes the calculation........... ????...

How do I use a pivot table to get an average count? #2
I have a list of transactions with colum headers of Month, Date, Weekday, Time and the transaction data. I would like to see the average number of transaction per hour per day. Can this be done in a pivot table? ...

Migrating Public Folders #2
I have just got involved in a Migration from EX5.5 to EX2003. 1. AD is clean an all accounts have been manually created not migrated. 2. Company has over 12,000 public folders on EX5.5. 3. I need to migrate mailboxes to EX2003 and connect then to the manually created accounts. 4. Migrate the Public Folders whilst keeping the security.! What is the best method to do this. All migrition tools seem to use sid history etc, obviuosly not an optionhere. I have looked at a util called PFADMIN. Does this export security using display name. If so could this be used to re-assign security on the EX2003....

slow performance #2
help! i can't seem to get my outlook to scroll through messages quicker. when i scroll to the next message, it takes up to 10 seconds to bring up the next one! it's driving my nuts! anyone have suggestions? What version of Outlook? What kind of system do you have? -- Aloha, -Ben- Ben M. Schorr, OneNote-MVP http://home.hawaii.rr.com/schorr **I apologize but I am unable to respond to direct requests for assistance. Please post questions and replies here in the newsgroup. Mahalo! "boogznhooch@charter.net" <anonymous@discussions.microsoft.com> wrote in mess...

first tab to other tabs #2
Help Pls. i want to enter data in the first tab, without retyping them in individual tabs have the automatic sepeared in other tabs. exp. column a b c a b c a b c global 300 pcs 10 boxes global 300 pcs 10 boxes island 400 pcs 20 boxes island 400 pcs 20 boxes global 200 pcs 5 boxes island 300 pcs 10 boxes island 300 pcs 10 boxes global 200 pcs 5 boxes all vendors global island Thank You ...

Retrieve data #2
I have a spreadsheet "franchise Stores" containing franchises in column "a" and their stores store# in column "b", a simple database with most franchises having more than one store. Every day different stores submit their problems to us using only their store numbers. I have a seperate spreadsheet for these calls "call log". I manually look up the stores franchise with the store number using the Find Function in "franchise Stores", and copy, paste it to "call log". Is there a way to have excel do this on its own? There are too many to...

Open XML File Converter
Version: 2004 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Any word on an update to this converter that resolves the "There is not enough memory or disk space to convert this document" bug on SL? I've seen it reported elsewhere so I'm assuming it's a known issue, but can't find any relevant info here. Thanks. On 11/2/09 2:33 PM, in article 59b7fd3a.-1@webcrossing.caR9absDaxw, "elliotcolbert@officeformac.com" <elliotcolbert@officeformac.com> wrote: > Any word on an update to this converter that resolves the "There is not e...