Exclude a range of cells from a list, and find Average

**nberzai** · 12-17-2010, 03:46 PM

Say I have a list for A1:A10. The numbers are A1 = 1, A2 = 2... to A10 = 10.

In column B I have the numbers 5 and 8. B1 and B2 respectively.

I want to have a formula that calculates the average without B1 and B2. The problem comes in when I blow the ranges up. Say A1:A500, and in the B column I have B1:B315. I want the formula to look in the range of B and compare that to the list in A, and take the average of A without the range of numbers in B. Hope this is clear. (Can it be done without using an array?)

Cheers,

Nate

**NBVC** · 12-17-2010, 03:50 PM

Try:

=AVERAGE(IF(ISNA(MATCH(A1:A10,B1:B2,0)),A1:A10))

confirmed with CTRL+SHIFT+ENTER not just ENTER

**Cutter** · 12-17-2010, 03:50 PM

Wouldn't it be:

=(SUM(A1:A500)-SUM(B1:B315))/(COUNT(A1:A500)-COUNT(B1:B315))

**nberzai** · 12-17-2010, 04:24 PM

Looks good on both fronts.

Cutter, how would I find the max? I can use the array formula above by just substituting max for average, but this won't work for yours... Any other ideas?