excel error mean calculation with #number! in column

[corinadejong19]

I am designing a database. In this database I have a column with totals that is often updated. Part of the cells of this column are filled with numbers and part with #number! were not all info is available yet. Despite the presence of missing data I would like to calculate the mean of the already present data. But if I write a simple formula: =MEAN(L2:L63) this doesn't work because of the #number! errors in some of the cells. I would like excell to ignore the errors and calculate the mean of the already present data. I thought something along the lines of: IFcell>=0 THEN MEAN (L2:L63) may work but unfortunately not. Can somebody give me a tip on how to solve this? Thanks!

[#2]

@corinadejong19 

Include and error check in the formula for the total column. For example:

=IF(ISERROR(SUM(A1:C1)),"",SUM(A1:C1))

It returns an empty cell in case the sum formula would otherwise generate an error.

[#3]

@corinadejong19 

Does this work for you?

=SUM(IFERROR(L2:L63,FALSE))/COUNT(L2:L63)

[#4]

Thanks! My column is based on a datedif function: DATEDIF(E2;F2;“d“). If I add the error check like in your example it looks like this: =IF(ISERROR(DATEDIF(E2;F2;“d“)),““,DATEDIF(E2;F2“d“)) . Tried this in excel but I get the reply that this does not contain enough arguments. Do you know how I can solve this? Thanks!

[#5]

@Subodh_Tiwari_sktneer unfortunately it returns an error..

[#6]

@corinadejong19 

What error did you get?

Btw you may fix your Datedif formula to handle errors like this...

=IFERROR(DATEDIF(E2;F2;"d");0)

OR

=IFERROR(DATEDIF(E2;F2;"d");"")

You may choose one depending upon your requirement.

[#7]

@corinadejong19 

I think you are missing a semi-colon after F2 in the latter part of the formula.