Using MAX Function Across Columns
I have a record of course development projects (masters) and the term and status of its development over time. We may develop the course in one term, then three terms later, revise it. In Smartsheet, this looks like this:
BUS 101 | 202160 | On Track | 000000 | No | 000000 | No | 202030 | Completed --> 202030 | On Track CAR 214 | 000000 | No | 000000 | No | 202010 | Completed --> 202010 | Commpleted ECO 422 | 000000 | No | 202110 | Completed | 202010 | Completed --> 202110 | Commpleted HSM 390 | 000000 | No | 000000 | No | 000000 | No --> -- | No Master
Above, there are four courses listed (BUS 101, CAR 214, ECO 422, and HSM 390). I separated the columns with a | symbol.
Looking at BUS 101,
- Column A is "BUS 101"
- Columns B through I indicate a development term and status (two columns for different fiscal years)
- Column B is "202160", which is a term code for summer 2021
- Column C is "On Track" which is the development status of the project right now
- Column D is "000000", which also would represent a term code, but since there was no work done on this course master in that fiscal year, the term code is set to 000000.
- Column E is "No", since the term code was "000000", there was no work done on the master, so the development status is just set to "No"
- Columns F and G are also "000000" and "No" since no work was done in that fiscal year.
- Column H is "202030" since the course master was originally developed in spring 2020
- Column I is "Completed" since the course master developed in 202030 is now marked Completed.
At the end of each row in the code block above I have "--->" followed by two columns that include the a term code and current development status of the course master. For BUS 101, it is "202030" and "On Track", since it was last completed in 202030, but since the master is in development, the status is "On Track".
Again, I put the "--->" just to point out the two columns with the formulas that would determine the MAX term code with a development status of "Completed" across the columns (pairs of columns for different fiscal years).
What I want those two far-right columns to show is the LAST term the course master was developed AND the development status is marked "Completed". I can use a MAX function for all the columns, and I get "202160" for BUS 101; however, that project is still "On Track". I instead want it to show the MAX term code in which the development status is also "Completed". If the MAX term code is "000000" it display " " for the term code and "No Master" for the development status.
If the MAX term code isn't also the MAX term code with a "Completed" development status, I display a blue symbol to indicate the course master is currently in development. In my examples, BUS 101 is the only one where this is the case and would be the only one with the blue symbol.
CAR 214 is easy---it was last developed (with a status of "Completed") in 202010.
ECO 214 is also easy, since both term codes (202110 and 202010) have "Completed" as the development status, so the MAX is 202110.
The MAX for HSM 390 is "000000", the course master was never developed, so it shows "--" and "No Master"
It would be easy if I could just use the MAX function across the columns; however, I only want the MAX term code (the first of the pair of columns for a fiscal year) if the dev status of the project (the second of the pair of columns for a fiscal year) with that term code is also "Completed".
Any ideas how to write the formulas in the two columns after the "--->"?
- Again, the first formula column would show the MAX term code with a development status of "Completed" or show "--" if the master was never developed.
- The second formula column would show the current development status (e.g., "Completed", "On Track", "In Review", etc. depending on the MAX term code or "No Master" if the MAX term code is 000000)
Note: I will have to add a new pair of columns each time a new fiscal year begins.
Help Article Resources
Check out the Formula Handbook template!