Zeros and blanks ruin average and median

[theroo]

I need to calculate length of stay (LOS) for individual patients then perform summary statistics on the whole group. I calculate individual LOS by subtracting Discharge from admit (both fields formatted as date and time, i.e. 11/7/03 23:00) to get LOS. Since some patients have not been discharged at the time calculation are need, their LOS field is blank (or contains 0). Because of this average and median functions are useless. I need to ignore blank or zero fields. The value in the LOS field can never actually be zero. How can I avoid this problem and perform calculations only on those records which contain a discharge date-time?

Any help will be appreciated hugely.
theroo

 

[Bob Phillips]

Average will ignore blanks, but not zero. So just make sure that your LOS
figure is blank if the end time is not yet available.

C1: =IF(B1<>"",B1-A1,"")


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

I need to calculate length of stay (LOS) for individual patients then

perform summary statistics on the whole group. I calculate individual LOS
by subtracting Discharge from admit (both fields formatted as date and time,
i.e. 11/7/03 23:00) to get LOS. Since some patients have not been
discharged at the time calculation are need, their LOS field is blank (or
contains 0). Because of this average and median functions are useless. I
need to ignore blank or zero fields. The value in the LOS field can never
actually be zero. How can I avoid this problem and perform calculations
only on those records which contain a discharge date-time?

 

[Peo Sjoblom]

Also use a newsreader so you can see the answer to your previous message

--

Regards,

Peo Sjoblom

I need to calculate length of stay (LOS) for individual patients then

perform summary statistics on the whole group. I calculate individual LOS
by subtracting Discharge from admit (both fields formatted as date and time,
i.e. 11/7/03 23:00) to get LOS. Since some patients have not been
discharged at the time calculation are need, their LOS field is blank (or
contains 0). Because of this average and median functions are useless. I
need to ignore blank or zero fields. The value in the LOS field can never
actually be zero. How can I avoid this problem and perform calculations
only on those records which contain a discharge date-time?

 

[theroo]

Thanks Bob,
That helps. I need to remove the formulas from the LOS field where there is no discharge date. That will eliminate the zeros.

 

[Tom Ogilvy]

Bob was suggesting that your formula should look at the discharge date, and
if it is empty, show a "", otherwise calculate the LOS as a number. Not
remove your formulas. Average and median ignore non-numeric, non-error
input.

--
Regards,
Tom Ogilvy

Thanks Bob,
That helps. I need to remove the formulas from the LOS field where there

is no discharge date. That will eliminate the zeros.

 

[Bob Phillips]

Precisely, I even gave you an idea of a formula to setup a "" value if there
was no dischareg date.

Bob