Automatically distributing quiz scores

With the respective points in C1:I1, C3=$I3*C$1/$I$1, copy across and down.

Originally Posted by josephteh

With the respective points in C1:I1, C3=$I3*C$1/$I$1, copy across and down.

Thank you for your response. However, what I'm looking for is not exactly this. If I do it this way, the scoring system for all those who score 60 will be the same. I want it to be more varied. If out of 700 students, 100 of them score 60, and they all get the same score on the same questions, it could cause a bit of a problem for me

A heuristic method,

=LET(
     no,FILTER(A3:A100,A3:A100<>""),
     score,FILTER(I3:I100,I3:I100<>""),
     DROP(REDUCE(0,SEQUENCE(ROWS(no)),LAMBDA(m,n,VSTACK(m,LET(
     data,DROP(REDUCE(0,SEQUENCE(50),LAMBDA(x,y,LET(a,RANDBETWEEN(0,$C$1),b,RANDBETWEEN(0,$D$1),c,RANDBETWEEN(0,$E$1),d,RANDBETWEEN(0,$F$1),e,RANDBETWEEN(0,$G$1),f,INDEX(score,n)-a-b-c-d-e,VSTACK(x,HSTACK(a,b,c,d,e,f))))),1),
     TAKE(FILTER(data,(INDEX(data,,6)>=0)*(INDEX(data,,6)<=$H$1)),1)
     )))),1)
  )

Amended formula. Remember to input score in column I.

=LET(
     no,FILTER(A3:A100,A3:A100<>""),
     score,FILTER(I3:I100,I3:I100<>""),
     DROP(REDUCE(0,SEQUENCE(ROWS(score)),LAMBDA(m,n,VSTACK(m,LET(
     data,DROP(REDUCE(0,SEQUENCE(100),LAMBDA(x,y,LET(a,RANDBETWEEN(0,$C$1),b,RANDBETWEEN(0,$D$1),c,RANDBETWEEN(0,$E$1),d,RANDBETWEEN(0,$F$1),e,RANDBETWEEN(0,$G$1),f,INDEX(score,n)-a-b-c-d-e,VSTACK(x,HSTACK(a,b,c,d,e,f))))),1),
     TAKE(FILTER(data,(INDEX(data,,6)>=0)*(INDEX(data,,6)<=$H$1)),1)
     )))),1)
  )

Thank you, but there's an issue. When I copy all the grades to cell I3, it gives a #CALC! error, and the columns appear empty. What could be the reason for this?

Amended formula 2.

=LET(
     no,FILTER(A3:A100,A3:A100<>""),
     score,FILTER(I3:I100,I3:I100<>""),
     DROP(REDUCE(0,SEQUENCE(ROWS(score)),LAMBDA(m,n,VSTACK(m,LET(
     data,DROP(REDUCE(0,SEQUENCE(100),LAMBDA(x,y,LET(h,INDEX(score,n),a,RANDBETWEEN(MAX(0,$C$1-100+h),$C$1),b,RANDBETWEEN(MAX(0,$D$1-100+h),$D$1),c,RANDBETWEEN(MAX(0,$E$1-100+h),$E$1),d,RANDBETWEEN(MAX(0,$F$1-100+h),$F$1),e,RANDBETWEEN(MAX(0,$G$1-100+h),$G$1),f,h-a-b-c-d-e,VSTACK(x,HSTACK(a,b,c,d,e,f))))),1),
     IFERROR(TAKE(FILTER(data,(INDEX(data,,6)>=0)*(INDEX(data,,6)<=$H$1)),1),"")
     )))),1)
  )

thanks bro

You are welcome. ：）
-----------------------------------
If that takes care of your original question, please click on "Thread Tools" from the menu link above and mark this thread as SOLVED.