formula to restrict marks with in 25

0

hi my question is

for internal assessment test

no of questions given =8

max marks for each question =5 {for theory subjects} and students should answer any  5 questions but some students will answer more than 5 questions..for this i have written a formula

 =SUMPRODUCT(LARGE((F9:M9>=0)*F9:M9,{1,2,3,4,5}))

using this formula i got expected result i.e within 25 marks

NOW 

for some mathematical subjects the allocation of marks will differ like

Q1=5, Q2=6, Q3=10, Q4=7, Q5=12.5, Q6=10, Q7=9, Q8=10

what is the formula i will have to write in excel for this type of problem, so that i should get the total of 25 marks no matter which ever and how many questions (i.e within 8 questions)  the student is going to answer.

Answer
Discuss

Answers

0

Surround it with the MIN() function:

=MIN(25,SUMPRODUCT(LARGE((F9:M9>=0)*F9:M9,{1,2,3,4,5})))
Discuss

Discussion

THANK YOU VERY MUCH, IT WORKED
manjukb Feb 23, '17 at 10:59 am
If this worked, please select it as the answer. That helps others find the answer if they have a similar question.
don (rep: 1482) Feb 23, '17 at 12:41 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login