display lowest 4 values from 6

I am looking to display the lowest 4 placing values that meet a team criteria. The input looks like this:

TEAM Placing

ABU - Ind
HC - Ind
STU 1
STU 2
MTA 3
MTA 4
MTA 5
USA 6
MTA - Ind
ABU - Ind
HC - Ind
USA 7
STU 8
STU 9
MTA 10
MTA 11
MTA 12
MTA - Ind
STU 13
USA 14
STU 15
ABU - Ind
USA 16


The output must look like this:

Place Team Score 1st runner 2nd runner 3rd runner 4th runner
1 STU 20pts 1 2 8 9
2 MTA 22pts 3 4 5 10
3 USA 43pts 6 7 14 16

But I cannot seem to find a combination of formulae that extracts the top 4 placings by team. Any ideas?

Assuming your data, teams and placings, is in A2:B24 then, with a specific team name in D2 you can use this formula in F2

=SMALL(IF($A$2:$A$24=$D2,$B$2:$B$24), COLUMNS($F2:F2))

This is an "array formula" which must be confirmed with CTRL+SHIFT+ENTER, then copy across to I2

Get the total points in E2 with

=SUM(F2:I2)

You can add additional teams in column D and copy all formulas down

This seems to work quite well if the placings are all sequential, but does not seem to work if there are gaps (empty cells). Is there a way around this?

If you have blanks in the "Placings" column, B2:B24 in my example, change formula in F2 to

=SMALL(IF($A$2:$A$24=$D2,IF ($B$2:$B$24<>"",$B$2:$B$24)),COLUMNS($F2:F2))

...also if you want to prevent errors in the scenario where a team has less than 4 placings make a further change to

=IF(COLUMNS($F2:F2)>SUM(($A$2:$A$24=$D2)*(ISNUMBER($B$2:$B$24))),"",SMALL(IF($A$2:$A$24=$D2,IF($B$2:$B$24<>"",$B$2:$B$24)), COLUMNS($F2:F2)))

This is brilliant - excatly what I am looking for. However there is one more item that needs to be figured out. I have another forumla which is giving the values in both columns A & B, so some of them are 'empty' and showing #N/A. So those formulae seem to be given a value of zero in a set of data with #N/A is present. Is there a way to tell the formulae to ignore #N/A entries?

It would probably be easier to change the formulas in columns A and B to return blanks rather than #N/A, what formulas do you have returning #N/A?

Yes, I guess you are right. Right now I have:

=LOOKUP(A29,Entries!$A$4:$E$387)

Which takes values from a database on another tab/sheet

What's in A29? That formula can give a "closest match" result even if A29 isn't found in Entries!$A$4:$A$387. If you only want an exact match, without #N/A try

=IF(LOOKUP(A29,Entries!$A$4:$A$387)=A29,LOOKUP(A29,Entries!$A$4:$E$387),"")

OK, sorry, better to use VLOOKUP if you only want an exact match, i.e.

=IF(ISNA(MATCH(A29,entries!$A$4:$A$387,0)),"", VLOOKUP(A29,entries!$A$4:$E$387,5,0))

This formula doesn't require CTRL+SHIFT+ENTER

Okay, this is definitely steps in the right direction, but there is still some bugs to work out. I'll admit this code is now way over my head - now some of the info comes back as #REF!, and the team scoring does not work.

Maybe it is easier for you to take a look at the spreadsheet itself? I have attached it if you want to take a look at it. Your help is greatly appreciated to this point!

You need to extend your lookup range beyond column E to pick up the values in Col F onward. That should get rid of the #REF.

Dean

A slight adjustment again, if you want the VLOOKUP across multiple columns its probably easier to use INDEX/MATCH then the column references will change automatically as you drag the formula across.

In 'Event #1 Results'!E8 use the formula

=IF(ISNA(MATCH($A8,Entries!$A$6:$A$389,0)),"", INDEX(Entries!B$6:B$389,MATCH($A8, Entries!$A$6:$A$389,0)))

copy across to K8 and down the columns,

in L8

=IF(ISNA(MATCH($A8,Entries!$A$6:$A$389,0)),"", INDEX(Entries!J$6:J$389,MATCH($A8, Entries!$A$6:$A$389,0)))

copied down

Now in 'Team Results'!D14

=SMALL(IF('Event #1 Results'!$G$8:$G$80=$B14,IF('Event #1 Results'!$C$8:$C$80<>"",'Event #1 Results'!$C$8:$C$80)),COLUMNS($D14:D14))

confirmed with CTRL+SHIFT+ENTER and copied down and across

unbelievable daddy. It's amazing how you do that so quickly! This works perfectly. Thank you for being so patient with this issue!

Another issue I am having - if you still have the patience to take a look at it, is to take and average time of each runner who counted for points in the team placings.

For example, the average time for the "STU" team would be 25:26. Keeping in mind that the time is 60sec/min so the output should calculate with an output that does nto have decimals (i.e. 25:26, not 25.43min)

Attached is a cleaned up version of the spreadsheet. Hopefully you know what I mean.

Try a formula like

=AVERAGE(IF(G2:G100="STU",IF(D2:D100,D2:D100)))

confirmed with CTRL+SHIFT+ENTER

format result cell as h:mm:ss or similar

yes, this works for all "STU" entries, which I actually figured out myslef (yipee!). but how would I go about averaging only the top 4 placing times for "STU"?

Would you really have runners placed but with no times like in 'Event #1 Results'!D13:D15?

no you would not. I have simply setup dummy stats to test the functions. Call it laziness for not filling out everything completely :-)

OK, try this formula in 'Team Results'!I15

=AVERAGE(IF('Event #1 Results'!$G$8:$G$80=$B15,IF('Event #1 Results'!$C$8:$C$80<=MAX(D15:H15),'Event #1 Results'!$D$8:$D$80)))

confirmed with CTRL+SHIFT+ENTER

thanks! I've learnt a lot of new rules - that I knew were possible with excel, but had no idea how to implement them!