COUNTIF Function

[Glen Parry]

I am attempting to obtain details of a range of call
response times, that fit within a maximum time. The cells
I am attempting to select this data from are formatted to
give the results in seconds, though the results just need
to be numeric. It would appear that the '=COUNTIF'function
is what I should be using and I have tried the following
formula:
=COUNTIF(N2:N65,"<=10")

Unfortunately, for a range of 21 filled cells, of which
only 13 match the criteria (i.e. the value is less than or
equal to 10 seconds) the formula gives a result of 64
matches.

The version of Excel in use is 97 SR-2.

Thanks in advance for any ideas that can be come up with
as to what is going wrong.

 

[Peo Sjoblom]

Try either

=COUNTIF(N2:N65,"<="&"0:0:10")

or

=COUNTIF(N2:N65,"<="&TIME(,,10))

assuming you have seconds in excel time format in your range

10 in excel time format is 10 days = 10*24*60*60 = 864000 seconds

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[Domenic]

Time is represented as a fraction of the day. Since there are 86,400
seconds in a day (24*60*60), 10 seconds would equal 0.000115741
(10/86,400). Therefore, the formula would be constructed as follows:

=COUNTIF(N2:N65,"<="&10/(24*60*60))

or

=COUNTIF(N2:N65,"<="&10/86400)

Hope this helps!

 

[CLR]

It sounds like you have formulas in N2:N65 returning zero if not
active.........you're probably counting the zeros.......change your formula
to give "" instead of 0............

Vaya con Dios,
Chuck, CABGx3

 

[Domenic]

Try either

=COUNTIF(N2:N65,"<="&"0:0:10")

Ahh yes...definitely easier to work with!

 

[Glen Parry]

Thanks for the suggestions, but still not giving anything
like the expected result.

Basically, I'm taking call logger data, in CSV format, and
importing it into an Excel worksheet. It's then being
formatted; so that it's actually intelligible, and then
the time a call is received by the system is being
deducted from the time it is acknowledged by an operative.
Although the initial dates are given in the format
dd/mm/yyyy hh:mm:ss AM/PM, the results of the subtraction
need only be given in seconds. A series of such results
are what fills the cells in the range N2:N65; although not
all cells in this range will be filled for any given date,
allowance has to be made for the absolute maximum number
of any given type of call that could be expected, & empty
cells are shown as blank (i.e. with no zero values).

I have checked the accuracy of the subtractions & they are
correct; we are talking a matter of 2 - 21 seconds. Of the
21 test entries being tried, 4 actually fit withing the
acceptance criteria of the =COUNTIF formula being tried,
but even having tried all the suggestions the best result
being acheived is a return of 52 valid entries.

Basically, just want to get this out of the way so that we
can actually get on with keepng the telephone & radio
systems running.

Thanks in advance for any further ideas.

Glen Parry