Forum Discussion
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
- Subodh_Tiwari_sktneer unfortunately it returns an error..
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.
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.
- 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!
I think you are missing a semi-colon after F2 in the latter part of the formula.
