Need help on Auto calculating aggreggates

[KOOLMAN]

Please, I made a simple template to take grades of my pupils. In the templates, some subjects on a continuum are compulsory (H - K) while the remaining (L- P) are optional, but 2 of them (best 2 with good grades ) to be added to H-K to give the aggregate in Q as indicated in the table below.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]....[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]ENGLISH LANG[/TD]
[TD]MATHS[/TD]
[TD]SCIENCE[/TD]
[TD]SOCIAL[/TD]
[TD]LANG[/TD]
[TD]TECH[/TD]
[TD]MORAL STUDIES[/TD]
[TD]ICT[/TD]
[TD]FRENCH[/TD]
[TD]AGGREGATE[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]KOOL[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]6[/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]13[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]SUUTY[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]8[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]5[/TD]
[TD]15[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]BASH[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]

How do I pick N2 and P2 (for KOOL), M3 and O3 (for SUUTY) , O4 and P4 add it automatically to the range H2:K2 without manually editing the formular by picking N2 and P2?

Thanks so much in advance.

 

[baitmaster]

welcome to the board

Try =SUM(H2:K2)+SMALL(L2:P2,1)+SMALL(L2:P2,2)

 

[KOOLMAN]

Tried but it returns #Value !. Thank you. Any other possible way?

 

[baitmaster]

No, you need to investigate why it's returning an error because it shouldn't

Do any of your input cells contain # value! errors?

Do any of your inputs contain numbers formatted as text, or that are otherwise blank, maybe including spaces? [unlikely to be this. Errors here would cause # Num! errors]

In the formula bar, highlight each section in turn and hit F9 to calculate that part of the formula. Which part creates the error?
- SUM(H2:K2)
- SMALL(L2:P2,1)
- SMALL(L2:P2,2)

 

[KOOLMAN]

It worked! God bless you. At first I didn't close one of the brackets. I'm happy!

 

[KOOLMAN]

Thank you. I just investigated that and it worked.