number in system using arrivals and departures

Is there and easy way to figure out the total number of persons in a system if have access to the arrival and departure times. I have tried to use counts with if statements, but I keep getting the number 1.  I know this is not correct. Is there any special macros or functions

Thanks
0
anonymous (74722)
4/14/2004 10:16:03 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
448 Views

Similar Articles

[PageSpeed] 13

Hi
you may provide some more details about your data. Could you post some
example rows (plain text - no attachments please) and describe your
expecte result

--
Regards
Frank Kabel
Frankfurt, Germany


megv wrote:
> Is there and easy way to figure out the total number of persons in a
> system if have access to the arrival and departure times. I have
> tried to use counts with if statements, but I keep getting the number
> 1.  I know this is not correct. Is there any special macros or
> functions.
>
> Thanks

0
frank.kabel (11126)
4/14/2004 10:21:49 PM
Would you  not just count the departures and subtract that from a count of
the arrivals.

For instance

=COUNTA(B:B)-COUNTA(A:A)

-- 

HTH

Bob Phillips
    ... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"megv" <anonymous@discussions.microsoft.com> wrote in message
news:698433AC-7B86-4C31-B33A-B83DB76F4B88@microsoft.com...
> Is there and easy way to figure out the total number of persons in a
system if have access to the arrival and departure times. I have tried to
use counts with if statements, but I keep getting the number 1.  I know this
is not correct. Is there any special macros or functions.
>
> Thanks


0
bob.phillips1 (6510)
4/14/2004 10:35:54 PM
Sample of the data in questio

ARRIVAL	DEPARTUR
12/31/00 21:22	1/1/01 1:2
12/31/00 21:37	1/1/01 0:3
12/31/00 22:00	1/1/01 0:3
12/31/00 22:13	1/1/01 0:0
12/31/00 22:12	1/1/01 0:2
12/31/00 22:15	1/1/01 0:1
12/31/00 23:16	1/1/01 0:3
12/31/00 23:34	1/1/01 3:1
12/31/00 23:36	1/1/01 1:4
12/31/00 23:49	1/1/01 0:3
1/1/01 0:07	1/1/01 2:2
1/1/01 0:12	1/1/01 1:0
1/1/01 0:13	1/1/01 0:5
1/1/01 0:38	1/1/01 2:1
1/1/01 0:42	1/1/01 3:1
1/1/01 0:51	1/1/01 1:5
1/1/01 0:51	1/1/01 3:1

0
anonymous (74722)
4/14/2004 11:31:06 PM
Hi
if these are date/time fileds and you have your start arrival date/time
in cell C1 and the latest departure time in D1 (that is you want to
count all perople who have arrived after C1 and have not departed
before D1) try
=SUMPRODUCT(--(A1:A100>=C1),--(B1:B100>D1))

--
Regards
Frank Kabel
Frankfurt, Germany


megv wrote:
> Sample of the data in question
>
> ARRIVAL DEPARTURE
> 12/31/00 21:22 1/1/01 1:20
> 12/31/00 21:37 1/1/01 0:30
> 12/31/00 22:00 1/1/01 0:30
> 12/31/00 22:13 1/1/01 0:05
> 12/31/00 22:12 1/1/01 0:20
> 12/31/00 22:15 1/1/01 0:10
> 12/31/00 23:16 1/1/01 0:30
> 12/31/00 23:34 1/1/01 3:10
> 12/31/00 23:36 1/1/01 1:40
> 12/31/00 23:49 1/1/01 0:37
> 1/1/01 0:07 1/1/01 2:27
> 1/1/01 0:12 1/1/01 1:00
> 1/1/01 0:13 1/1/01 0:55
> 1/1/01 0:38 1/1/01 2:10
> 1/1/01 0:42 1/1/01 3:10
> 1/1/01 0:51 1/1/01 1:52
> 1/1/01 0:51 1/1/01 3:10

0
frank.kabel (11126)
4/15/2004 5:50:51 AM
Reply:

Similar Artilces:

WPF using Usercontrol in Listbox
I sent a previous post about binding to a usercontrol in a listbox / listview. I had assumed that I could use a usercontrol in the itemtemplate of a listbox but NO No NO (apologies to Amy Winehouse). When I attempt to use the usercontrol I get a compile error: Error 10 The tag 'RFolder' does not exist in XML namespace 'clr-namespace:Media_Player;assembly=MediaPlayer'. Line '1' Position '143'. C:\Visual Studio Projects\Media Player\Media Player\Windows\ArtistBrowswer.xaml 42 17 Media Player Now intellisence guides to add the control and if I...

Using Arrays With IF functions
Hi all, I am running into a problem. I am trying to sum values in a table using arrays and I get the VALUE error. My formula is something like this. {=SUM((A1:A10=A15)*(B1:B10=B15)*(C1:C10))} The values in A1:A10 and B1:B10 are manually inputted. The C1:C10 are calculated using an IF functions and has some greater than, less than equations within. My goal is to get the A and B columns to equal TRUE and then multiply the C column and the Sum them all together. I think my problem is the C column but not sure. Can this error be cause by the IF function? I even tried making a new table, linki...

Exporting mailing list to MS Word using Mail Merge
I am having trouble using the Mail Merge fuction on MS Word 2003. I cannot import list from saved .xls file in Excell 2003. I have full customer information fileds including name, address, and city/state fields but cannot import these fields to Word to create mailing labels.Need help! Thank You Each entry in the excel file needs to be on one row. Example: A2 = lastname1, B2 = firstname1, C2 = Address1, D2 = state1 ... A3 = lastname 2, B3 = firstname2 etc etc Make sure there is no other information on that sheet (except possibly column headers) Then in Word, using the mail me...

Using Form to update table
Hi I am a little lost. I have a form which has a subform. In the header of the form there is a combo box to selete numbers and a text box to enter a date (not current date) On the subform I have 2 text boxes which onces the 2nd textbox is populated another set is to come underneath ready for completion (continues form). On the main forms footer I have an update button which is to perform a task. What I am lost with is how can I get the subform to work as a continues form so once the 2nd text box is completed another set is ready for entering. and How do I then click on the button to fi...

Define a specific Printer using VBA (Access 2000)
Hello, I am using Access 2000. (No way to change Access version for now) I know that In Access XP can be done this request. I Would like to print to a specific Printer when I open a report in NormalView without changing the default printer (I know how to reset default Printer using VBA. There is an option in PageSetup of every report "Print to a specific printer" but I would like this option to be set for every report by VBA. I thank you in Advance Kon. ...

Two Questions Regarding Bills
In Money 2006, if you have a recurring deposit every two weeks set in the "Bills Summary" it would show both occurrences for the month. I have currently have Bill Settings set to 45 days and I can still only displays one occurrence of the deposit at a time. How can I fix this to properly show both deposits? Secondly, is there any way to print the "View bills and deposits on the calendar" view? I would like a hard copy of each month's calendar of bills and deposits. Thanks for any help!! ...

Missing in System Manager
Hi all I did a bad thing. Ran exmerge 2000, I have a 2000 exch server. Anyway, al seems normal, still have all mailboxs and public folders but when I go int exchange system manager, when you go to servers i cant see any of th mailboxs, its as if its all disappeared. When I go to protocols there i only x400 and nothing else like http pop smtp etc. OWA is still workin perfect however!? Same for pop smtp etc et Strange thing, mailboxs are working fine, i have a few gig worth of inf store 40+ users. Cant find them at all when I go into the usual spot, there simply is no mailboxs at all to be fou...

Voiding or Deleting Tracking Number
The shipping system correctly inserts a record with the tracking number into the SOP10107 table when a shipment is processed. But if the shipment is later voided I want to delete the record. Is there any reason I should not delete the record from the table? The sql looks fine, but the record does not delete. Is there anything special I should look for? You didnot list the SQL statement so I cannot comment on it. If the transaction has not been posted, you should be able to open the document in the application and delete the tracking number from there. If it is already posted, you ca...

count number of rows in 2 worksheets
HI, Anyone can help? I need a macro code to count number of used rows in two seperate worksheets and compare. If the number do not match error message shall appear "Sheet1 has (blank) number and Sheet2 has (blank) number". (blank) being the number of used rows per sheet. Can this be done? thanks! You have some answers in your first posting of this question. However, I'd like to tell you that your question is not defined very well. You have to tell us what you mean by "used rows". For example, if there are blank rows inside your data, are they to be coun...

Using Access 2003 Run-Time Version
I am trying to use the Access 2003 run-time and almost everything seems to be working just fine. I have noticed however that I seemed to have lost the ability to right click on a report and bring up the options of print/email.... When I right click using the run-time versionm nothing displays (as if the right button is disabled). When I right click using the regular mdb (full access 2003) all the options appear and I am able to print/email/etc at this time. My main question is is there any way to get the right click options back in a run-time version. Thanks That's correct: the run...

Created By SYSTEM
Does anyone know why we have activities that are created by users via the outlook client that are showing up as Created By SYSTEM if you do an advanced find and include that field? I have seen this happen when the name in your data does not match. For us we pulled names of our account reps from our SAP data and often that name does not match with the active directory name, so the system struggles to identify records and will assign records and activities that it can not determine the correct owner to System or CRM System. "Dan Stein" wrote: > Does anyone know why we have a...

VBA using an array
I have an array Array(100) and I want to set Array() = 10 for 1-100 Is there another way to set = 10 rather then using For i = 1 to 100 Array(i) = 10 Next I Thanks for your help Not really. But why worry? Even if that loop went to 100000 it'd be a fraction of a second! "Jeff" <Jeff@discussions.microsoft.com> wrote in message news:473989B9-140D-4E1C-B914-76FFA2B6BFC5@microsoft.com... > I have an array Array(100) > > and I want to set Array() = 10 for 1-100 > > Is there another way to set = 10 > > rather then using > > For i = 1 to 100 > A...

Excel Cell Format for Numberic Values
When I export data having 20 numeric characters, Excel will put in place a scientifc equation. The numbers are rounded off after 15 characters. Cannot get Excel to display all 20 numeric numbers. Any ideas on how to get Excel to read all 20 numbers in a cell? Hi not possible. Excel only supports 15 significant digits -- Regards Frank Kabel Frankfurt, Germany "Al" <Al@discussions.microsoft.com> schrieb im Newsbeitrag news:4324528D-BBAB-493C-A1B0-EC108BCB6571@microsoft.com... > When I export data having 20 numeric characters, Excel will put in place a > scientifc e...

Returns & lot numbers
Version: GP 8, SP 3 How does one do a purchasing return for an item on an invoice that has already been paid? How does one change a lot number on a received item when one has mistyped the lot number and the transaction has been posted? Thank you, M. E. Houston ...

Next Check Number Upon Company Setup
Hello: I have created a new company within my client's Great Plains 8.0 install. This includes the creation of a checkbook in Checkbook Maintenance, of course. I am concerned about the Next Check Number field. I remember that, when I first implemented Great Plains at this client and with their first company, the first payables check run grabbed all check numbers up through the check number specified in the Next Check Number field. I think the number was 100 and Great Plains printed checks for numbers 1-99 installed of starting at 100 and moving forward from there. I want to kno...

using two different outgoing servers
is it posible to set up one idenity to send mail by two smtp servers I use diferent isp at work & at home & wish to be able to send mail from both sites currently I have to either switch identies or change to outgoing server details "test" <test@hotmail.com> wrote in message news:433b4f0f$0$22741$afc38c87@news.optusnet.com.au... > is it posible to set up one idenity to send mail by two smtp servers > > I use diferent isp at work & at home & wish to be able to send mail from > both sites currently I have to either switch identies or change to &...

You can use Google Maps with an Acess DB
How to open a Google Maps showing a location from your database. Not sure if anyone is interested but it was asked a couple of days ago and I didn’t know a good answer - just in case you have looked at my 1st attempt :-) so I looked into it and came up with this. There will be other methods – so this is just one. To open Google Maps with the pointer placed on any location in your database. Notes: I assume you have a field called PostCode – if not then change the OnClick. The field must contain a real postcode (the link will fail if the postcode does not exist) This will work for anywh...

Date turns to a number
Hi I have a text field in a report with =Date() as the control source and format as medium date, the problem I have is when I send the report to Excel it turns the date into a number how do I stop this happening? Thanks Bob Just format the cell as a Date. Under the covers, VBA dates are eight byte floating point numbers, where the integer portion is the date as the number of days relative to 30 Dec, 1899 and the decimal portion is the time as a fraction of a day. Today is 27 Oct, 2007: if you ask Access for Format(Date, "0"), it will return 39382. If you plug that number ...

how to use "Findresource"
hi, i am facing some problem ,while retriving the resource from dll. That dll is a "esource only dll". Here is the code,what i am using. HMODULE hmod; HRSRC hr; /*here testdll.dll is the dll created for testing purpose and it has bitmap1.bmp resource*/ hmod=::LoadLibrary("testdll.dll"); hr=::Findresource(hr,"bitmap1.bmp",RT_BITMAP); if(hr==NULL) MessageBox("This is not working"); else MessageBox("This is Working"); This is code i have written ,but when ever i run,i get only "This is not working" messagbox. This is means we un...

Lists of Numbers
I have a spreadsheet with multiple columns of numbers. Some of these numbers are repeated throughout the columns and rows. I want to run a routine that checks each number in turn, if the number has not already been found, then it puts it on a list on a different page. If the number has been put on the list already, it just ignores it. Hopefully you will understand this!!!! Regards SantaUK Take a look here for a solution to a similar query from microsoft.public.excel.worksheet.functions http://makeashorterlink.com/?F2F0157A8 "SantaUK" <google@mmillar.co.uk> wrote in me...

Using DoCmd.FindRecord
Hi! My problem is basically in a search modal window, where the user is allowed to make a selection on which option to take. After selecting an option and then giving the data to search, the user clicks the "OK" button and the user gets taken back to the "Customer" form with the correct searched record. The function works fine except that every now and then it takes 3-5 seconds to close the search modal window and goes back to the "Customer" form after clicking the "OK" button Below is the actual code for the "OK" button: - - - - - - -...

How to set System.Web.Services.Protocols.SoapDocumentMethodAttribu
In a proxi class there is a the following definition(s) containing values for RequestNamespace, ResponseNamespace and so on. [System.Web.Services.Protocols.SoapDocumentMethodAttribute("http://tempuri.org/SaveSurvey", RequestNamespace="http://tempuri.org/", ResponseNamespace="http://tempuri.org/", Use=System.Web.Services.Description.SoapBindingUse.Literal, ParameterStyle=System.Web.Services.Protocols.SoapParameterStyle.Wrapped)] If I have a service that can be deployed to many sites / customers I would need to set these values for each site. Is there a w...

Syntax error when using names with apostrophe
i'm getting syntax error message for this button that tries to get name (of building) with apostrophe. Any idea how to solve this? Note that i must use name with apostrophe e.g. O'neal: Private Sub Command76_Click() On Error GoTo Err_Command76_Click Dim stDocName As String Dim stLinkCriteria As String stDocName = ChrW(1506) & ChrW(1491) & ChrW(1499) & ChrW(1503) & ChrW(32) & ChrW(1508) & ChrW(1512) & ChrW(1496) & ChrW(1497) & ChrW(32) & ChrW(1502) & ChrW(1489) & ChrW(1504) & ChrW(1492) stLin...

send mail fail using cable modem
I cannot send mail using a cable modem. receive works fine. I installed outlook express and it can receive/send using same account as outlook, but outlook fails to send. do not get ans error message, email just disappears in a "black hole" -- outlook version 9.0.0.3821 -- using a linksys LNE100TX nic Surely it is because the email account (SMTP server) is incorrectly setup. Re-read your mail service provider's instructions. Hope this is useful to you. Let us know. rms curtis wrote: > I cannot send mail using a cable modem. receive works > fine. I install...

email AWAYS from one account even I tell it to use another email
I'm using Outlook 2003 fully patched, ok I haven't patched the French grammer checker and the infopath (neither of which I use) on Win XP Home SP2 fully patched. Anyhow, all my emails going out are showing up in the sent box with an incorrect email address. When I Create a New Message, one of the first things I do is select which email account the email message is to go out from. I'm selecting my pop3 email account. (The check mark is beside the pop3 account!) I type in my message, press the send button, and the email shows up in my sent box with a hotmail account on ...