+ Reply to Thread
Results 1 to 10 of 10

AverageIf based on different criteria

  1. #1
    Registered User
    Join Date
    01-15-2013
    Location
    CA
    MS-Off Ver
    Excel 2010
    Posts
    5

    AverageIf based on different criteria

    Hello,

    I need a formula that provides the average of salaries based on the following criteria.

    Provide the average of salary, if Grade = X and Department =Y and Type = A or B.

    Also, is it possible to add the iserror formula so that I dont get the #DIV/0 error for grades that dont have data?

    Thanks for your help.
    Last edited by caseman; 01-15-2013 at 08:58 PM. Reason: Added details.

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,084

    Re: AverageIf needed

    Looks like you forgot the criteria, but take a look at =AVERAGEIFS(average_range,criteria_range,criteria,...)
    HTH
    Regards, Jeff

  3. #3
    Registered User
    Join Date
    01-15-2013
    Location
    CA
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: AverageIf based on different criteria

    Thanks Jeff. I submitted before i added my details. I have tried AVERAGEIFS, but it does not seem to return the right data.

  4. #4
    Registered User
    Join Date
    01-15-2013
    Location
    CA
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: AverageIf needed

    Thanks Jeff. I submitted before i added my details. I have tried AVERAGEIFS, but it does not seem to return the right data.

  5. #5
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,084

    Re: AverageIf based on different criteria

    How about...

    Please Login or Register  to view this content.

  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 based on different criteria

    Try this array formula**:

    =IFERROR(AVERAGE(IF(Grade="X",IF(Department="Y",IF(Type={"A","B"},Salary)))),"")

    ** 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.

  7. #7
    Registered User
    Join Date
    01-15-2013
    Location
    CA
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: AverageIf based on different criteria

    Thanks Biff. Built the formula...

    =IFERROR(AVERAGE(IF($J$2:$J$187="8",IF(L$2:$L$187="R&D - US",IF($I$2:$I$187={"M","AR"},$AD$2:$AD$187)))),"")

    AD2:AD187 is the salary average i need to return if the grade = 8, they are in R&D and if their type is either M or AR.

    At this time, nothing is populating and i know there should be data there. Any suggestions?

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

    Re: AverageIf based on different criteria

    A couple of possibilities...

    Remove the quotes from around the number 8.

    =IFERROR(AVERAGE(IF($J$2:$J$187=8,IF(L$2:$L$187="R&D - US",IF($I$2:$I$187={"M","AR"},$AD$2:$AD$187)))),"")

    Also, make sure the formula is array entered.

    Array formulas are entered differently than a regular formula. After you type in a regular formula you hit the ENTER key. With an array formula you *must* use a combination of keys. Those keys are the CTRL key, the SHIFT key and the ENTER key. That is, hold down both the CTRL key and the SHIFT key then hit the ENTER key.

    When done properly Excel will enclose the formula in squiggly brackets { }. You can't just type these brackets in, you *must* use the key combo to produce them. Also, anytime you edit an array formula it *must* be re-entered as an array using the key combo.

  9. #9
    Registered User
    Join Date
    01-15-2013
    Location
    CA
    MS-Off Ver
    Excel 2010
    Posts
    5

    Re: AverageIf based on different criteria

    Thanks for the reply Tony. I removed the "" around the 8, removed the typed {} around "M","AR" and I array entered the formula. I noticed that the {} are placed on the outside of the entire formula (see below) and I dont get an value in the cell. Any other suggestions?

    {=IFERROR(AVERAGE(IF($J$2:$J$187=1,IF(!$L$2:$L$187="R&D - US",IF($I$2:$I$187="M","AR",$AD$2:$AD$187)))),"")}

  10. #10
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Excel 2016
    Posts
    10,084

    Re: AverageIf based on different criteria

    Hi caseman,

    Tony did not mean for you to remove the {} from inside the formula. Look at his example in post #8.

    The {} are for around the formula (ctrl + shift + enter) which makes it an array formula.

    http://www.cpearson.com/excel/arrayformulas.aspx

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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