SOLVED

Need Help with Excel Function

%3CLINGO-SUB%20id%3D%22lingo-sub-2215110%22%20slang%3D%22en-US%22%3ENeed%20Help%20with%20Excel%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2215110%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20trying%20to%20make%20the%20following%20code%20not%20average%20blank%20spaces.%20Please%20help%26nbsp%3B%3C%2FP%3E%3CP%3E%3DAVERAGEIF(F2%3AF79%2CVLOOKUP(F2%2CF2%3AG79%2C%7B1%2C2%7D%2CFALSE)%2CG2%3AG79)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2215110%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2215393%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20Help%20with%20Excel%20Function%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2215393%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F999501%22%20target%3D%22_blank%22%3E%40Johntyler1%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20do%20you%20mean%20by%20%22blank%20spaces%22%3F%3C%2FP%3E%3CP%3EEither%20empty%20cells%20or%20cells%20with%20a%20space%20in%20them%3F%3C%2FP%3E%3CP%3EIn%20any%20case%20AVERAGEIF()%20ignores%20empty%20cells%20and%20text.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

I am trying to make the following code not average blank spaces. Please help 

=AVERAGEIF(F2:F79,VLOOKUP(F2,F2:G79,{1,2},FALSE),G2:G79)

4 Replies

@Johntyler1 

What do you mean by "blank spaces"?

Either empty cells or cells with a space in them?

In any case AVERAGEIF() ignores empty cells and text.

 

Empty Cells.
Unless I have a number in at least one cell, It continues to give me the #DIV/0! error
best response confirmed by Johntyler1 (New Contributor)
Solution

@Johntyler1 

That's how every average function works. You need at least one number value to calculate an average.

You can wrap IFERROR( ) around your formula to show a message or a 0.

=IFERROR(your_formula;"no values")