more alterations

[RDL]

Hi guys,
I am hoping you can help me with one more thin for this
formula. Part of my spreadsheet looks like this:

COLUMN C COLUMN R COLUMN S
Date of Date Found Wait in
Application Doctor Months

06/30/03 11/20/03 4

06/30/03 11/20/03 4


06/30/03 11/20/03 4

08/01/03 04/19/04 8
08/01/03 04/19/04 8


Before, I was asking you about column R. The cells that
contain a number in column S have the formula
=DATEDIF(C6,R6,"m")

I need to use that formula for the whole column, so that
when a date is entered in column R, the difference in
months automatically appears in column S. The cells with
numbers in Column S already do that, but I need the whole
column to do that when dates are entered later on, when
aplicants find a doctor. What I need is something added
onto the formula, =DATEDIF(C6,R6,"m") that takes into
account blank cells, so the error #NUM! doesn't appear,
the cell remains blank even though the formula is there.
I tried adding ,">0" to the end but that didnt work.

Do you understand what I mean??

 

[Frank Kabel]

Hi
use
=IF(COUNTA(C6,R6)=2,DATEDIF(C6,R6,"m"),"")

 

[RDL]

Thanks sooo much Frank! But, why then does that formula
change my cell containing the average of the numbers in
the cells of Column S to #VALUE! whereas before it read
the number 6 (as in the average wait time before finding a
doctor is 6 months)
What can I do to have it still give me the average. The
formula: =AVERAGE(S3:S372) now returns the error #VALUE!

 

[Frank Kabel]

Hi
AVERAGE should ignore the blank cells. Do you have any error in your
column S?

 

[Guest]

Hi,

I guess it did. Three cells contained #VALUE! because the
corresponding cells in column R had only X's because I
dont know the date yet. Once I got rid of those the
AVERAGE came back up at 6.40 months. I guess there is no
way around it if I leave those X's in there??

 

[Frank Kabel]

Hi
try
==IF(AND(ISNUMBER(C6),ISNUMBER(R6)),DATEDIF(C6,R6,"m"),"")