use sumif with array

[pdberger]

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.

 

[JPW]

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]

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]

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.