Problem creating a problem with MINA - newbie!!

[Jazz M]

Hello all,
I am looking for some advice on how to set up a formula to allow me enter student marks for answering parts of a question

The question 3 parts (each part of the question has 5 marks each) and a student may answer any two parts. their marks are capped 10 marks so the maximum that can be earned is 10 marks

However I have come across students who have answered all 3 parts but I need a formula that only takes the best marks from 2 of the 3 parts only.

I was experimenting with MINA and subtracting in my formula but I got a bit confused, here is my formula
=SUM(A3:G3)-MINA(SUM(C3:E3),F3,G3)

here is what it looks like in Excel

[TABLE="width: 549"]
<tbody>[TR]
[TD="colspan: 3"]Answer any 2 of 3 parts[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Q3 Part(A1) 3marks[/TD]
[TD]Q3 Part(A2) 2marks[/TD]
[TD]Q3 Part(B1) 3marks[/TD]
[TD]Q3 Part(B2) 1mark[/TD]
[TD]Q3 Part(B3) 1mark[/TD]
[TD]Q3 Part(C1) 2marks[/TD]
[TD]Q3 Part(C2) 3marks[/TD]
[TD]Q3 Total Max 10 marks[/TD]
[/TR]
[TR]
[TD] A3[/TD]
[TD]B3[/TD]
[TD]C3[/TD]
[TD]D3[/TD]
[TD]E3[/TD]
[TD]F3[/TD]
[TD]G3[/TD]
[TD]H3[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I would be grateful for any help folks, thanks in advance
Jazz M

 

[Jazz M]

Any inspiration folks ?

 

[Joe4]

Welcome to the Board!

Shouldn't it be:

[COLOR=#333333]=SUM(A3:G3)-MINA(SUM(A3:B3),SUM(C3:E3),SUM(F3:G3))[/COLOR]

 

[Jazz M]

Thanks Joe4, I'll try that.

Welcome to the Board!

Shouldn't it be:

[COLOR=#333333]=SUM(A3:G3)-MINA(SUM(A3:B3),SUM(C3:E3),SUM(F3:G3))[/COLOR]

 

[Jazz M]

Thanks Joe4, I'll try that.

Hi Joe4

That didnt quite solve it.

The Students are only supposed to answer two out of 3 sections as the max they can earn is 5 marks per section, so two sections = 10 marks.

Now a lot are answering the 3 parts but i need to limit their score to the best attempt out of the 3 sections and hence remove the 'worst' marks from the poorest answered section as there is only 10 marks available in total for this question.

Thanks again.
Jazz M

 

[Joe4]

That didnt quite solve it.

It should.

Based on your description of the problem, it looks like your three mark bands are:
A3:B3
C3:E3
F3:G3

So how do we get the best two? Add them ALL up (A3:G3) and remove the smallest of the three mark bands.
That is what the formula I posted should do. Unless there is something about the data in A3:G3 that you are not telling us (like it needs to be multiplied by some "weighting factor").

If the formula does not return what you expect, please provide an actual data example where it is not working, along with your expected results.

 

[Marcelo Branco]

Not sure i understand what you looking for.

See if this does what you need
=SUM(LARGE(CHOOSE({1,2,3},SUM(A3:B3),SUM(C3:E3),SUM(F3:G3)),{1;2}))

M.

 

[Jazz M]

It should.

Based on your description of the problem, it looks like your three mark bands are:
A3:B3
C3:E3
F3:G3

So how do we get the best two? Add them ALL up (A3:G3) and remove the smallest of the three mark bands.
That is what the formula I posted should do. Unless there is something about the data in A3:G3 that you are not telling us (like it needs to be multiplied by some "weighting factor").

If the formula does not return what you expect, please provide an actual data example where it is not working, along with your expected results.

I do apologise Joe, I have just re ran my data set and that does indeed appear to have solved it. Thank you for taking the time, you have helped increase productivity significantly !!

Regards,
Jazz M

 

[Jazz M]

Not sure i understand what you looking for.

See if this does what you need
=SUM(LARGE(CHOOSE({1,2,3},SUM(A3:B3),SUM(C3:E3),SUM(F3:G3)),{1;2}))

M.

Marcelo, thank you for the reply, I will also try this.

Kind regards
Jazz M

 

[Joe4]

You are welcome.
Glad it worked!