Home

Excel Formulas

%3CLINGO-SUB%20id%3D%22lingo-sub-560651%22%20slang%3D%22en-US%22%3EExcel%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-560651%22%20slang%3D%22en-US%22%3EHi.%20I%20want%20to%20do%20average%20to%20a%20column(B)%20of%20date.%20It%20doesn%20t%20work%20just%20with%20average.%20The%20column%20of%20date%20has%20the%20follow%20formulas%3A%20DATEDIF.%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-560651%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-560689%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-560689%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F340544%22%20target%3D%22_blank%22%3E%40Mihnea1923%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBecause%20of%20%26amp%3B%22MONTH%22%20in%20your%20formula%20the%20result%20is%20text.%20And%20text%20is%20ignored%20in%20AVERAGE().%3C%2FP%3E%3CP%3ESolution%3A%20Remove%20%26amp%3B%22MONTH%22%20from%20you%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-560698%22%20slang%3D%22en-US%22%3ERe%3A%20Excel%20Formulas%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-560698%22%20slang%3D%22en-US%22%3EIf%20your%20data%20starts%20in%20Row%202%2C%20you%20may%20use%20this%20formula%20to%20remove%20the%20text%20%22MONTH%22%20and%20obtain%20the%20average%3A%3CBR%20%2F%3E%3DAVERAGE(INDEX(--SUBSTITUTE(B2%3AB101%2C%22MONTH%22%2C%22%22)%2C0))%3C%2FLINGO-BODY%3E
Highlighted
Mihnea1923
Occasional Visitor
Hi. I want to do average to a column(B) of date. It doesn t work just with average. The column of date has the follow formulas: DATEDIF.
2 Replies

@Mihnea1923 

Because of &"MONTH" in your formula the result is text. And text is ignored in AVERAGE().

Solution: Remove &"MONTH" from you formula.

If your data starts in Row 2, you may use this formula to remove the text "MONTH" and obtain the average:
=AVERAGE(INDEX(--SUBSTITUTE(B2:B101,"MONTH",""),0))
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies