Go Back  DVD Talk Forum > General Discussions > Tech Talk
Reload this Page >

excel help please

Tech Talk Discuss PC Hardware, Software, Internet and Other Technology

excel help please

Old 10-08-01, 06:34 PM
DVD Talk Limited Edition
Thread Starter
Join Date: Jan 2001
Posts: 6,635
Likes: 0
Received 0 Likes on 0 Posts
excel help please

I have a problem i need to do with excel

column a is an array of times individuals enter a parking lot

in column b and c I have times in ten minute intervals (b1=1:00, c1=1:10, b2=1:10, c2=1:20 etc)

I need to count the number of people (from column A) that arrive within the time interval of b1:c1. answer to be shown in d1 etc...

I think the proper function is COUNTIF(), but I am not getting the function to work properly (probably due to syntax). if anyone can help me, that would be great...

Thanks in advance...
Old 10-08-01, 07:19 PM
DVD Talk Legend
Join Date: Oct 2000
Location: Cincinnati, OH
Posts: 10,059
Likes: 0
Received 0 Likes on 0 Posts
countif will count the number of times that something appears depending on the critieria that you enter in the brackets of the function

what are you putting in the brackets as your critera?

it sounds to me as if you need to change the time intervals to something that excel can read like 60-70, 70-80 minutes.
Old 10-08-01, 07:37 PM
DVD Talk Hero
Join Date: Jul 2001
Location: MI
Posts: 25,060
Likes: 0
Received 0 Likes on 0 Posts
Are you letting Excel store the times in col. A. Excel's native format for time/date is a modified julian date where each integer represents one day, and time within the day is the fraction of the number. What you see in time-date format is just a display. Therefore your limits in your countif() statement may be incorrect.
Old 10-09-01, 11:46 AM
DVD Talk Limited Edition
Thread Starter
Join Date: Jan 2001
Posts: 6,635
Likes: 0
Received 0 Likes on 0 Posts
I understand that the time code is just a display, but it actually acts as a decimal number, I can convert it easily. for example, 9:50 AM is 0.410 (lets say this is A1)

I think the problem is in the criteria portion of the brackets. I was trying to put statements such as

.4 < A1:A100 < = .5 (without the spaces, for some reason it wont post exactly)

(saying I want a count of cells between A1:A100 that have values greater than 0.4 but less than .5). Then I want to know how many are between .5 and .6 ...

I have been able to complete my task by making a big matrix/table with the if() command, but was looking for a shortcut as I have many tables to do keeping in mind I have 144 time intervals (or columns needed for the the matrix).

Thanks for your help all...

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is On
Trackbacks are Off
Pingbacks are Off
Refbacks are Off

Thread Tools
Search this Thread

Archive - Advertising - Cookie Policy - Privacy Statement - Terms of Service - Do Not Sell My Personal Information

Copyright 2018 MH Sub I, LLC dba Internet Brands. All rights reserved. Use of this site indicates your consent to the Terms of Use.