Using Min Formula with Multiple Criteria Excluding Zeros
 

Hello:

I am working in Excel 2007. I have columns with times (half-hour
intervals), calls received and the workgroup the calls were received in. I
set up a pivot table to calculate the sum of the calls and the count of the
calls then did a separate row with the count excluding zeroes by time. I am
having difficulty trying to figure out a formula to calculate the grand total
for the minimum calls of the included workgroups excluding zeroes by time
interval. I need the minimum for each of the workgroup that make up the
worksheet then a grand total as the results would be skewed otherwise. The
result I keep getting with the fomulas I tried is skewed. E.g., in the
interval 9:00a where Workgroup 1 has 1, workgroup 2 has a minimum of 3 and
workgroup 3 has a minimum of 10 - result should be I am getting a current
result of 1 as opposed to the desired current result of 14. Any assistance
would be greatly appreciated!

Time Calls Workgroup


08:00:00 0 Workgroup 1
08:30:00 4 Workgroup 1
09:00:00 11 Workgroup 1
09:30:00 7 Workgroup 1
08:00:00 0 Workgroup 2
08:30:00 4 Workgroup 2
09:00:00 11 Workgroup 2
09:30:00 7 Workgroup 2
08:00:00 0 Workgroup 3
08:30:00 4 Workgroup 3
09:00:00 11 Workgroup 3
09:30:00 7 Workgroup 3


--
LB

Using Min Formula with Multiple Criteria Excluding Zeros
 

Assuming your data as posted is within A2:C13
Assume you have listed in E2:G2 down
the 3 input variables**: 9:00:00, 10:00:00, Workgroup 1
**Start-times, End-times, Workgroup

Then this expression, array-entered* in H2:
=MIN(IF((A$2:A$13=E2)*(A$2:A$13<F2)*(C$2:C$13=G2) ,B$2:B$13))
will return the required min "Calls" for the 3 inputs set in E2:G2,
ie the minimum calls for workgroup 1 in the 9 am interval
(start-times are inclusive [=E2], endtimes exclusive [<F2])

Copy H2 down as required to return correspondingly for other input sets.
Since we are checking "Time" against valid "Start-times" to "End-Times",
think the expression as-is should suffice w/o having to additionally check
for Time = zero. But do test it out for yourself over there, and check that
it returns the expected results. Adapt the ranges to suit.

*Press CTRL+SHIFT+ENTER to confirm the formula
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---
"LucyB4God" wrote:
I am working in Excel 2007. I have columns with times (half-hour
intervals), calls received and the workgroup the calls were received in. I
set up a pivot table to calculate the sum of the calls and the count of the
calls then did a separate row with the count excluding zeroes by time. I am
having difficulty trying to figure out a formula to calculate the grand total
for the minimum calls of the included workgroups excluding zeroes by time
interval. I need the minimum for each of the workgroup that make up the
worksheet then a grand total as the results would be skewed otherwise. The
result I keep getting with the fomulas I tried is skewed. E.g., in the
interval 9:00a where Workgroup 1 has 1, workgroup 2 has a minimum of 3 and
workgroup 3 has a minimum of 10 - result should be I am getting a current
result of 1 as opposed to the desired current result of 14. Any assistance
would be greatly appreciated!

Time Calls Workgroup


08:00:00 0 Workgroup 1
08:30:00 4 Workgroup 1
09:00:00 11 Workgroup 1
09:30:00 7 Workgroup 1
08:00:00 0 Workgroup 2
08:30:00 4 Workgroup 2
09:00:00 11 Workgroup 2
09:30:00 7 Workgroup 2
08:00:00 0 Workgroup 3
08:30:00 4 Workgroup 3
09:00:00 11 Workgroup 3
09:30:00 7 Workgroup 3

Using Min Formula with Multiple Criteria Excluding Zeros
 

Hi Max:

First, please excuse me - this is my first post and I was unsure how to work
the reply and replied incorrectly! I so appreciate your response especially
so quickly. Your help assisted in with getting the minimum values except if
the value is zero it is still not calculating correctly. I now have

=MIN(IF((A$16:A$28=E15)*(C$16:C$28=G15),B$16:B$28) )+MIN(IF((A$16:A$28=E15)*(C$16:C$28=A43),B$16:B$28 ))+MIN(IF((A$16:A$28=E15)*(C$16:C$28=A44),B$16:B$2 8))


Any idea on how to exclude the zero?

--
LB


"Max" wrote:

Assuming your data as posted is within A2:C13
Assume you have listed in E2:G2 down
the 3 input variables**: 9:00:00, 10:00:00, Workgroup 1
**Start-times, End-times, Workgroup

Then this expression, array-entered* in H2:
=MIN(IF((A$2:A$13=E2)*(A$2:A$13<F2)*(C$2:C$13=G2) ,B$2:B$13))
will return the required min "Calls" for the 3 inputs set in E2:G2,
ie the minimum calls for workgroup 1 in the 9 am interval
(start-times are inclusive [=E2], endtimes exclusive [<F2])

Copy H2 down as required to return correspondingly for other input sets.
Since we are checking "Time" against valid "Start-times" to "End-Times",
think the expression as-is should suffice w/o having to additionally check
for Time = zero. But do test it out for yourself over there, and check that
it returns the expected results. Adapt the ranges to suit.

*Press CTRL+SHIFT+ENTER to confirm the formula
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---
"LucyB4God" wrote:
I am working in Excel 2007. I have columns with times (half-hour
intervals), calls received and the workgroup the calls were received in. I
set up a pivot table to calculate the sum of the calls and the count of the
calls then did a separate row with the count excluding zeroes by time. I am
having difficulty trying to figure out a formula to calculate the grand total
for the minimum calls of the included workgroups excluding zeroes by time
interval. I need the minimum for each of the workgroup that make up the
worksheet then a grand total as the results would be skewed otherwise. The
result I keep getting with the fomulas I tried is skewed. E.g., in the
interval 9:00a where Workgroup 1 has 1, workgroup 2 has a minimum of 3 and
workgroup 3 has a minimum of 10 - result should be I am getting a current
result of 1 as opposed to the desired current result of 14. Any assistance
would be greatly appreciated!

Time Calls Workgroup


08:00:00 0 Workgroup 1
08:30:00 4 Workgroup 1
09:00:00 11 Workgroup 1
09:30:00 7 Workgroup 1
08:00:00 0 Workgroup 2
08:30:00 4 Workgroup 2
09:00:00 11 Workgroup 2
09:30:00 7 Workgroup 2
08:00:00 0 Workgroup 3
08:30:00 4 Workgroup 3
09:00:00 11 Workgroup 3
09:30:00 7 Workgroup 3

Using Min Formula with Multiple Criteria Excluding Zeros
 

Lucy
Any idea on how to exclude the zero?

Tracing back to my earlier response, here's a tweak to my earlier expression
to now exclude Calls = 0 from the MIN calcs as well (think this is what you
are trying to exclude)

Try this revised expression, array-entered in H2, then copied down:
=MIN(IF((A$2:A$13=E2)*(A$2:A$13<F2)*(C$2:C$13=G2) *(B$2:B$130),B$2:B$13))
which should now yield the desired results

The additional check on the Calls col (col B) to exclude zeros is this part:
...*(B$2:B$130)

Please note that if the expression is NOT correctly array-entered, it'll
return incorrect results. Visually confirm that the array-entering is
correctly done by looking at the formula in the formula bar post
confirmation, it should appear wrapped by curly braces: { ... }. If it
doesn't have the curlies, that means it wasan't array-entered. Click inside
the formula bar, re-do the CTRL+SHIFT+ENTER again to re-confirm the formula.
Copy H2 down only after ensuring that it is correctly array-entered.

For easy ref, here's a working sample illustrating the above:
http://freefilehosting.net/download/429ml
Min calls other than zero for a given wkgrp n time interval.xls
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---

Using Min Formula with Multiple Criteria Excluding Zeros
 

Max:

Again thank you for all your help. I was so stumped and it was a tremendous
help in getting me to the right place. Last night I ended up tweaking your
array formulas for the columns and adding a IF statement. I see it is very
close to your recommendation and the good news it worked.
MIN(IF((A$16:A$27=E15)*(C$16:C$27=G15),(IF(B$16:B$ 270,B$16:B27)))) I will
remember in the future the second IF is not needed.

Have a great day.

--
LB


"Max" wrote:

Lucy
Any idea on how to exclude the zero?

Tracing back to my earlier response, here's a tweak to my earlier expression
to now exclude Calls = 0 from the MIN calcs as well (think this is what you
are trying to exclude)

Try this revised expression, array-entered in H2, then copied down:
=MIN(IF((A$2:A$13=E2)*(A$2:A$13<F2)*(C$2:C$13=G2) *(B$2:B$130),B$2:B$13))
which should now yield the desired results

The additional check on the Calls col (col B) to exclude zeros is this part:
..*(B$2:B$130)

Please note that if the expression is NOT correctly array-entered, it'll
return incorrect results. Visually confirm that the array-entering is
correctly done by looking at the formula in the formula bar post
confirmation, it should appear wrapped by curly braces: { ... }. If it
doesn't have the curlies, that means it wasan't array-entered. Click inside
the formula bar, re-do the CTRL+SHIFT+ENTER again to re-confirm the formula.
Copy H2 down only after ensuring that it is correctly array-entered.

For easy ref, here's a working sample illustrating the above:
http://freefilehosting.net/download/429ml
Min calls other than zero for a given wkgrp n time interval.xls
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:363 Subscribers:64
xdemechanik
---

Using Min Formula with Multiple Criteria Excluding Zeros
 

Welcome, glad it helped.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:20,500 Files:365 Subscribers:65
xdemechanik
---
"LucyB4God" wrote in message
...
Max:

Again thank you for all your help. I was so stumped and it was a
tremendous
help in getting me to the right place. Last night I ended up tweaking
your
array formulas for the columns and adding a IF statement. I see it is
very
close to your recommendation and the good news it worked.
MIN(IF((A$16:A$27=E15)*(C$16:C$27=G15),(IF(B$16:B$ 270,B$16:B27)))) I
will
remember in the future the second IF is not needed.

Have a great day.

--
LB