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.

0

Surround it with the MIN() function:

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

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: 1665) Feb 23, '17 at 12:41 pm