Home |
Search |
Today's Posts |
#1
|
|||
|
|||
use sumif with array
I'm trying to model patient flow delays through a receptionist and a nurse to
see a doctor. I've got a two dimentional array, with the distribution of delays created by a receptionist across the top, and those created by nursing preparation down the side. In each cell, I have the percentage of patients experiencing that combination of delays: Receptionist Causes 2min 3min 4min 5min 6min N 4min 2% 3% 6% u 6min 8% 11% r 8min 5% s etc. I want to create a histogram from this table showing the total percentage of patients experiencing each level of delay. Then, I can go back and design process changes or staffing changes to reduce those delays in a predictable way. Also, I can use the output as input for queueing models at the next step (say, the doc, the lab, x-ray, an expensive piece of equipment, etc.) Can I use a SUMIF function to add the respective table headers (top & side) into a single sum and use that sum for the if condition? Or do I have to write a bunch of VBA language? Thanks in advance. |
#2
|
|||
|
|||
Are you looking for each combination that equals a certain percentage (i.e.
all the cells at 2%) or for each way the minutes add up (i.e. 2min + 8min = 10; 6min + 4min = 10)..? If the latter, the first thing you need to do is change your "min" cells to actual minute values, using a custom format to display them how you'd like. That way, you have actual numbers to work with instead of labels, and we can move on to the next step. Let us know how much help you need here... "pdberger" wrote in message ... I'm trying to model patient flow delays through a receptionist and a nurse to see a doctor. I've got a two dimentional array, with the distribution of delays created by a receptionist across the top, and those created by nursing preparation down the side. In each cell, I have the percentage of patients experiencing that combination of delays: Receptionist Causes 2min 3min 4min 5min 6min N 4min 2% 3% 6% u 6min 8% 11% r 8min 5% s etc. I want to create a histogram from this table showing the total percentage of patients experiencing each level of delay. Then, I can go back and design process changes or staffing changes to reduce those delays in a predictable way. Also, I can use the output as input for queueing models at the next step (say, the doc, the lab, x-ray, an expensive piece of equipment, etc.) Can I use a SUMIF function to add the respective table headers (top & side) into a single sum and use that sum for the if condition? Or do I have to write a bunch of VBA language? Thanks in advance. |
#3
|
|||
|
|||
Thanks for your interest. Actually, in the worksheet, each header cell is
numeric, and represents the number of minutes. What I'm trying to do is create a histogram of the sums of the waiting times experienced by a segment of the patient population. So, if a patient waits 1 minute for the receptionist & 7 minutes for the nurse, or 2 for recep + 6 for nurse, or 3 for each, he doesn't care and (for these purposes) neither do I. I want to see what happens to the "output" of this two-step system -- patients prepped and ready to see the doctor -- if (for example) I reduce the variation of patient prep times at either step. Again, thanks in advance for any help you can offer. "JPW" wrote: Are you looking for each combination that equals a certain percentage (i.e. all the cells at 2%) or for each way the minutes add up (i.e. 2min + 8min = 10; 6min + 4min = 10)..? If the latter, the first thing you need to do is change your "min" cells to actual minute values, using a custom format to display them how you'd like. That way, you have actual numbers to work with instead of labels, and we can move on to the next step. Let us know how much help you need here... "pdberger" wrote in message ... I'm trying to model patient flow delays through a receptionist and a nurse to see a doctor. I've got a two dimentional array, with the distribution of delays created by a receptionist across the top, and those created by nursing preparation down the side. In each cell, I have the percentage of patients experiencing that combination of delays: Receptionist Causes 2min 3min 4min 5min 6min N 4min 2% 3% 6% u 6min 8% 11% r 8min 5% s etc. I want to create a histogram from this table showing the total percentage of patients experiencing each level of delay. Then, I can go back and design process changes or staffing changes to reduce those delays in a predictable way. Also, I can use the output as input for queueing models at the next step (say, the doc, the lab, x-ray, an expensive piece of equipment, etc.) Can I use a SUMIF function to add the respective table headers (top & side) into a single sum and use that sum for the if condition? Or do I have to write a bunch of VBA language? Thanks in advance. |
#4
|
|||
|
|||
Well, we have two options. If your minute values always progress in the same order (you don't insert or delete rows or columns) it
may be possible to use a convoluted combination of CHOOSE and/or lookup formulas to create a list of totals. The other option, which will work no matter what, would be to create a VBA procedure. Personally I'd lean toward the latter, but that's only because it's what I'm more comfortable with. "pdberger" wrote in message ... Thanks for your interest. Actually, in the worksheet, each header cell is numeric, and represents the number of minutes. What I'm trying to do is create a histogram of the sums of the waiting times experienced by a segment of the patient population. So, if a patient waits 1 minute for the receptionist & 7 minutes for the nurse, or 2 for recep + 6 for nurse, or 3 for each, he doesn't care and (for these purposes) neither do I. I want to see what happens to the "output" of this two-step system -- patients prepped and ready to see the doctor -- if (for example) I reduce the variation of patient prep times at either step. Again, thanks in advance for any help you can offer. "JPW" wrote: Are you looking for each combination that equals a certain percentage (i.e. all the cells at 2%) or for each way the minutes add up (i.e. 2min + 8min = 10; 6min + 4min = 10)..? If the latter, the first thing you need to do is change your "min" cells to actual minute values, using a custom format to display them how you'd like. That way, you have actual numbers to work with instead of labels, and we can move on to the next step. Let us know how much help you need here... "pdberger" wrote in message ... I'm trying to model patient flow delays through a receptionist and a nurse to see a doctor. I've got a two dimentional array, with the distribution of delays created by a receptionist across the top, and those created by nursing preparation down the side. In each cell, I have the percentage of patients experiencing that combination of delays: Receptionist Causes 2min 3min 4min 5min 6min N 4min 2% 3% 6% u 6min 8% 11% r 8min 5% s etc. I want to create a histogram from this table showing the total percentage of patients experiencing each level of delay. Then, I can go back and design process changes or staffing changes to reduce those delays in a predictable way. Also, I can use the output as input for queueing models at the next step (say, the doc, the lab, x-ray, an expensive piece of equipment, etc.) Can I use a SUMIF function to add the respective table headers (top & side) into a single sum and use that sum for the if condition? Or do I have to write a bunch of VBA language? Thanks in advance. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
Modify SumIF... Array Formula | Excel Worksheet Functions | |||
Modify SumIF... Array Formula | Excel Worksheet Functions | |||
sumif and array formulas | Excel Worksheet Functions | |||
SUM(IF( Array to avoid #NUM! values | Excel Worksheet Functions |