 • 442K Members
• 9,890 Online
• 530K Conversations

# 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

# 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.

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

Does this work for you?

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

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

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

@Subodh_Tiwari_sktneer unfortunately it returns an error..

# 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.

# 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
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies