+ Reply to Thread
Results 1 to 6 of 6

Averageif criteria

  1. #1
    Registered User
    Join Date
    04-03-2010
    Location
    Orlando, FL
    MS-Off Ver
    Office 365
    Posts
    94

    Averageif criteria

    Hi All,

    I want to average the numbers in column C based on criteria in column B. Example, if the value of any of the cells in cells B2:B35 is between 1.0 and 2.0, then the corresponding cells in column C should be averaged. I hope that makes sense...
    I tried this formula, but am getting a "divide by zero" error...
    Please Login or Register  to view this content.
    Last edited by wpryan; 11-03-2015 at 06:55 AM.

  2. #2
    Forum Expert
    Join Date
    02-22-2013
    Location
    London / Stockholm
    MS-Off Ver
    Office 365
    Posts
    1,218

    Re: Averageif criteria

    Since you are using 2007, try (untested):

    =SUMPRODUCT(((B2:B35>=1)-(B2:B35>2)>0)*C2:C35)/SUMPRODUCT(--(((B2:B35>=1)-(B2:B35>2))>0))

  3. #3
    Forum Expert
    Join Date
    02-22-2013
    Location
    London / Stockholm
    MS-Off Ver
    Office 365
    Posts
    1,218

    Re: Averageif criteria

    My mistake, I guess the averageifs function also applies to Excel 2007.

    See if this works for you,

    =AVERAGEIFS(C2:C35,B2:B35,">=1",B2:B35,"<=2")

  4. #4
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,040

    Re: Averageif criteria

    That should be AND not OR, ie
    B2:B35 > 1.0 AND B2:B35 (in fact you've already typed "and" in your description of the problem
    Try

    =AVERAGEIFS(C2:C35,B2:B35,">=1",B2:B35,"<=2")
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  5. #5
    Registered User
    Join Date
    04-03-2010
    Location
    Orlando, FL
    MS-Off Ver
    Office 365
    Posts
    94

    Re: Averageif criteria

    That does the trick (the second one - I didn't try the first). How can I get the max and min values using the same kind of conditions?

  6. #6
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Averageif criteria

    Try these array formulas**:

    =MAX(IF((B2:B35>=1)*(B2:B35<=2),C2:C35))

    =MIN(IF((B2:B35>=1)*(B2:B35<=2),C2:C35))

    ** array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Using a Variable as a Criteria in the AVERAGEIF function
    By TheDudeDude in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-04-2021, 10:46 PM
  2. Averageif for the top 70,80,90% and based on a criteria
    By skate1991 in forum Excel General
    Replies: 16
    Last Post: 05-06-2015, 12:54 PM
  3. Multiple Criteria AverageIF
    By orev2 in forum Excel General
    Replies: 1
    Last Post: 08-19-2014, 01:18 PM
  4. averageif formula with multiple criteria
    By CPitta in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-08-2013, 12:56 PM
  5. AverageIf based on different criteria
    By caseman in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 01-16-2013, 02:18 PM
  6. Criteria question - AverageIF (if you please)
    By djalexr in forum Excel General
    Replies: 3
    Last Post: 01-14-2011, 08:00 AM
  7. AverageIf based on another columns criteria
    By AllenMead in forum Excel General
    Replies: 7
    Last Post: 09-06-2010, 12:46 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1