Newbie with newbie question

[elgrandekazoo]

Would like to create a high school golf tournament scoring program. Need to add the 4 lowest scores of each team to create a team score. Each team will have either 4 or 5 members. How do you get Excel to add the 4 lowest numbers, regardless of the numbers of the team membership?

[BenjieLop]

Would like to create a high school golf tournament scoring program. Need to add the 4 lowest scores of each team to create a team score. Each team will have either 4 or 5 members. How do you get Excel to add the 4 lowest numbers, regardless of the numbers of the team membership?

Use this formula ...

=sum(small(A:A,{1,2,3,4}))


Regards.

[elgrandekazoo]

Thank you for the information. Plugged it in, made adjustments for values of A:A & it works.

[elgrandekazoo]

Have thought of another question in the same vein. Suppose I have 4 teams [W,X,Y,Z]. Right now, I have to plug in the A:A values for each team. Is it possible to adjust the formula =SUM(SMALL(A:A,{1,2,3,4})) to ask it to find scores by the team name, then add the scores together?

[BenjieLop]

Have thought of another question in the same vein. Suppose I have 4 teams [W,X,Y,Z]. Right now, I have to plug in the A:A values for each team. Is it possible to adjust the formula =SUM(SMALL(A:A,{1,2,3,4})) to ask it to find scores by the team name, then add the scores together?

Here is a suggestion. ASSUME that the scores of the following teams are listed as follows:

Team W : Column A:A

Team X : Column B:B

Team Y : Column C:C

Team Z : Column D:D

The team name whose top 4 scores you want to add up will be in Cell E1.

Then, in Cell F1, enter the following formula:

=if(E1="Team W",sum(small(A:A,{1,2,3,4})),if(E1="Team X",sum(small(B:B,{1,2,3,4})),if(E1="Team Y",sum(small(C:C,{1,2,3,4})),if(E1="Team Z",sum(small(D:D,{1,2,3,4})),""))))

There maybe a more elegant solution to the above but this will work for the meantime.


Regards.

[BenjieLop]

Have thought of another question in the same vein. Suppose I have 4 teams [W,X,Y,Z]. Right now, I have to plug in the A:A values for each team. Is it possible to adjust the formula =SUM(SMALL(A:A,{1,2,3,4})) to ask it to find scores by the team name, then add the scores together?

Or, if you want ... here's another format:

ASSUME that the scores are entered as follows:

Team W scores are entered in Cells A1:A10;
Team X scores are entered in Cells B1:B10;
Team Y scores are entered in Cells C1:C10;
Team Z scores are entered in Cells D1:D10

Then, in the following cells enter the ff formulas:

Cell A11: =sum(small(A1:A10,{1,2,3,4}))

Cell B11: =sum(small(B1:B10,{1,2,3,4}))

Cell C11: =sum(small(C1:C10,{1,2,3,4}))

Cell D11: =sum(small(D1:D10,{1,2,3,4}))

Hope this will help you.

Regards.