I am a teacher trying to track progress of my students' grades over 3 assessments using Excel. Unfortunately, absences can cause blank values to occur within the columns. I would do (ex. A2, B2, C2 contain data and D2 being the progress made between A2 and C2) =C2-A2, which would work, but it would not work if a student was absent and did not have a value within A2 or C2. Ideally, I would like to keep the formula consistent throughout the entire progress column.
In my internet searches, I came across a formula that someone had provided which seems to work for the most part, but not entirely
=IF(P430<>"",IF(R430<>"",R430-P430,R430),IF(R448<>"",-P430,0))
This is shown in the example in the photo that is attached.
Can someone please provide insight as to how I can correctly track progress?
CodePudding user response：
Assuming that you have headers in Row 1, that data begins in Row 2, and that the only data down the columns is score data — delete everything in Col D (including the header). Then place the following formula in D1:
=ArrayFormula({"Progress";IF(MMULT(IF(A2:C>0,1,0),{1;1;1})<2,,REGEXEXTRACT(A2:A&"~"&B2:B&"~"&C2:C,"(\d )~*$")-REGEXEXTRACT(A2:A&"~"&B2:B&"~"&C2:C,"^~*(\d )"))})
This one formula will return the header (which you can change as you like within the formula itself) and all results for all rows where there are at least two entries. If there are fewer than two entries, null will be returned. You will not drag this formula down.
Understand that this is an array formula that will "own" all of Col D. So you will not be able to type anything into Col D manually without "breaking the array" (which will result in an error in cell D1 and all rows of formulaic data being blank).
As to how the formula works, MMULT (as used here) just counts valid values row-by-row. IF uses that count to determine if there are at least two values.
The two commas together is actually "comma-null" (meaning return null if the count of values is less than 2 in any row).
Otherwise, REGEXEXTRACT will act on a concatenation of all cell values with a tilde ~ separating them. The last value present will be taken from which the first value will be subtracted.
I cannot fully explain MMULT nor REGEX2 expressions in this post, but I trust the gist is clear.
CodePudding user response：
As shown in the example, columns P, Q, R are the assessments and S is the overall progress. For Column S, I would like to show the overall growth that was made between these assessments. However, the issue is that sometimes, students are not able to be in attendance for these assessments, so the cell will be blank. The formula mentioned by Spectral Instance worked when P and R only had values, but did not work when just P or just R were missing. The last two rows show where the formula did not work correctly. In those instances, I would like to then only show progress with the assessments did take. Is there a formula that will work universally no matter which of the 3 columns are missing data?
Once again, thank you all for your insight and feedback. I apologize if this is a small fix that I am maybe missing.
CodePudding user response：
Please see below.
Rows 1-4 are correct. The last 3 rows are incorrect.
Thank you
