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