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

1. ## 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.

2. ## 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.``

3. ## 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. ## 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")

5. ## 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. ## Re: average the numbers in a row but exclude some values based on other row data

Originally Posted by 6StringJazzer
P.S. Check your title, you are averaging numbers in a column

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

See post # 4 above...

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

Originally Posted by NBVC
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. ## Re: average the numbers in a row but exclude some values based on other row data

Originally Posted by NBVC
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.

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

#### 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