# Excel 2007 : AVERAGEIFS alternative

1. ## AVERAGEIFS alternative

Hi,

I'm using AVERAGEIFS to count specific data from a sheet using 3 criterea, however I also need to extract the actual data into another calculation, so instead of returning the average I want it to return the list of corisponding data against 3 selection criterea.

ie: I have a list of members who take part in diferent activities which get a score. I want to be able to lookup the member name with a specific activity and then extract the top 10 scores from their total list of scores.

So A1=surname;B1=forname;C1=activity type;D1=score

Everytime a member attends they get an entry for that day, the list has multiple member name entries.

I can obviously achieve the result by filtering then applying a calculation to the filtered result but I need it to be automated because there are a hell of alot of members.

Any suggestion would be welcomed.

Thanks Shaun

2. ## Re: AVERAGEIFS alternative

=Large(IF(A1:A10="Name",D1:D10),1)

confirmed with CTRL+SHIFT+ENTER not just ENTER extracts the Largest score for the name identified...

=Large(IF(A1:A10="Name",D1:D10),2) confirmed with CSE keys gets 2nd largest... etc.

3. ## Re: AVERAGEIFS alternative

Originally Posted by NBVC
=Large(IF(A1:A10="Name",D1:D10),1)

confirmed with CTRL+SHIFT+ENTER not just ENTER extracts the Largest score for the name identified...

=Large(IF(A1:A10="Name",D1:D10),2) confirmed with CSE keys gets 2nd largest... etc.
OK, so presumably I can nest multiple IF statements because I need to select on surname;forname and acivity

rgds Shaun

4. ## Re: AVERAGEIFS alternative

Yes:

e.g

=LARGE(IF(A1:A10="Surname",IF(B1:B10="Forename",IF(C1:C10="Activity",D1:D10))),1)

confirmed with CSE keys

5. ## Re: AVERAGEIFS alternative (SOLVED)

EXCELLENT, Just adjusted it for my main sheet and it works perfectly, sometimes you just miss or blank on the formula.

6. ## Re: AVERAGEIFS alternative (SOLVED)

No problem.

How to mark a thread Solved
Go to the first post
Click edit
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

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