Ignore blank cells in formulas & in average

  • Thread starter Thread starter RS
  • Start date Start date
R

RS

AAAUUUGGGHHH!!!! I'm trying to do something which seems
simple but is turning out not to be so. Here is my
problem...I have columns with the following 4 formulas:

C1=B1-A1
F1=E1-D1
I1=H1-G1
(Note that columns A,B,D,E,G,&H contain numbers in them.)

Then, J1=AVERAGE(C1,F1,I1)

This works fine when there are numbers in all the columns.
(For example: cells A1,D1,&G1 all contain the number "1";
cells B1,E1,H1 all are "2"; then C1,F1,&I1 all result in
"1" with the resulting average in J1 = "1")

HOWEVER, 2 problems occur with blank cells:
1a) if there are any blanks in columns A,B; D,E; or G,H;
then column C,F, or I gives a value of 0 instead of
staying blank. (For example: if G1 & H1 are blank, I1
shows a "0" instead of a blank cell)

1b) This in turn results in column J thinking there are
numbers in all three cells and calculating the average of
all three cells. (For example: if C1 is 1 and F1 is 1,
and G1 & H1 are blank, I1 displays 0, and the average in
J1 calculates the result as 0.6667 instead of 1.

2) If B,E, or H is blank, Excel treats those cells as if
they are zero and places a value in C,F, or I. [For
example: cells A1,D1,&G1 all contain the number "1";
and only cells B1 & E1 are "2" (H1 is blank); then C1&F1
both = "1", but I1 = -1 with the resulting average in J1 =
"0.3333")

So...after all this, I want Excel to simply ignore blank
cells [I don't want it to ignore ZERO values, because this
may sometimes occur (i.e, if A1=1 & B1=1, then C1, which
is B1-A1, will calculate 0 and this value should be
included in the "average" calculation.

If anyone could help that would be great!!! Once again,
sorry for the long message, but without being able to
paste in a sample spreadsheet in this newsgroup posting, I
had to be a bit verbose.
 
I think I understand your "verbose" post. If you read the Excel help, you
will note that the AVERAGE function ignores blank cells. So I think what
you are looking for is to make C1, F1, and I1 blank as opposed to zero
values if A1, B1, D1, E1, G1, or H1 are blank. In C1, then, try the
formula:

=IF(OR(A1="",B1=""),"",B1-A1)

Then copy it to F1 and I1. In the above, the formula checks first to see if
EITHER A1 or B1 are blank. If one of those cells is blank, the formula
returns "" (an empty value) as opposed to returning zero. The AVERAGE
formula in J1 would then ignore the value in C1 and will not include it in
the calculation.

If I'm misunderstanding, please let me know.

HTH
Mike
 
Friend I don't know if you found an answer but the same problem is driving me crazy. I'm Looking at a hospital/ER situation where I need average and median length of stay (LOS). I've got admit date and time field, discharge date and time field, and a calculated LOS field=Discharge minus admit. Since some patients haven't been discharged there are blanks or zeros in the LOS field. This completely trashes average and median. It would be safe to ignore ALL zeros in LOS column since LOS can never be zero but I just can't make it happen.
Hope you found an answer.
theroo
 
=AVERAGE(IF(A1:A5<>0,A1:A5))

entered with ctrl + shift & enter

--

Regards,

Peo Sjoblom

theroo said:
Friend I don't know if you found an answer but the same problem is driving
Click to expand...
me crazy. I'm Looking at a hospital/ER situation where I need average and
median length of stay (LOS). I've got admit date and time field, discharge
date and time field, and a calculated LOS field=Discharge minus admit.
Since some patients haven't been discharged there are blanks or zeros in the
LOS field. This completely trashes average and median. It would be safe to
ignore ALL zeros in LOS column since LOS can never be zero but I just can't
make it happen.
 
Back
Top