Home

excel error mean calculation with #number! in column

%3CLINGO-SUB%20id%3D%22lingo-sub-843546%22%20slang%3D%22en-US%22%3Eexcel%20error%20mean%20calculation%20with%20%23number!%20in%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-843546%22%20slang%3D%22en-US%22%3EI%20am%20designing%20a%20database.%20In%20this%20database%20I%20have%20a%20column%20with%20totals%20that%20is%20often%20updated.%20Part%20of%20the%20cells%20of%20this%20column%20are%20filled%20with%20numbers%20and%20part%20with%20%23number!%20were%20not%20all%20info%20is%20available%20yet.%20Despite%20the%20presence%20of%20missing%20data%20I%20would%20like%20to%20calculate%20the%20mean%20of%20the%20already%20present%20data.%20But%20if%20I%20write%20a%20simple%20formula%3A%20%3DMEAN(L2%3AL63)%20this%20doesn't%20work%20because%20of%20the%20%23number!%20errors%20in%20some%20of%20the%20cells.%20I%20would%20like%20excell%20to%20ignore%20the%20errors%20and%20calculate%20the%20mean%20of%20the%20already%20present%20data.%20I%20thought%20something%20along%20the%20lines%20of%3A%20IFcell%26gt%3B%3D0%20THEN%20MEAN%20(L2%3AL63)%20may%20work%20but%20unfortunately%20not.%20Can%20somebody%20give%20me%20a%20tip%20on%20how%20to%20solve%20this%3F%20Thanks!%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-843546%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-843580%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20error%20mean%20calculation%20with%20%23number!%20in%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-843580%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F405704%22%20target%3D%22_blank%22%3E%40corinadejong19%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EInclude%20and%20error%20check%20in%20the%20formula%20for%20the%20total%20column.%20For%20example%3A%3C%2FP%3E%3CP%3E%3DIF(ISERROR(SUM(A1%3AC1))%2C%22%22%2CSUM(A1%3AC1))%3C%2FP%3E%3CP%3EIt%20returns%20an%20empty%20cell%20in%20case%20the%20sum%20formula%20would%20otherwise%20generate%20an%20error.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-843635%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20error%20mean%20calculation%20with%20%23number!%20in%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-843635%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F405704%22%20target%3D%22_blank%22%3E%40corinadejong19%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20this%20work%20for%20you%3F%3C%2FP%3E%3CP%3E%3DSUM(IFERROR(L2%3AL63%2CFALSE))%2FCOUNT(L2%3AL63)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-843713%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20error%20mean%20calculation%20with%20%23number!%20in%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-843713%22%20slang%3D%22en-US%22%3EThanks!%20My%20column%20is%20based%20on%20a%20datedif%20function%3A%20DATEDIF(E2%3BF2%3B%E2%80%9Cd%E2%80%9C).%20If%20I%20add%20the%20error%20check%20like%20in%20your%20example%20it%20looks%20like%20this%3A%20%3DIF(ISERROR(DATEDIF(E2%3BF2%3B%E2%80%9Cd%E2%80%9C))%2C%E2%80%9C%E2%80%9C%2CDATEDIF(E2%3BF2%E2%80%9Cd%E2%80%9C))%20.%20Tried%20this%20in%20excel%20but%20I%20get%20the%20reply%20that%20this%20does%20not%20contain%20enough%20arguments.%20Do%20you%20know%20how%20I%20can%20solve%20this%3F%20Thanks!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-843721%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20error%20mean%20calculation%20with%20%23number!%20in%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-843721%22%20slang%3D%22en-US%22%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3Eunfortunately%20it%20returns%20an%20error..%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-843728%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20error%20mean%20calculation%20with%20%23number!%20in%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-843728%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F405704%22%20target%3D%22_blank%22%3E%40corinadejong19%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20error%20did%20you%20get%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBtw%20you%20may%20fix%20your%20Datedif%20formula%20to%20handle%20errors%20like%20this...%3C%2FP%3E%3CP%3E%3DIFERROR(DATEDIF(E2%3BF2%3B%22d%22)%3B0)%3C%2FP%3E%3CP%3EOR%3C%2FP%3E%3CP%3E%3DIFERROR(DATEDIF(E2%3BF2%3B%22d%22)%3B%22%22)%3C%2FP%3E%3CP%3EYou%20may%20choose%20one%20depending%20upon%20your%20requirement.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-843734%22%20slang%3D%22en-US%22%3ERe%3A%20excel%20error%20mean%20calculation%20with%20%23number!%20in%20column%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-843734%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F405704%22%20target%3D%22_blank%22%3E%40corinadejong19%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20think%20you%20are%20missing%20a%20semi-colon%20after%20F2%20in%20the%20latter%20part%20of%20the%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E
corinadejong19
New Contributor
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

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

 

@corinadejong19 

Does this work for you?

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

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!
@Subodh_Tiwari_sktneer unfortunately it returns an error..

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

@corinadejong19 

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