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. P2, Q2, R2 contain data and S2 being the progress made between P2 and R2) =R2-P2, which would work, but it would not work if a student was absent and did not have a value within P2 or R2. Ideally, I would like to keep the formula consistent throughout the entire progress column.
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?
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?
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.
