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
Last edited by nberzai; 12-20-2010 at 12:01 PM.
Try:
=AVERAGE(IF(ISNA(MATCH(A1:A10,B1:B2,0)),A1:A10))
confirmed with CTRL+SHIFT+ENTER not just ENTER
Microsoft MVP - Excel
Where there is a will there are many ways. Pick One!
Please read the Forum Rules
If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below
Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
Preferred Charities: Lupus Canada and Sick Kids Foundation.
Feel Free to Donate if you want to, for the assistance you received today.
Wouldn't it be:
=(SUM(A1:A500)-SUM(B1:B315))/(COUNT(A1:A500)-COUNT(B1:B315))
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?
By the way, thanks for the quick response and the answer!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks