Translation of Statistical Equation To Excel Formula | General Excel Questions & Answers | Excel Forum

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

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

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

"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