ExcelBanter - Demonic formula

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Demonic formula (https://www.excelbanter.com/excel-discussion-misc-queries/83495-demonic-formula.html)

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! :)

--
robotfighter
------------------------------------------------------------------------
robotfighter's Profile: http://www.excelforum.com/member.php...o&userid=33549
View this thread: http://www.excelforum.com/showthread...hreadid=533327

Maybe...

=sumproduct(--(calltimetime(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.

robotfighter wrote:

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! :)

--
robotfighter
------------------------------------------------------------------------
robotfighter's Profile: http://www.excelforum.com/member.php...o&userid=33549
View this thread: http://www.excelforum.com/showthread...hreadid=533327

--

Dave Peterson

And for 2 to 3 pm (oopsie):

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

Dave Peterson wrote:

Maybe...

=sumproduct(--(calltimetime(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.

robotfighter wrote:

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! :)

--
robotfighter
------------------------------------------------------------------------
robotfighter's Profile: http://www.excelforum.com/member.php...o&userid=33549
View this thread: http://www.excelforum.com/showthread...hreadid=533327

--

Dave Peterson

--

Dave Peterson

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

--
robotfighter
------------------------------------------------------------------------
robotfighter's Profile: http://www.excelforum.com/member.php...o&userid=33549
View this thread: http://www.excelforum.com/showthread...hreadid=533327

Maybe a sample implementation of Dave's suggestion
(slightly adapted) would help illustrate that
it should work fine:

http://cjoint.com/?erfsK7SzLs
robotfighter_misc.xls
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

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

robotfighter wrote:

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

--
robotfighter
------------------------------------------------------------------------
robotfighter's Profile: http://www.excelforum.com/member.php...o&userid=33549
View this thread: http://www.excelforum.com/showthread...hreadid=533327

--

Dave Peterson

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 are
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 have
been received - In the above example, I am attempting to retrive those
which were received between 1:00 AM and 2:00 AM (using a military time
format).

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

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

Thanks again. ;)

--
robotfighter
------------------------------------------------------------------------
robotfighter's Profile: http://www.excelforum.com/member.php...o&userid=33549
View this thread: http://www.excelforum.com/showthread...hreadid=533327

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.

--
robotfighter
------------------------------------------------------------------------
robotfighter's Profile: http://www.excelforum.com/member.php...o&userid=33549
View this thread: http://www.excelforum.com/showthread...hreadid=533327

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(--(CallTimetime(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 he
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

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

robotfighter wrote:

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 are
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 have
been received - In the above example, I am attempting to retrive those
which were received between 1:00 AM and 2:00 AM (using a military time
format).

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

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

Thanks again. ;)

--
robotfighter
------------------------------------------------------------------------
robotfighter's Profile: http://www.excelforum.com/member.php...o&userid=33549
View this thread: http://www.excelforum.com/showthread...hreadid=533327

--

Dave Peterson

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

--
robotfighter
------------------------------------------------------------------------
robotfighter's Profile: http://www.excelforum.com/member.php...o&userid=33549
View this thread: http://www.excelforum.com/showthread...hreadid=533327

IT WORKED!!!!!!!!

Thanks!!!!! You just saved me tons of time! ;) :rolleyes: :)

--
robotfighter
------------------------------------------------------------------------
robotfighter's Profile: http://www.excelforum.com/member.php...o&userid=33549
View this thread: http://www.excelforum.com/showthread...hreadid=533327