+ Reply to Thread
Results 1 to 8 of 8

AverageIf based on another columns criteria

  1. #1
    Registered User
    Join Date
    07-06-2010
    Location
    Beckenham
    MS-Off Ver
    Excel 2007
    Posts
    84

    AverageIf based on another columns criteria

    Am a little stuck with the average if function.

    I want to average the values in column G (where populated) if Column A=PHA

    It's monday afternoon! well,that's my excuse for being rubbish today!
    Last edited by AllenMead; 09-06-2010 at 12:46 PM.

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: AverageIf based on another columns criteria

    Try like this

    =AVERAGEIF(A:A,"PHA",G:G)
    Audere est facere

  3. #3
    Registered User
    Join Date
    07-06-2010
    Location
    Beckenham
    MS-Off Ver
    Excel 2007
    Posts
    84

    Re: AverageIf based on another columns criteria

    Quote Originally Posted by daddylonglegs View Post
    Try like this

    =AVERAGEIF(A:A,"PHA",G:G)
    That works fantastically except where I have a few zeros as I don't want to count these either in G:G

    Sorry, I should have been clearer in the first place.
    Last edited by AllenMead; 09-06-2010 at 11:13 AM.

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: AverageIf based on another columns criteria

    OK, in that case you can use AVERAGEIFS (with an "S" at the end) and add another condition to exclude zeroes, i.e.

    =AVERAGEIFS(G:G,G:G,">0",A:A,"PHA")

  5. #5
    Registered User
    Join Date
    07-06-2010
    Location
    Beckenham
    MS-Off Ver
    Excel 2007
    Posts
    84

    Re: AverageIf based on another columns criteria

    Quote Originally Posted by daddylonglegs View Post
    OK, in that case you can use AVERAGEIFS (with an "S" at the end) and add another condition to exclude zeroes, i.e.

    =AVERAGEIFS(G:G,G:G,">0",A:A,"PHA")
    Spot on, Thank you

    One last one, just need to add in one further criteria to the above

    the average also needs to be based on figures in column H (including zeros) so, G & H together against A.

  6. #6
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: AverageIf based on another columns criteria

    Do you want the average of the total of columns G and H? You won't be able to use AVERAGEIFS for something like that, you need an "array formula" like this

    =AVERAGE(IF(A1:A100="PHA",IF(G1:G100+H1:H100>0,G1:G100+H1:H100)))

    confirmed with CTRL+SHIFT+ENTER

  7. #7
    Registered User
    Join Date
    07-06-2010
    Location
    Beckenham
    MS-Off Ver
    Excel 2007
    Posts
    84

    Re: AverageIf based on another columns criteria

    Quote Originally Posted by daddylonglegs View Post
    Do you want the average of the total of columns G and H? You won't be able to use AVERAGEIFS for something like that, you need an "array formula" like this

    =AVERAGE(IF(A1:A100="PHA",IF(G1:G100+H1:H100>0,G1:G100+H1:H100)))

    confirmed with CTRL+SHIFT+ENTER
    No, I'm not explaining this very well (Can't post the sheet due to data protction act)

    This is what I want but, it needs to check column E which must =PHA. If column E does not =PHA, then this must not be included.

    =AVERAGEIF(Properties!BU3:BU350,">0",Properties!BV3:BV350)

    I know this won't help, but the answer is -13.48 lol.

    =AVERAGEIF(Properties!E3:E350,"PHA",Properties!BV3:BV350)

    with this, I get -8.75
    Last edited by AllenMead; 09-06-2010 at 12:13 PM.

  8. #8
    Registered User
    Join Date
    07-06-2010
    Location
    Beckenham
    MS-Off Ver
    Excel 2007
    Posts
    84

    Re: AverageIf based on another columns criteria

    I'm stupid, sorted. Thanks very much for your help.

    Final equation;

    =AVERAGEIFS(Properties!BV3:BV350,Properties!BU3:BU350,">0",Properties!E3:E350,"PHA")

+ 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