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)
1. Column B is "202160", which is a term code for summer 2021
2. Column C is "On Track" which is the development status of the project right now
3. 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.
4. 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"
5. Columns F and G are also "000000" and "No" since no work was done in that fiscal year.
6. Column H is "202030" since the course master was originally developed in spring 2020
7. 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.

Thanks!!!

Find more posts tagged with

Accepted answers

All comments

Genevieve P.

Hi @Art Schneiderheinze

There isn't one quick-and-easy way to do this since all of your data is in one row; the formula won't be able to identify each column's pairing and relation to each other without us specifically writing in this relation.

For example, you could do a massive Nested IF statement to check each of your Status columns. Then if the Status is Complete, you'd return the value in the column to the left of it. You could put each of these IF statements inside a MAX function so it only returns the MAX value, like so:

=MAX(IF([Status - 1]@row = "Completed", [Term Code - 1]@row, ""), IF([Status - 2]@row = "Completed", [Term Code - 2]@row, ""), IF([Status - 3]@row = "Completed", [Term Code - 3]@row, ""), IF([Status - 4]@row = "Completed", [Term Code - 4]@row, ""))

Screen Shot 2021-07-30 at 4.15.58 PM.png

Keep in mind every time you have a new column added in the sheet you'll need to add this column pairing in your formula.

Then you could add in a greater IF statement to say that IF the max = 0, return --, otherwise calculate the formula:

=IF(MAX(IF([Status - 1]@row = "Completed", [Term Code - 1]@row, ""), IF([Status - 2]@row = "Completed", [Term Code - 2]@row, ""), IF([Status - 3]@row = "Completed", [Term Code - 3]@row, ""), IF([Status - 4]@row = "Completed", [Term Code - 4]@row, "")) = 0, "--", MAX(IF([Status - 1]@row = "Completed", [Term Code - 1]@row, ""), IF([Status - 2]@row = "Completed", [Term Code - 2]@row, ""), IF([Status - 3]@row = "Completed", [Term Code - 3]@row, ""), IF([Status - 4]@row = "Completed", [Term Code - 4]@row, "")))

The easier way to do this would require a re-structure of your sheet. You would have only one column for all of your Term Codes and one column for all of your Statuses. I would personally put the name of the course as a Parent row:

Screen Shot 2021-07-30 at 4.30.18 PM.png

Then the formulas can be a lot more simple and dynamic as you add in new rows for each section:

=MAX(COLLECT(CHILDREN([All Term Codes]@row), CHILDREN(Status@row), "Completed"))

This is for the Parent line, looking at the MAX of the children in the CODE column, but only if the cell next to it says COMPLETED.

Then you'd wrap this in an IF statement to check and see if the MAX children are 0, return -- instead:

=IF(MAX(CHILDREN([All Term Codes]@row)) = 0, "--", MAX(COLLECT(CHILDREN([All Term Codes]@row), CHILDREN(Status@row), "Completed")))

For the second formula, finding the Status based on the actual Max code, try this:

=IFERROR(INDEX(CHILDREN(Status@row), MATCH(MAX(CHILDREN([All Term Codes]@row)), CHILDREN([All Term Codes]@row))), "No Master")

The MATCH will find the MAX code and match it to the row that also has Status, and bring back that status. If there is no max (because everything is 0), it will return "No Master".

Let me know if this all makes sense or if I can clarify anything further!

Cheers,

Genevieve

Art Schneiderheinze

You offered a great solution! A restructuring of the seat really makes a lot of sense. The challenge with that is I would have to manually add new rows anytime a redevelopment to the original course master happens. Even though I manually add the pair of columns for each new fiscal year, the determination if a redevelopment was done in that fiscal year is automated. However, with that, it makes determining the MAX more complicated (if not impossible). Definitely something to think about! Thanks for your time.

Genevieve P.

No problem at all! I can see how this is a tricky one. Good luck, and let me know if you need help with anything further.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.