AverageIF excluding matching columsn over a number

**los318** · 03-01-2011, 11:30 AM

so i'm trying to get mroe accurate data out of my reports. I'm looking for the Average of a column excluding anything higher then a certain mark and equaling the column next to it. Not sure if totally possible. Still new to excel

Example.

A B
10 10
15 17
20 21
35 35
40 41
47 47

So say for htis. I want the average of Column A over the A and the Average of B over B. that i have. But over those numbers. I want the Average of Column A excluding anything 35 and over that equals column B. So Average everything but the 35 and 47. ..And the same for Column B. Thanks for any assistance you can provide

Carlos

edit: i'm not stuck on the AverageIf formula if it needs to be changed, i'm all ears..or eyes....

**NBVC** · 03-01-2011, 11:43 AM

Try:

=AVERAGE(IF((A2:A7<35)+(A2:A7<>B2:B7),A2:A7))

confirmed with CTRL+SHIFT+ENTER not just ENTER

and similarly for column B.

**los318** · 03-01-2011, 02:54 PM

awesome!! One more thing..since the amount verfies per week. sometimes its 20 ..sometimes its 30...can we add a ">=0" statment? That way i can have it out to 40 and not have to adjust the formula every week? Reall thanks! i never would ahve gotten that.

Edit: By the way, that did exactly what i was looking for!! thanks soo much!!