I'm having some trouble with a Google Sheets formula, and as a relative beginner at figuring out these problems, I'm struggling to come up with a solution.
Here is the sample spreadsheet: https://docs.google.com/spreadsheets/d/1F7Qjy3Tg3jOTmK54wQxsfjTk-GEuTmjuE9qm3gpCQm0/edit?usp=sharing
I have a formula in I4:I24 of the included sample Gradebook spreadsheet that I'm having trouble setting a MIN and a MAX for.
Here is the current formula in I4, which I'll use as the example:
=IF(G4="","",MAXA(0,IFS(G4>0,(IF(H4>0,((((TODAY()-G4)/7)*2.78)/100),"")))))
The I column in the I4:I24 range is intended to calculate the expected progress through a course based on a student's start date in the G column. The formula is written so that if G4 has a date in it, I4 will calculate the current date minus G4, divide it by 7 to determine the weeks, and then multiply it by the percentage points that students are expected to complete each week (in this case, an additional 2.78 percent each week). This figure is different for different courses, such as Pre-Algebra (on row 9), since Pre-Algebra is only a half-year course. I'm sure this is all overly complicated, but this is the best I could do at my skill level for now, and it works except for this final problem.
The problem I'm running into is that if I4 calculates beyond 100%, it will continue counting up (as in cell I9 on the example worksheet). Since a student can only ever be expected to complete a maximum of 100% of a course, I need to cap it at 100% to avoid confusion for the parent and student.
I have successfully set the minimum value to 0 so that I4 never shows a negative number when there's a start date set in the future, but I'm having trouble adding the maximum value.
Using I4 as an example, I've tried to embed some solutions into this formula, such as
=max(min(H4,100),0)
and
=MAX(MIN(original formula; max value); min value)
These have not worked for me. When I try to cap the maximum value to 100 so that I4 never goes above 100, it always returns "100".
Please help if you can! Thank you!
I've pasted a formula in your column O for testing. lemme know how it goes.
=if(G4="",,let(a,((((TODAY()-G4)/7)*2.78)/100),ifs(a>1,1,a<0,0,TRUE,a)))