Comments on: How to calculate average (mean) in Excel with or without formulas

In Microsoft Excel there are a handful of different functions for calculating the average for a set of numeric values. Moreover, there is an instant non-formula way. In this article, you will find a quick overview of all the methods illustrated with examples of use and best practices. Continue reading →

Comments page 3. Total comments: 109

  1. Dear Svetlana & Community,
    thanks for the great tutorials.
    I am trying to average a selection of non-continous columns, all titled "kcal in".
    I have set the columns DF2:XA2 as the range, which is the title column. "kcal in" is the crietria and DF4:$XA$435 is the average of all kcal in in the DF4 row. I can drag the formula down to obtain the average for rows DF5 etc.

    =AVERAGEIF($DF$2:$XA$2, "kcal in", DF4:$XA$435)

    This works well, however, I am trying to exclude any 0 values in row DF4 from the average and I am not sure where to put this criterion.
    Many thanks in advance for your help

    1. Found the answer here myself :)
      =AVERAGEIFS(DF6:XA6,$DF$2:$XA$2,"kcal in",DF6:XA6,">0")

      Adapted from your explanation

      Average_range is DF6:XA6 (the row you want to average if both conditions are met);
      Criteria_range1 is $DF$2:$XA$2 (the head column, range fixed with dollar sign) and criteria1 is "kcal in"(allowing to average non-continuous columns)
      Criteria_range2 is DF6:XA6 (the row you want to average) and criteria2 is ">0". (values of 0 are excluded)

      Thanks for the great site, finally my mind can rest :D

  2. Hi , i am trying to find an average based on criteria :

    Example 1 : A1 = 3, E1=2 , J1 = 10, My answer will be A1+E1+J1/3 = 5.

    Example 2 A1 = 3, E1 = 3, J1 = -, My answer will be A1+E1+J1/3 = 3.

    Example 3 A1 = -, E1 = -, J1 = -, My answer should be A1+E1+J1/3 = 0.

    Example 1 and 2 is Ok, Where as in example 3 am getting error #DIV/0! instead of this i need 0 in the box.

    Thanks in advance

    1. Hi,
      please note that empty cells are ignored by AVERAGE function.
      Try entering zero (0) in the cells, if you want zero to return.

      Hope it helps!

  3. Hi , i am trying to find an average but only based on a certain number of cells in a range. I want to leave the full range in place for the Average formula , but only use part of it based on criteria :

    Example : Range A1:M1 carries a forecast of values at start of a Quarter. Three weeks into the Quarter , I then update with Actuals into Cells A1 , B1 , C3 (i.e. i have input 3 cells with Actuals.)

    Now i want the average formula for A1:R1 to only use the first 3 cells (which are the Actuals now and the remainder are the Forecast) in my result.

  4. hi, i need to get the average of below data but it seems like im having problem, just one category from type is getting. THANKS SO MUCH IN ADVANCE:)

    here's my formula: =AVERAGEIFS(D:D,B:B,"prior20",C:C,"cavite",E:E,{"crtfixed","crtvoice","rbg-crt"})

    A B C D
    date area ave type
    prior20 cavite 10 crtfixed
    prior20 cebu 9 crtvoice
    prior20 makati 8 rbg-crt
    prior20 cavite 7 crtfixed
    prior20 cebu 6 crtfixed
    prior20 makati 5 crtvoice
    prior20 cavite 4 rbg-crt
    prior20 cebu 3 crtfixed
    beyond luzon 2 crtfixed
    prior20 makati 5 crtvoice
    prior20 cavite 4 rbg-crt
    prior20 cebu 3 crtfixed

    1. Hi Mitch,
      try this formula:

      =SUM(SUMIFS(D:D,B:B,"prior20",C:C,"cavite",E:E,{"crtfixed","crtvoice","rbg-crt"}))/SUM(COUNTIFS(B:B,"prior20",C:C,"cavite",E:E,{"crtfixed","crtvoice","rbg-crt"}))

  5. I want to auto calculate average of every five cells of a column to another column. I have daily rainfall data for 30 years but I want average of every 5 days of each year.

  6. I have the monthly production but I want to average the documents processed by user per hour
    I have following information:
    User name Posting Date Time Document no.
    1 1/17/2017 13:53:27 xxxxx
    1 1/18/2017 9:24:29 xxxxx
    2 1/17/2017 15:05:30 xxxxx
    3 1/20/2017 12:09:51 xxxxx
    4 1/01/2017 12:09:52 xxxxx
    5 1/31/2017 08:30:20 xxxxx

    hour
    have a file with the monthly production and I´m trying to average the invoices process by user per day

  7. Hi Team Ablebits,

    can i use AverageIf function for the criteria by font color?

  8. 2001 8.5
    2002 9
    2003 10
    2004 18
    2005 16
    2006 10
    2007 11
    2008 16.25
    2009 14.15
    2010 15.5
    2011 25
    2012 15.5
    2013 15.35
    2014 13.65
    2015 11.86
    2016 5.15

    i want to calculate the average among the years like 2003-2009 or 2011-2015 etc.
    from: 2003 is one cell
    to: 2009 is on cell

    i m using =AVERAGEIFS(B7:B22,A7:A22,B30&"=>"&"="&B31)
    BUT NOT ANSWERING, PLEAE HELP ME IN THIS MATTER.

  9. I want to average multiple cells in a row for each task and then copy down the formula rows below. Each cell has a text value which first needs to be converted to number and then the average calculated. E.g. A1="Start" (which equals 0; B1="Mid Point" (which equals 50) and C1="End" and then Column D1=Average(A1:C1) = ((0+50+100)/3)=50. Formula should work for a range of cells without including the condition (if cell="Start",0,if(cell="Mid Point",50,100))for A1,B1...M1 separately.

  10. I need formula to calculate this data
    67 87 86 57 97 86 54 87
    determine if the candidate pass PR not if the average score is 55

  11. I am using an AVERAGEIF formula using ranges that have been rounded up and down. When I evaluate my formula the range changes from say 9.0 to 8.999999999 which then means the formula produces a #DIV/0! value.

    Can you help with sorting this out please?

    1. Sorry, that should have read AVERAGEIFS

      Cheers

  12. Hi, Could you please tell me how can i calculate average excluding blank boxes in excel?

    1. Hi!

      By default, the Excel Average function excludes blank cells from an average, though cells with zero values are included.

  13. Hi,
    I want to do average of the values using averageif formulae, these values contain 0 also but I want to exclude these Os from the formula.
    Please suggest which formula to use.

    1. Hello Avnish,

      Use the AVERAGEIFS function and include "is not equal to 0" ("<>"&0) as one of the criteria.

  14. Helo every one.
    I'm trying to ge the average for a type of subscription that has 2 names (both are in the same column):
    Sub1 and Sub2
    (I would like the average for both types together)
    However, I also want to restrict to the length of time the subscription has been held ie between two values, say 1 - 3 months.
    I think what I'm after is getting the average for OR, and then an AND

    Regards

    Karon

  15. For relative newcomers or those returning after a long period of non use of Excel, it might have been better to keep it really simple.
    I thought it would be straight forward but alas despie reading several internet "help" sources I am no nearer to doing something very simple.
    I just have about 650 temperature readings in a column of several thousand.
    The range of the 650 is contiguous. I wast the arithmetic average.
    "Average(C68:C720)" was the formula I attempted but nothing happens when I press enter.
    If it's easy why does this not work, & why if there is "a secret" is it not mentioned?

  16. I have the following formula which works perfectly, however if the lookup value returns no data, I want it to average cells in the previous five rows. Can you help me with a formula to make it work? I have excel 2010

    =+IFERROR(VLOOKUP($A6,$A$4:$N$34,9,0),0)

  17. Start Down time End Down Time
    11:00 AM 11:05 AM
    1:00 PM 1:10 PM
    2:30 PM 2:45 PM

    Q Write a function in highlighted green cell to get average down time.

  18. I have this formula:

    =IFERROR(AVERAGEIFS(Data!G:G,Data!B:B,">=7/1/2016",Data!B:B,"<=7/31/2016",Data!E:E,'Pareto Osmosis'!A33),"-")

    I need that this formula change the values of the month that i have in the archive (with respect to column), and, i want to do a little macro to update to the next month. What do i need to do?

  19. =AVERAGEIF(Q2:Q3304, Q2, H2:H3304)
    The above formula works for the first of 3304 rows of data. I need to copy the formula down to average the remainder of the scores on each row. I need the middle indicator "Q2" to change based on the row number, but I need the other values to remain the same Q2:Q3304 and H2:H3304.

    When I copy the rows down, it wants to change the beginning row for each line.
    Ex:
    Coping down incorrectly changes it to: =AVERAGEIF(Q3:Q3305, Q3, H3:H3305)--
    I need it to be: =AVERAGEIF (Q2:Q3304, Q3, H2:H3304)

    Is there a way to do this? Please assist.
    Thank you!

  20. I still dont have the answer which l am looking for:

    I need a function as if 5 Days average of a certain is 59% and the sixth day l add 30% manually so then its 50% how to bring this in a function

  21. I really like excel, I always try to something new and to learn and this for svetlana Cheusheva...

  22. Hi,

    Within Cells A33:A151, I have options of saying either "LISTING" or "DBL END".

    In cell N152, my current formula is as follows:
    =AVERAGEIF(A33:A151,"LISTING",N33:N151)

    This formula works, however, I am having trouble writing the formula so that when a cell within A33:A151 says "DBL END" (as opposed to "LISTING"), Cell N152 still calculates accordingly.

    Thanks.

  23. Hi I want include the Zeros between Feb to Aug and exclude the other Zeros for Calculating the Average
    Jan Feb Mar Apr May Jun Average
    0 505 0 0 346 0 ??

  24. I'm try to create a formula that takes a percentage in four cells per page up to 6 pages. So with a possibility of 24 total entries. that I fill in weekly. Lets say I only have 18 entries that week. So I will like the formula to see that there is only 18 entries and give me the average percentage on those 18 entries not the total 24, the rest will be zero. In the current page the cells are position in (H18,B18,B5,H5) location.

  25. Hi

    How can I make the averageifs between two dates work if the average range and the criteria range are in different tabs to the date references?

    =AVERAGEIFS('Timeliness SP'!$D$2:$D$10000,'Timeliness SP'!$AM$2:$AM$10000,">="&B66,'Timeliness SP'!$AM$2:$AM$10000,"<"&B67)

    This returns Div/0 but if I look at only one date (there are multiple items per date) then it works fine.

    Thanks for any help!

  26. 80 Fail 92 Pass #DIV/0! #DIV/0!

    How get the average and sum this range ?
    Please suggest me Pls

  27. Good morning,

    We are trying to use the averageifs function over an array of number from C2:O1090 - is this possible as all examples point to a column average only?

    Thank you.

    1. Hello Jackie,

      Thank you for your question! Until now I haven't realized that all the examples in this tutorial are for a column average, maybe because it's the most typical task.

      In fact, you can supply absolutely any range to your Average formula, and even several ranges or individual cells separated by commas. So, the following formula will work just fine:
      =AVERAGE(C2:O1090)

      1. Is there any way to use the range in AVERAGEIFS? I have a block of data and need to take the average based on a column criteria and row criteria. Thanks.

  28. A 55
    B 60
    A
    B 20
    A
    B 30
    A 20
    AVERAGE

  29. I am working with data spread over 30years and need to find the average for figures which are above 0.5 in the data. I have tried using the averageifs but keeps receiving error message. Attached is a screen shot. Kindly advice how i can proceed.

    YEAR MONTH DAY 1 DAY 2 DAY 3 DAY 4 DAY 5 DAY 6
    1972 01 0,0 0,0 0,0 0,0 0,0 0,0
    1972 02 0,0 0,0 0,0 0,0 0,0 0,0
    1972 03 0,0 0,0 0,0 0,0 2,5 0,0
    1972 04 0,0 25,2 0,0 0,0 0,0 59,4
    1972 05 0,0 0,0 34,0 1,0 0,0 0,0
    1972 06 0,0 0,0 0,0 0,0 0,0 0,0
    1972 07 0,0 0,0 0,0 0,0 0,0 0,0
    1972 08 0,0 0,0 0,3 0,5 0,3 0,0
    1972 09 0,0 0,0 0,0 20,3 0,0 0,3

  30. Hello,

    I need to find the average but I need the average of the values between two other values, this lasts change with every new range

    Thanks,

  31. Svetlana, I have a large data set of over 500 referential urls. The table contains both a count of page views and times spent on the page. I have the following formula that allows me to create a report that gauges interest by keyword in the urls. I need a formula which will filter the following conditions I used in my sumproduct formulat but which will give me an average instead of a sumproduct. Everything I have tried to make this happen has not worked. How can I use what you write above to accomplish this task. Your help would be appreciated. Many Thanks.

    =SUMPRODUCT(('RAW DATA'!$A$2:$A$25000=B$336)*
    ('RAW DATA'!$E$2:$E$25000="micrositeABC")*
    (MMULT(0+ISNUMBER(SEARCH(
    {"rebate","savings"},
    'RAW DATA'!$J$2:$J$25000)
    ),{1;1})>0),'RAW DATA - PAGE CATEGORIES'!$K$2:$K$25000)

    1. – thanks for dropping by man! I like that you bring in a number of other areas and tie it into creation, I completely agree that this is the mode and task of the church today — to create a forum and median for pattpciiarion [in the Kingdom of God].

    2. Hello, Thom,

      For us to be able to help you better, we need the original math formula you use. Or your workbook with the expected result. You can email the details at support@ablebits.com.

  32. Dear Svetlana,

    I need to average on a averageifs formula for a range that includes na() and Div/0!

    =AVERAGEIFS('SQL Data'!$L$4:$L$65,'SQL Data'!$A$4:$A$65,'sql vs rbct'!$B19,'SQL Data'!$B$4:$B$65,""&'sql vs rbct'!$C19)

    This formula above averages between dates and times for the selected date and time range. The data to average is in column l, data in A, times in B.

    I have tried the change below but get a value error if I combine the two formulas...

    {=AVERAGE(IF(ISNUMBER(Q188:Q194),Q188:Q194))}

    Can you help?

    1. Dear Pieter,

      For us to be able to assist you better, please send us a small sample table with your data in Excel to support@ablebits.com. Please also include the expected result. Thank you.

  33. range is spelt as rage

    1. **spelled

  34. typogrphical error under Excel Average function, second sentence, with bold-face type font

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)