formula to ignore NA data OR how to make the formula result in a blank rather than NA

Help please:

I have some data about discharge dates and I then get the average of these the trouble is I sometimes don't have the discharge dates so this results in an #NA - which is fine as I know that it's because I don't have the data however the problem is when I sum all of those dates to get an average and this results in #NA as there are #NA's in that column....

do I need to put something in the formula of the average sum to ignore the #NAs

Or

do I need to put something in the formula which is working out how long the patient has been in to say result in a blank if there is no discharge date then the average sum formula would be happy again?????

Thanks

To use data for further calculation you definatelly need to change formula that returns #N/A. You can return 0 or return nothing (blank cell).
But you should take into account that all 0 will affect average result. To skip rows without discharge dates - you should keep cell blank.
I don't know that formula do you use - lets call it "formula". Try to amend it like this:

Formula:

Please Login or Register  to view this content.

Start Date / Expected Discharge Date / Length of stay to date / Anticipated LOS


Theses are my four title - I often don't have an expected discharge date so that column is empty so I would like in that case the anticipated LOS column to result in a blank cell

I tried your formula - see below

=if(ISNA(DATEDIF(E9,F9,"D")),"",DATEDIF(E9,F9,"D"))

But instead of a blank cell is gave me a number

26/10/2016 BLANK 70 83

I don't understand why it resulted in 83?

Ok, lets make it simple. Expected Discharge Date is column B, right? If not, then change B9 to the right column reference.
So formula could be:

Formula:

Please Login or Register  to view this content.

E = Start Date

F = expected discharge date

G = Length of Stay

H = Anticipated LOS

I would like H to result in a blank if F is blank

Cell H9 formula:

Formula:

Please Login or Register  to view this content.

yes that worked - thanks
Have hit an error when there was some writing in the cell but think will just have to deal with those couple on individual basis

thanks for the help