Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 --- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help! Minimum Excluding zeros across multiple sheets | Excel Worksheet Functions | |||
Average from three different coworkers excluding zeros formula? | Excel Discussion (Misc queries) | |||
Count if, excluding zeros | Excel Discussion (Misc queries) | |||
Averages excluding zeros | Excel Discussion (Misc queries) | |||
EXcluding Zeros from the average in a row | Excel Discussion (Misc queries) |