0

I am trying to help develop a grading scale which determines the overall grade for a student with 5 assignments entered in A (4) to F (0) with no plusses or minuses. I would like to see each possible combination with no repeats (i.e. A, A, A, A, F is treated the same as F, A, A, A, A). It would be preferable to have them in separate columns so that I can see their averages.

Assign. 1 Assign. 2 Assign. 3 Assign. 4 Assign. 5 Average
4 4 4 4 4 4
4 4 4 4 3 3.8
4 4 4 4 2 3.6
4 4 4 4 1 3.4
4 4 4 4 0 3.2

With the above table to expand and include all possible combinations of the assignment grades.

I have tried using the array formula with the permut function: =ARRAYFORMULA(PERMUT(5,5)) but it returns the number of combinations but won't print them. Is there a way to do this?

Bryce Towle
  • 3
  • 3
  • Do NOT share [spreadsheets](//meta.stackoverflow.com/a/260455)/[images](//meta.stackoverflow.com/q/285551) as the only source of data, to avoid closure of the question. Make sure to add input and expected output as **plain text table** to the question. [Click here](//webapps.stackexchange.com/a/161855) to create a table easily, which are **easier to copy/paste as well**. Also, note that [your email address can also be accessed by the public](//meta.stackoverflow.com/q/394304), if you share Google files. – TheMaster Apr 07 '23 at 14:47

1 Answers1

0

The below approach is extremely inefficient but does generate the specified answer:

=arrayformula(let(
assignments,5,
grades,5,
splitdigits,lambda(x,mid(x,sequence(1,assignments),1)),
permuts,base(sequence(grades^assignments,1,0),grades,grades),
splitpermuts,1*splitdigits(permuts),
combos,unique(byrow(splitpermuts,lambda(row,join(,large(row,sequence(1,assignments)))))),
splitcombos,1*splitdigits(combos),
{{"Assign. "&sequence(1,assignments),"Average"};
{splitcombos,byrow(splitcombos,lambda(row,average(row)))}}))

We are using some maths to generate every permutation of grades, then extracting all the combinations by sorting the grades per row and taking the unique list of the rows. The base/sequence approach is taken from TheMaster's answer here: How to list all permutations without repetition?

The God of Biscuits
  • 2,029
  • 2
  • 3
  • 10
  • This is awesome! Does exactly what I need. I try to adjust the number of assignments and it does not seem to like that; is there a way to adjust for more assignments? What you've provided is excellent, just wondering as I try to dissect it. – Bryce Towle Apr 10 '23 at 17:44
  • I noticed a typo in the formula - was assignments^grades previously but should be grades^assignments to calculate all the permutations (I've changed it now and it seems to respond correctly to changes in the number of assignments or grades). Wasn't obvious previously as both assignments & grades were set to 5... – The God of Biscuits Apr 10 '23 at 18:10