Zeros and blanks ruin average and median

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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)

theroo said:
I need to calculate length of stay (LOS) for individual patients then
Click to expand...
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?
 
Also use a newsreader so you can see the answer to your previous message

--

Regards,

Peo Sjoblom

theroo said:
I need to calculate length of stay (LOS) for individual patients then
Click to expand...
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?
 
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 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

theroo said:
Thanks Bob,
That helps. I need to remove the formulas from the LOS field where there
Click to expand...
is no discharge date. That will eliminate the zeros.
 
Precisely, I even gave you an idea of a formula to setup a "" value if there
was no dischareg date.

Bob
 
Back
Top