Max Function

eganji

I have a formula that finds and returns the maximum of a set of date cells meaning that it finds the latest date. It works fine if all cells are populated with dates but if one cell is blank it error out. This is the formula:

=MAX([Due Date]1, [Family Member A  Due Date]1, [Family Member B  Due Date]1, [Family Member C  Due Date]1, [Family Member D Due Date]1, [Family Member E Due Date]1)

How can I fix it to still return the MAX value if any of the cells for Members A, B, C, D, or E are blank?

Thank you

Paul Newcome

Are all of those columns adjacent? If so, you could use a MAX(COLLECT to specify to only pull dates and ignore blanks.

eganji

Hi Paul,

Thank you for responding. They are not adjacent but is there a way I can use the column title to consider dates and drop the blanks?

Thank you

Paul Newcome

Are there any other date columns between the leftmost and rightmost that you would NOT want included? We could include criteria in the COLLECT function to only look at dates. That way even if there are text fields in the middle of the range, it will skip over those. 

 

So if you had some thing like this...

 

Date1  Text1   Date2   Symbol1    Text2    Date3

 

Would would say something like

 

=MAX(COLLECT([Date1]@row:[Date3]@row, [Date1]@row:[Date3]@row, ISDATE(@cell))

.

This only works though if you want to include all 3 date columns. If you just wanted the first and third, we would need to come up with a different solution.

 

PS

The ISDATE function will also exclude blank date fields along with text, number, and symbol fields.

eganji

Hi Paul,

There are almost nothing but dates. I am attaching a small screenshot. I thought maybe I could eliminate the cause of blank cells. Cells are calculated through a formula that only has two elements and if wither one of them is missing the returned value will be blank. So I thought I would check before the formula is calculated and if either one of the elements are blank I would assign an obviously incorrect date. So I posted a question a couple hours ago that has to do with this issue too. This is what I thought:

How can I combine the following two formulas in one statement:

1)    IF([Patient Date of Birth]1 OR [Last Patient Visit Date]1) = BLANK OR NULL)

    THEN  ([Patient Date of Birth]1 AND [Last Patient Visit Date]1) = "1/1/1940"

2)    IF(NETDAYS([Patient Date of Birth]1, [Last Patient Visit Date]1) / 360 > 12, DATE(YEAR([Last Patient Visit Date]1) + 7, MONTH([Last Patient Visit Date]1), DAY([Last Patient Visit Date]1)), DATE(YEAR([Patient Date of Birth]1) + 19, MONTH([Patient Date of Birth]1), DAY([Patient Date of Birth]1))).

what I am trying to do is if that if first statement is true and happens, then replace the values in the second statement with "1/1/1940" and continue to the calculation is statement 2.

Thank you

Paul Newcome

So exactly which date columns are you trying to pull from for your MAX statement?

eganji

I am trying to pull MAX for the following columns:

Destruction Due Date

Family Member A Destruction Due Date

Family Member B Destruction Due Date

Family Member C Destruction Due Date

Family Member D Destruction Due Date

Family Member E Destruction Due Date

But if a cell in any of these columns is blank the Max formula will generate a #Invalid Data Type error.

Paul Newcome

Ok. So here is what we would need to do...

 

1. Move all of the referenced date columns so that they are adjacent (doesn't seem like an option because of layout).

.

2. Create 6 additional "Helper Columns". We could call them A, B, C, D, E, and Destroy (just for this example - you can call them whatever you want if you go this direction). You would put these helper columns adjacent to each other and then enter a very basic formula to pull the date from the corresponding Family Member column.

 

To pull the MAX function using these column names while skipping over blank fields, you would enter

 

=MAX(COLLECT(A@row:Destroy@row, A@row:Destroy@row, ISDATE(@cell)))

.

3. Create a single "Helper Row" (I am going to use row 1 for this example - you can use whatever row you want). In this row you would put some kind of indicator (for this example I'll use ') in this row in each of the date columns you want to pull from.

 

We would then use the same formula as in #2 but we would tack on the criteria of only being in columns that contain the '.

 

=MAX(COLLECT(A@row:Destroy@row, A@row:Destroy@row, ISDATE(@cell), A$1:Destroy$1, "'"))