Histogram Bins Created From Sum Of Two Cells

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, kind of like this:

Receptionist Causes
2min 3min 4min 5min 6min
N 4min 2% 3% 6%
u 6min 8% 11%
r 8min 5%
s
etc.

The patient doesn't care (rightfully) whether the delay is created by the
receptionist or the nurse so I want to create an output histogram showing the
SUM of the two delays. Two patients wait eight minutes, but one delay was
caused mostly by the receptionist, and the other mostly by the nurse. He
doesn't care. I want to create a histogram from this table showing the
percentage of patients experiencing each level of total 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.)

I've tried using SUMIF kinds of approaches, but have failed. I'm a VERY
novice VBA writer, so any help would be appreciated.

Thanks in advance.

Is it possible you have the "raw" data somewhere in your spreadsheet? It
would be possible to create the frequency distribution from the array you
have, but it would be complicated. But your array had to have come from a
list of data from individual visits, I am sure - and it would be so much
easier to do it from that list - perhaps even with worksheet functions so you
won't need VBA. So do you have the raw data, and if so how is it set up
(e.g. what are the columns?)

"pdberger" wrote:

> 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, kind of like this:
>
> Receptionist Causes
> 2min 3min 4min 5min 6min
> N 4min 2% 3% 6%
> u 6min 8% 11%
> r 8min 5%
> s
> etc.
>
> The patient doesn't care (rightfully) whether the delay is created by the
> receptionist or the nurse so I want to create an output histogram showing the
> SUM of the two delays. Two patients wait eight minutes, but one delay was
> caused mostly by the receptionist, and the other mostly by the nurse. He
> doesn't care. I want to create a histogram from this table showing the
> percentage of patients experiencing each level of total 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.)
>
> I've tried using SUMIF kinds of approaches, but have failed. I'm a VERY
> novice VBA writer, so any help would be appreciated.
>
> Thanks in advance.
>

Actually, I don't have the 'raw' data anywhere. This table is developed by
applying two statistical distributions to each other. The original data is
available to when I developed the distributions, but it's almost impossible
to follow a set of patients through a network like a medical office. It's
just too complex.

I did figure out a 'workaround'. I created a second table, the same size as
the first, consisting of the sums of the two delays. Then I was able to
apply the criteria to those table entries, figuring the sums from the first
table's entries. Not elegant, but it seems to get it done.

If there's a prettier way, I'd love to know about it.

Thanks for your interest.

"K Dales" wrote:

> Is it possible you have the "raw" data somewhere in your spreadsheet? It
> would be possible to create the frequency distribution from the array you
> have, but it would be complicated. But your array had to have come from a
> list of data from individual visits, I am sure - and it would be so much
> easier to do it from that list - perhaps even with worksheet functions so you
> won't need VBA. So do you have the raw data, and if so how is it set up
> (e.g. what are the columns?)
>
> "pdberger" wrote:
>
> > 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, kind of like this:
> >
> > Receptionist Causes
> > 2min 3min 4min 5min 6min
> > N 4min 2% 3% 6%
> > u 6min 8% 11%
> > r 8min 5%
> > s
> > etc.
> >
> > The patient doesn't care (rightfully) whether the delay is created by the
> > receptionist or the nurse so I want to create an output histogram showing the
> > SUM of the two delays. Two patients wait eight minutes, but one delay was
> > caused mostly by the receptionist, and the other mostly by the nurse. He
> > doesn't care. I want to create a histogram from this table showing the
> > percentage of patients experiencing each level of total 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.)
> >
> > I've tried using SUMIF kinds of approaches, but have failed. I'm a VERY
> > novice VBA writer, so any help would be appreciated.
> >
> > Thanks in advance.
> >

Yes, I think the way you tackled it is about the only way - you need somehow
to work with the sums of the times - the table will have duplicates (e.g. 2
min + 6 min = 4 min + 4 min) but as long as you account for that correctly
you can then figure the overall distribution.

And, by the way, I work at a hospital monitoring performance statistics. I
have a "live" graph, fed by frequent updates from registration data, that
allows us to monitor the waiting time in our emergency room. Yes, it is
complex, but as much as possible I like to leave the original data on a sheet
within my workbooks, since you never know when you will want to (or be asked
to) look at the data in a different way.

"pdberger" wrote:

> Actually, I don't have the 'raw' data anywhere. This table is developed by
> applying two statistical distributions to each other. The original data is
> available to when I developed the distributions, but it's almost impossible
> to follow a set of patients through a network like a medical office. It's
> just too complex.
>
> I did figure out a 'workaround'. I created a second table, the same size as
> the first, consisting of the sums of the two delays. Then I was able to
> apply the criteria to those table entries, figuring the sums from the first
> table's entries. Not elegant, but it seems to get it done.
>
> If there's a prettier way, I'd love to know about it.
>
> Thanks for your interest.
>
> "K Dales" wrote:
>
> > Is it possible you have the "raw" data somewhere in your spreadsheet? It
> > would be possible to create the frequency distribution from the array you
> > have, but it would be complicated. But your array had to have come from a
> > list of data from individual visits, I am sure - and it would be so much
> > easier to do it from that list - perhaps even with worksheet functions so you
> > won't need VBA. So do you have the raw data, and if so how is it set up
> > (e.g. what are the columns?)
> >
> > "pdberger" wrote:
> >
> > > 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, kind of like this:
> > >
> > > Receptionist Causes
> > > 2min 3min 4min 5min 6min
> > > N 4min 2% 3% 6%
> > > u 6min 8% 11%
> > > r 8min 5%
> > > s
> > > etc.
> > >
> > > The patient doesn't care (rightfully) whether the delay is created by the
> > > receptionist or the nurse so I want to create an output histogram showing the
> > > SUM of the two delays. Two patients wait eight minutes, but one delay was
> > > caused mostly by the receptionist, and the other mostly by the nurse. He
> > > doesn't care. I want to create a histogram from this table showing the
> > > percentage of patients experiencing each level of total 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.)
> > >
> > > I've tried using SUMIF kinds of approaches, but have failed. I'm a VERY
> > > novice VBA writer, so any help would be appreciated.
> > >
> > > Thanks in advance.
> > >