+ Reply to Thread
Results 1 to 9 of 9

average the numbers in a column but exclude some values based on other column data

  1. #1
    Registered User
    Join Date
    05-21-2009
    Location
    Pittsburgh, US
    MS-Off Ver
    Excel 2003
    Posts
    7

    average the numbers in a column but exclude some values based on other column data

    I'm having trouble getting a formula to work that will average numbers in a column while excluding some data based on the data in another column.

    I think I'm having trouble because I'm seeing some formulas startout with =AVERAGEIF and others starting out with =AVERAGE(IF ...

    [Well, to be honest, since I can't get either approach to work, maybe that isn't the problem.]

    Anyway, here's the situation:
    • I want to average some of the numbers that appear in column AC (data in cells AC2:AC148)
    • I want to exclude any numbers that correspond to rows that have an "y" in F2:F148.


    Thanks in advance for your time.
    Last edited by MrZ113; 02-16-2011 at 05:01 PM.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: average the numbers in a row but exclude some values based on other row data

    Try this

    Please Login or Register  to view this content.
    AVERAGEIF is not available in 2003 but if you ever get >=2007 you can try
    Please Login or Register  to view this content.
    Last edited by 6StringJazzer; 02-16-2011 at 04:47 PM. Reason: AVERAGEIF not available in 2003
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: average the numbers in a row but exclude some values based on other row data

    P.S. Check your title, you are averaging numbers in a column

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: average the numbers in a row but exclude some values based on other row data

    6SJ.. the countif part shouldn't have the 3rd arguement

    =SUMIF(F2:F148,"<>y",AC2:AC148)/COUNTIF(F2:F148,"<>y",AC2:AC148)

    s/b

    =SUMIF(F2:F148,"<>y",AC2:AC148)/COUNTIF(F2:F148,"<>y")
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  5. #5
    Registered User
    Join Date
    05-21-2009
    Location
    Pittsburgh, US
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: average the numbers in a row but exclude some values based on other row data

    I get an error message that "too many arguments were entered for this function" when I try the =sumif approach ...

  6. #6
    Registered User
    Join Date
    05-21-2009
    Location
    Pittsburgh, US
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: average the numbers in a row but exclude some values based on other row data

    Quote Originally Posted by 6StringJazzer View Post
    P.S. Check your title, you are averaging numbers in a column
    Thanks. I couldn't find a way to correct the thread title. Do only mods have access to fix thread titles?

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: average the numbers in a row but exclude some values based on other row data

    See post # 4 above...

  8. #8
    Registered User
    Join Date
    05-21-2009
    Location
    Pittsburgh, US
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: average the numbers in a row but exclude some values based on other row data

    Quote Originally Posted by NBVC View Post
    6SJ.. the countif part shouldn't have the 3rd arguement

    =SUMIF(F2:F148,"<>y",AC2:AC148)/COUNTIF(F2:F148,"<>y",AC2:AC148)

    s/b

    =SUMIF(F2:F148,"<>y",AC2:AC148)/COUNTIF(F2:F148,"<>y")
    The boldfaced one worked like a charm. Thank you very much, everyone.

    Thanks to you, college students can determine the number of sexual partners that the average, sexually active student on campus has had.

  9. #9
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: average the numbers in a row but exclude some values based on other row data

    Quote Originally Posted by NBVC View Post
    6SJ.. the countif part shouldn't have the 3rd arguement
    Very sorry about that, thanks for the correction. Got lazy and didn't test it.

+ 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