Search code examples
arraysif-statementgoogle-sheetsgoogle-sheets-formulaarray-formulas

How to find Curved Grade in Google Sheets?

I'm working on a file where final grades of all courses are added, every student has the right to get up to 5 grades as curve for all of his/her final grades with are lower than Passing Grade. My question is: is it possible to find new final grades with added grades by a Formula and/or Script in Google Sheets. Below is a sample of the data

studentName         course          final           final (withCurve)
A                    Math             59              60
A                    Physics          58              60
A                    English          54              54
B                    English          55              55
B                    Physics          58              60
B                    French           57              60

Note: Assume the passing Grade is 60 and give the 5 grades to more courses as possible, which means better to give it to 57, 58 than one single 55 (if it is possible).

Solution

  • as requested the passing point is 60 and awarder are only 5 bonus points only to students that fail in particular subjects and those bonus points are distributed to cap up the passing value

    so, the formula would be:

    =ARRAYFORMULA(IFNA(VLOOKUP(A3:A&B3:B, QUERY(SPLIT(TRANSPOSE(SPLIT(
 QUERY(TRANSPOSE(QUERY(TRANSPOSE("♦"&QUERY(TRANSPOSE(QUERY({
 SORT(FILTER(A3:C, C3:C<60, C3:C>54), 1, 1, 3, 1), 
 SORT(FILTER(ROW(A3:A), C3:C<60, C3:C>54), 1, 0)}, 
 "select max(Col3) where Col1 is not null group by Col4 pivot Col1")), "select Col1", 0)&
 QUERY(TRANSPOSE(QUERY(QUERY({SORT(FILTER(A3:C, C3:C<60, C3:C>54), 1, 1, 3, 1), 
 SORT(FILTER(ROW(A3:A), C3:C<60, C3:C>54), 1, 0)}, 
 "select Col2,max(Col3) where Col1 is not null group by Col4,Col2 pivot Col1"), 
 "offset 1", 0)), "limit 0", 1)&"♠"&
 IFNA(REGEXREPLACE(TO_TEXT(TRANSPOSE(QUERY(QUERY({SORT(FILTER(A3:C, C3:C<60, C3:C>54), 1, 1, 3, 1), 
 SORT(FILTER(ROW(A3:A), C3:C<60, C3:C>54), 1, 0)}, 
 "select max(Col3) where Col1 is not null group by Col4 pivot Col1"), "offset 1", 0))), "^60$", )+IFNA(
 IF(IFERROR(QUERY(1*REGEXREPLACE(TO_TEXT(60-TRANSPOSE(QUERY(QUERY({SORT(FILTER(A3:C, C3:C<60, C3:C>54), 1, 1, 3, 1),
 SORT(FILTER(ROW(A3:A), C3:C<60, C3:C>54), 1, 0)}, 
 "select max(Col3) where Col1 is not null group by Col4 pivot Col1"), "offset 1", 0))), "^60$", ), 
 "select Col1+Col2+Col3+Col4+Col5 label Col1+Col2+Col3+Col4+Col5''"), 6)<=5, 
 REGEXREPLACE(TO_TEXT(60-TRANSPOSE(QUERY(QUERY({SORT(FILTER(A3:C, C3:C<60, C3:C>54), 1, 1, 3, 1), 
 SORT(FILTER(ROW(A3:A), C3:C<60, C3:C>54), 1, 0)}, 
 "select max(Col3) where Col1 is not null group by Col4 pivot Col1"), "offset 1", 0))), "^60$", ),
 IF(IFERROR(QUERY(1*REGEXREPLACE(TO_TEXT(60-TRANSPOSE(QUERY(QUERY({SORT(FILTER(A3:C, C3:C<60, C3:C>54), 1, 1, 3, 1),
 SORT(FILTER(ROW(A3:A), C3:C<60, C3:C>54), 1, 0)}, 
 "select max(Col3) where Col1 is not null group by Col4 pivot Col1"), "offset 1", 0))), "^60$", ), 
 "select Col1+Col2+Col3+Col4 label Col1+Col2+Col3+Col4''"), 6)<=5, 
 REGEXREPLACE(TO_TEXT(60-TRANSPOSE(QUERY(QUERY({SORT(FILTER(A3:C, C3:C<60, C3:C>54), 1, 1, 3, 1), 
 SORT(FILTER(ROW(A3:A), C3:C<60, C3:C>54), 1, 0)}, 
 "select max(Col3) where Col1 is not null group by Col4 pivot Col1"), "offset 1", 0))), "^60$", ), 
 IF(IFERROR(QUERY(1*REGEXREPLACE(TO_TEXT(60-TRANSPOSE(QUERY(QUERY({SORT(FILTER(A3:C, C3:C<60, C3:C>54), 1, 1, 3, 1),
 SORT(FILTER(ROW(A3:A), C3:C<60, C3:C>54), 1, 0)}, 
 "select max(Col3) where Col1 is not null group by Col4 pivot Col1"), "offset 1", 0))), "^60$", ), 
 "select Col1+Col2+Col3 label Col1+Col2+Col3''"), 6)<=5, QUERY(
 REGEXREPLACE(TO_TEXT(60-TRANSPOSE(QUERY(QUERY({SORT(FILTER(A3:C, C3:C<60, C3:C>54), 1, 1, 3, 1),
 SORT(FILTER(ROW(A3:A), C3:C<60, C3:C>54), 1, 0)}, 
 "select max(Col3) where Col1 is not null group by Col4 pivot Col1"), "offset 1", 0))), "^60$", ), 
 "select Col1,Col2,Col3"),
 IF(IFERROR(QUERY(1*REGEXREPLACE(TO_TEXT(60-TRANSPOSE(QUERY(QUERY({SORT(FILTER(A3:C, C3:C<60, C3:C>54), 1, 1, 3, 1),
 SORT(FILTER(ROW(A3:A), C3:C<60, C3:C>54), 1, 0)}, 
 "select max(Col3) where Col1 is not null group by Col4 pivot Col1"), "offset 1", 0))), "^60$", ), 
 "select Col1+Col2 label Col1+Col2''"), 6)<=5, QUERY(
 REGEXREPLACE(TO_TEXT(60-TRANSPOSE(QUERY(QUERY({SORT(FILTER(A3:C, C3:C<60, C3:C>54), 1, 1, 3, 1),
 SORT(FILTER(ROW(A3:A), C3:C<60, C3:C>54), 1, 0)}, 
 "select max(Col3) where Col1 is not null group by Col4 pivot Col1"), "offset 1", 0))), "^60$", ),
 "select Col1,Col2"), 
 IF(IFERROR(QUERY(1*REGEXREPLACE(TO_TEXT(60-TRANSPOSE(QUERY(QUERY({SORT(FILTER(A3:C, C3:C<60, C3:C>54), 1, 1, 3, 1), 
 SORT(FILTER(ROW(A3:A), C3:C<60, C3:C>54), 1, 0)}, 
 "select max(Col3) where Col1 is not null group by Col4 pivot Col1"), "offset 1", 0))), "^60$", ), 
 "select Col1 "),6)<=5, QUERY(
 REGEXREPLACE(TO_TEXT(60-TRANSPOSE(QUERY(QUERY({SORT(FILTER(A3:C, C3:C<60, C3:C>54), 1, 1, 3, 1),
 SORT(FILTER(ROW(A3:A), C3:C<60, C3:C>54), 1, 0)}, 
 "select max(Col3) where Col1 is not null group by Col4 pivot Col1"), "offset 1", 0))), "^60$", ) ,
 "select Col1"), ))))), 0), 0)),,999^99)),,999^99), "♦")), "♠"), "where Col2 !=0"), 2, 0), C3:C))

    0

    spreadsheet demo