 • 581K Members
• 10.6K Online
• 703K Conversations

Highlighted

# excel error mean calculation with #number! in column

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!
6 Replies
Highlighted

# Re: excel error mean calculation with #number! in column

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.

Highlighted

# Re: excel error mean calculation with #number! in column

Does this work for you?

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

Highlighted

# Re: excel error mean calculation with #number! in column

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!
Highlighted

# Re: excel error mean calculation with #number! in column

@Subodh_Tiwari_sktneer unfortunately it returns an error..
Highlighted

# Re: excel error mean calculation with #number! in column

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.

Highlighted

# Re: excel error mean calculation with #number! in column

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

Related Conversations
Average
Janedb in Excel on
5 Replies
Add Selected data 3D maps greyed out.
RobHoekstra in Excel on
0 Replies
Excel formula
Riaan1055 in Excel on
9 Replies
The data model is not updated
aleonc in Excel on
0 Replies