Hi Shelley,

Please find file attached. Not sure if the values I substituted into the formula make sense but you can replace them with real figures.

Mynda

Hi Mynda

It would be good if the cells was renamed NI,TI etc so that the formula can be clearer

=(NI*TI)/NA/(BT-BA)

The answer given in by you is 5 but I believe there may be some parenthesis missing.

(1) =(A2*A3)/A4/(A5-A6) this gives 5 but

(2) =(A2*A3)/(A4/(A5-A6)) gives 125

so which is correct? My guess is (2)

Sunny Kow

Actuallly, my bad. The formula should have been:

=(A2*A3)/A4*(A5-A6)

I incorrectly used divide instead of multiply after A4.

It gives the same result as your formula number 2 but I think it reads inline with the formula Shelley provided.

I didn't use named ranges because I got the impression that Shelly's level of Excel skills may not be at that level yet since this:

=(NI*TI)/NA*(BT-BA)

Is not a lot different to this:

niti/Na(BT-BA)

I just thought it would be clearer for her to follow cell references without the added level of named range complexity.

I've attached a revised file with the correct formula. By all means use named ranges if you understand them, but if you have multiple emergency departments to evaluate then named ranges won't help you. Instead, format your data in an Excel table with a column for each criteria. I've inserted an example table in the revised file attached.

Mynda

Hi Mynda,

I'm a bit in doubt about the correct interpretation of the formula:

niti/Na(BT-BA)

Fully written it should be ni*ti/Na*(BT-BA)

In words and respecting the mathematically order of the operators I read:

the product of ni & ti divided by the product of NA multiplied with the difference of BT & BA.

or: (ni*ti)/(Na*(BT-BA))

Excel 2013 does not respect the rules. It calcualtes ni*ti and divides it by NA, next multiplies by BT-BA.

=(A2*A3)/(A4*(A5-A6)) results in 5 and not 125. But again the formula is not really clear about this.

According to this: The_Emergency_Department_Occupancy_Rate_A-Simple_Measure_of_.pdf

"The numerator of the EDWIN is the sum of the Emergency Severity Index triage categories (ti) of all active patients (ni) in the ED.

The EDWIN denominator is the cross-product of the number of treatment bays (BT) minus the number of admitted patients (holds) (BA) multiplied by the number of attending physicians (Na) working each hour."

From the original expression: ∑niti/Na(BT-BA),

∑niti is the nominator, and Na(BT-BA) is the denominator.

Mynda first formula is correct, =(A2*A3)/(A4*(A5-A6)) is exactly the same with =(A2*A3)/A4/(A5-A6) , it's basic arithmetics (just like 10/(2*3)=10/2/3)

My opinion is that the formula is still not accurate, because of the ∑ symbol. This is usually used when ti and ni are arrays of values, for each ti we have a ni.

This means that we have to multiply each ti with its corresponding ni, and sum the results. This is done using the SUMPRODUCT formula. Considering that in A1:A10 we have the ti for each triage categories, and in B1:B10 we have the corresponding number of active patients ni, the formula should be:

Cell C1 should have the Na value, D1 should be BT, and E1 should have the value of BA.

In real scientific environement, when a formula is written, the nominator is written first, then the slash and the denominator, I don't think that anyone would write on a paper something like this: 10*30/2*3 with the intention to divide 30 by 2 AND by 3.  Thinking in Excel, if we type this as is, it will be a big mistake. If the denominator is not wrapped in paranthesis, excel will consider that 10*30*3 is the nominator, and 2 is the denominator.

At least, this is how I see things, hope it helps.

Catalin

Hi Catalin,

I agree and I do also think that the sigma part is not yet included but IMO it is more an integral distribution formula we need here. Just could not find which one.

It does make me think about Erlang calculations but I don't see relations.

It may be look a bit strange to compare emergency occupancy with call center occupancy but basically it is the same, except the fact that you can refuse to pick up a call but can't refuse a patient.

"The numerator of the EDWIN is the sum of the Emergency Severity Index triage categories (ti) of all active patients (ni) in the ED." Good point, Catalin. It's unlikely that all patients will have the same severity index, so that would explain the sigma component which I wasn't sure about. Good job hunting down the documentation on the formula.

JP-Ronse, interesting that you mention Erlang calculations. We have a new course on call center analysis and on the info page for the course we include a tutorial on Erlang. It's here if you, or anyone would like to watch it: https://www.myonlinetraininghub.com/excel-for-customer-service-professionals

Mynda

Hi

Thanks to everyone who posted here, I really appreciate it.  I should have included this first but there is a website that calculates the EDWIN and when entering the information used in the examples, I don't get the same results as the others here.  Unfortunately the website calculator doesn't have a way for me to determine how they are calculating it.

http://providers.otddi.com/h3/index.php/2012-11-06-16-50-56/edwin-calculator

You are also all correct that the patients won't have the same severity or triage level but I've also read in other material that the weight of the "ti" is in reverse.   With the triage level 1 is most ill (resuscitative) and triage level 5 is not urgent at all and is to be avoided.  But based on other material I've read the values are reversed for the EDWIN calculation i.e. triage 1 =5, triage 2 = 4, triage 3 = 3, triage 4 = 2 and triage 5 = 1.  

Based on the calculator above below is some data and the resultant value based on the calculator:

ni = 15

nt = 2 x triage 1; 5 x triage 2; 2 x triage  3; 4 x triage 4; 2 x triage 5

NA = 2

BT = 13

BA = 2

EDWIN = 2.09

Not sure if this helps with figuring out this formula?  Thanks.

Mynda, because an Emergency Department is also about flow and staffing, I would think the call center course might reveal tools that could be applied to it as well?

Sorry, me again.  Catalin is exactly correct at his interpretation of this because I did as he indicated, using the reverse order of the triage levels to multiply by and I too came up with 2.09 just like the calculator did.

niti = (2x5)+(5x4)+(2x3)+(4x2)+(2x5) = 46

NA = 2

BT = 13

BA=2

(46)/(2x(13-2)) = 2.09

Thanks again everyone! 

Thanks Mynda for the info on the Erlang training.

It's great to see the forum was able to find an answer to Shelley's question and it's wonderful to see an active discussion with many participants all working together to help and learn.

Thanks everyone 🙂

Shelley,

I'll get Harold Graycar, who created the Excel for Customer Service Professionals course, to contact you via email to discuss if he thinks you would benefit from his course.

Mynda

Hello everyone who took part in this conversation,

I'll respond to Shelly by PM, but for general information:

My course 'Excel for Customer Service Professionals' does not specifically cover the EDWIN statistic, but it does give a strong overview of the methods used to calculate queues and service statistics in Excel.  It provides models for the Poisson Queue (where people arrive at random intervals) and the Erlang Queue (which accommodates waiting times for service).

Due to the complexity of some of the calculations, a lot of the mathematical work is done in code (Visual Basic for Applications) and the code is provided with the sample workbooks in the course.

My course goes into the techniques for visualizing, charting and presenting information clearly, so if you're interested in the best ways of creating an EDWIN calculator that can be used in your workplace, then I'd recommend the course.

Harold