Demonic formula

R

robotfighter

Hi guys,

I've been browsing through these forums for hours, but I can't find the
answer to a problem I am having with a formula...

I have a table which looks like this:

1 Date Time Day
2 1/1/2006 21:21 Sun
3 1/1/2006 22:27 Sun
4 1/1/2006 20:13 Mon
5 1/1/2006 22:13 Mon

I am attempting to create a formula where items which were received
within a specific timeframe -AND- on a specific day will be counted.
For instance: what are the amount of items which occurred on a -Monday-
_between_ -1:00 PM -and -2:00 PM-.

The following formula is the closest I could get, but unfortunately
does not produce the desired results (please note the items in red
indicate named ranges):

=SUMPRODUCT(TEXT(CallTime,"hh:mm")>"02:00")*(TEXT(CallTime,"hh:mm")<"03:00"))/(COUNTIF(Day,"Mon"))

Your help would be IMMENSELY appreciated! :)
 
D

Dave Peterson

Maybe...

=sumproduct(--(calltime>time(2,0,0)),
--(calltime<time(3,0,0)),
--(text(CallDate,"ddd")="mon")))

(all one cell)

I wouldn't use Day as a range name--it's very similar to the =day() worksheet
function.
 
D

Dave Peterson

And for 2 to 3 pm (oopsie):

=sumproduct(--(calltime>time(14,0,0)),
--(calltime<time(15,0,0)),
--(text(CallDate,"ddd")="mon")))
 
R

robotfighter

Unfortunately, that formula did not work for me. Using your guidelines
the formula returned "0"
 
D

Dave Peterson

If Max's workbook doesn't help, maybe you should post what your range names are
and what they hold.
 
R

robotfighter

I applied the sample formula to my spreadsheet, but it just didn't work
Perhaps because I am using Excel 2003, thus some formula codes ar
modified in the latest version?

Here's my current formula:

=SUMPRODUCT(--(TEXT(CallTime,"hh:mm")>"01:00"),--(TEXT(CallTime,"hh:mm")>"02:00"))--(COUNTIF(WeekDay,"Mon"))

"CallTime" is a range (column) listing the various times calls hav
been received - In the above example, I am attempting to retrive thos
which were received between 1:00 AM and 2:00 AM (using a military tim
format).

"WeekDay" represents a range (column) displaying the days of each weee
during which each call was received. In the above example, I am lookin
for all calls received on Mondays.

Therefore, In the above example, I am attempting to combine thes
formulas to acquire all calls occurring on a Monday between the hour
of 1:00 AM and 2:00 AM. I would like to ultimately determine th
average amount of calls received each day of the week, broken down int
each hour of the day.

Thanks again. ;
 
R

robotfighter

I applied the sample formula to my spreadsheet, but it just didn't work
Perhaps because I am using Excel 2003, thus some formula codes ar
modified in the latest version?

Here's my current formula:

=SUMPRODUCT(--(TEXT(CallTime,"hh:mm")>"01:00"),--(TEXT(CallTime,"hh:mm")>"02:00"))--(COUNTIF(WeekDay,"Mon"))

"CallTime" is a range (column) listing the various times calls hav
been received - In the above example, I am attempting to retrive thos
which were received between 1:00 AM and 2:00 AM (using a military tim
format).

"WeekDay" represents a range (column) displaying the days of each weee
during which each call was received. In the above example, I am lookin
for all calls received on Mondays.

Therefore, In the above example, I am attempting to combine thes
formulas to acquire all calls occurring on a Monday between the hour
of 1:00 AM and 2:00 AM. I would like to ultimately determine th
average amount of calls received each day of the week, broken down int
each hour of the day.

Thanks again. ;
 
R

robotfighter

Also, the use of ",--" doesn't seem to work for me. Is this code
intended to be used to combine formulas? I looked it up and the
explanation didn't make sense.
 
D

Dave Peterson

First, I wouldn't use Weekday as a range name either. It's too close to the the
worksheet function =weekday().

But is that Weekday range really text containing "Mon" or is a date just
formatted to show the day of week?

If it's really text:

=SUMPRODUCT(--(TEXT(CallTime,"hh:mm")>"01:00"),
--(TEXT(CallTime,"hh:mm")<"02:00"),
--(CallDays="Mon"))

(I changed the range name in my test workbook.)

If it's really a date formatted to show "Mon":

=SUMPRODUCT(--(TEXT(CallTime,"hh:mm")>"01:00"),
--(TEXT(CallTime,"hh:mm")<"02:00"),
--(text(CallDays,"ddd")="Mon"))

or

=SUMPRODUCT(--(TEXT(CallTime,"hh:mm")>"01:00"),
--(TEXT(CallTime,"hh:mm")<"02:00"),
--(weekday(CallDays)=2))

This won't matter, but I like to use the =Time() function:

=SUMPRODUCT(--(CallTime>time(1,0,0),
--(calltime<time(2,0,0),
--(CallDays="Mon"))

And watch your > signs, too. You > 1:00 am and > 2:00 am. Not what you
described in your message.

Adjust the ranges to match--but you can't use whole columns.

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html
 
R

robotfighter

Thanks for taking the time to explain this in such thorough detail. I
will post back as soon as I try your formulas.
 
Top