Using the highest 2 out of 3 values in a formula
 

Hi Guys,

I am preparing a spreadsheet to keep track of my marks at uni.

In one of my units I have two sets of 3 tests (ie 3 reading
comprehension tests and 3 problem solving tests) and only the highest 2
results from each set of tests will contribute towards my final mark.

What I am trying to do is devise a formula so the lowest score in each
set of tests is not included in the calculation (or only the highest
two are included).

The data will be entered into the sheet as follows:

reading test 1 (Cell E5)
problem solving 1 (Cell E6)
reading test 2(Cell E7)
problem solving 2 (Cell E8)
reading test 2 (Cell E9)
problem solving 2 (Cell E10)

I hope I have explained this well enough for someone to assist me with
my endeavours.

Thanks in advance

Chris


--
christopherp
------------------------------------------------------------------------
christopherp's Profile: http://www.excelforum.com/member.php...fo&userid=4162
View this thread: http://www.excelforum.com/showthread...hreadid=518167

Using the highest 2 out of 3 values in a formula
 

Group the reading tests together and the problem solving tests together i.e.
reading E5:E7 and problem solving E8:E10.

In F7 enter: =SUM(E5:E7)-MIN(E5:E7) and copy into F10

This will give you a total mark excluding the lowest in each case

Ewan


"christopherp" wrote:


Hi Guys,

I am preparing a spreadsheet to keep track of my marks at uni.

In one of my units I have two sets of 3 tests (ie 3 reading
comprehension tests and 3 problem solving tests) and only the highest 2
results from each set of tests will contribute towards my final mark.

What I am trying to do is devise a formula so the lowest score in each
set of tests is not included in the calculation (or only the highest
two are included).

The data will be entered into the sheet as follows:

reading test 1 (Cell E5)
problem solving 1 (Cell E6)
reading test 2(Cell E7)
problem solving 2 (Cell E8)
reading test 2 (Cell E9)
problem solving 2 (Cell E10)

I hope I have explained this well enough for someone to assist me with
my endeavours.

Thanks in advance

Chris


--
christopherp
------------------------------------------------------------------------
christopherp's Profile: http://www.excelforum.com/member.php...fo&userid=4162
View this thread: http://www.excelforum.com/showthread...hreadid=518167

Using the highest 2 out of 3 values in a formula
 

put the same tests together as in first answer then use

=large(A1:A3,1)+large(A1:A3,2)


--
robert111
------------------------------------------------------------------------
robert111's Profile: http://www.excelforum.com/member.php...o&userid=31996
View this thread: http://www.excelforum.com/showthread...hreadid=518167

Using the highest 2 out of 3 values in a formula
 

Thanks for the responses guys - I have solved the problem with your
help.

This is the formula I have used and it works fine:

=F24+(SUM(F18,F20,F22)-MIN(F18,F20,F22))+(SUM(F19,F21,F23)-MIN(F19,F21,F23))

F24 is a constant and will be included in the calculation regardless of
other results.

Thanks again

Chris


--
christopherp
------------------------------------------------------------------------
christopherp's Profile: http://www.excelforum.com/member.php...fo&userid=4162
View this thread: http://www.excelforum.com/showthread...hreadid=518167