+ Reply to Thread
Results 1 to 7 of 7

Average IF

  1. #1
    Registered User
    Join Date
    06-24-2009
    Location
    Royersford, Pa
    MS-Off Ver
    Excel 2007
    Posts
    89

    Average IF

    I need to find the average of a column I have labled as "Difference in Pay Rates" IF column M = a specific provider and if column G = a specific position and IF column Q <> "Not Changed".

    I have the formulas to count rows based on values in multiples columns but I'm not sure how to use the subtotal average formula in the same situation.

    Any help would be appreciated.Thanks
    Last edited by Cjax; 07-27-2009 at 12:32 PM.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Subtotal IF

    You should only really need to use SUBTOTAL if you're hiding rows via Filter ?

    If this is not the case and given you're using XL2007 you can possibly use AVERAGEIFS function:

    =AVERAGEIFS($X$1:$X$1000,$M$1:$M$1000="Provider",$G$1:$G$1000,"Position",$Q$1:$Q$1000,"<>Not Changed")

    change that in red to reflect the range containing the values to average... try to avoid using entire column references in your formula - ie keep ranges to a minimum.

    If you are hiding rows via filters etc let us know...

  3. #3
    Registered User
    Join Date
    06-24-2009
    Location
    Royersford, Pa
    MS-Off Ver
    Excel 2007
    Posts
    89

    Re: Subtotal IF

    Thanks Don - I'm going to give the AverageIFS a try - I have a formula in there that will average based on my filters but I want to have constant averages remain for certain clients. I should have made that more clear. I'll let you know how this works. Thanks again

  4. #4
    Registered User
    Join Date
    06-24-2009
    Location
    Royersford, Pa
    MS-Off Ver
    Excel 2007
    Posts
    89

    Re: Subtotal IF

    The AVERAGEIFS formula works perfectly. I think there is one error in the formula that wrote in the post above though.

    Here is the formula you wrote with the potential error bolded: =AVERAGEIFS($X$1:$X$1000,$M$1:$M$1000="Provider",$G$1:$G$1000,"Position",$Q$1:$Q$1000,"<>Not Changed") I believe that bolded = sign should be replaced with a ,

    Thanks again for the help

  5. #5
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Subtotal IF

    oh yes, apologies, I was in Sumproduct overdrive at the time... the = operator should indeed be a comma (or region appropriate argument delimiter!)

  6. #6
    Registered User
    Join Date
    06-24-2009
    Location
    Royersford, Pa
    MS-Off Ver
    Excel 2007
    Posts
    89

    Re: Subtotal IF

    Just one more question - should I change the title of this post to Average IF or is it ok as is?

  7. #7
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Subtotal IF

    that would perhaps be a better fit but as is is ok if you're pushed for time...

+ 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