Formula for average to eliminate blank cells

%3CLINGO-SUB%20id%3D%22lingo-sub-2294630%22%20slang%3D%22en-US%22%3EFormula%20for%20average%20to%20eliminate%20blank%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2294630%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20-%20I%20am%20trying%20to%20get%20the%20average%20appointments%20per%20week%20but%20there%20are%20some%20weeks%20where%20there%20is%20no%20data%20so%20it's%20counting%20the%20blanks%2C%20for%20example%2C%20there%20are%2018%20weeks%20so%20far%2C%20only%203%20weeks%20has%20data%2C%20week%206%20%3D3%20week%208%20%26amp%3B%2015%20%3D%201%20for%20a%20total%20of%205.%26nbsp%3B%20When%20using%20the%20formula%20%3Daverage(D1%3AU1)%2C%20it%20returns%203.%26nbsp%3B%20What%20formula%20do%20I%20use%20to%20eliminate%20the%20blank%20cells%3F%26nbsp%3B%20Thanks%2C%26nbsp%3B%20Annette%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2312121%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20for%20average%20to%20eliminate%20blank%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2312121%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1037282%22%20target%3D%22_blank%22%3E%40Annette827%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAVERAGE()%20shall%20ignore%20blanks%20and%20texts%2C%20not%20sure%20how%20did%20you%20receive%203.%20Anyway%2C%20to%20ignore%20something%2C%20e.g.%20zero%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DAVERAGEIF(D1%3AU1%2C%22%26lt%3B%26gt%3B0%22)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi - I am trying to get the average appointments per week but there are some weeks where there is no data so it's counting the blanks, for example, there are 18 weeks so far, only 3 weeks has data, week 6 =3 week 8 & 15 = 1 for a total of 5.  When using the formula =average(D1:U1), it returns 3.  What formula do I use to eliminate the blank cells?  Thanks,  Annette

3 Replies

@Annette827 

AVERAGE() shall ignore blanks and texts, not sure how did you receive 3. Anyway, to ignore something, e.g. zero

=AVERAGEIF(D1:U1,"<>0")

Thank  you.  I am getting the same answer - 3.  Maybe it is a weighted average that I am looking for.  Again , there are 18 weeks, 15 of them have nothing in them, one has 3 and two of them have 1 so I can't say the average is 3 per week when most don't have any!  I'm not really sure.  but appreciate you trying to help.  @Sergei Baklan 

@Annette827 

Sorry, I can't understand how do you receive 3 as an answer and therefore not sure which result you'd like to have.

From D1 to U1 we have 18 columns. Sum of values in them is 5. If ignore blank cells we have =5/3, if do not ignore =5/18. In both cases that doesn't return 3.

image.png

If in the middle not blank cells but cells with zero that also doesn't return 3.