Formula that will display the lowest value from two data sets (with the highest value).

EmilyH · 07/13/21

Hello Smartsheet community, I need some help fixing a formula.

The goal = Display the lowest value between (the highest value between 1A and 1B) and (the highest value between 2A and 2B).

The formula I created is:

=MIN(COLLECT(MAX(COLLECT([DATE 1A]@row, [DATE 1A]@row, @cell <> ""), COLLECT([DATE 1B]@row, [DATE 1B]@row, @cell <> "")), MAX(COLLECT([DATE 2A]@row, [DATE 2A]@row, @cell <> ""), COLLECT([DATE 2B]@row, [DATE 2B]@row, @cell <> "")), @cell <> ""))

Note: It works for scenarios A, C and E. However, for scenario B the date should be 05/15/20. For scenario D the date should be 02/20/23.

Thank you so much for your help.

Emily

NOTE: All date columns have a Date format.

Andrew Dehyd · 07/13/21

Hi @EmilyH,

At first I tried

=MIN(MAX([Date 1A]@row, [Date 1B]@row), MAX([Date 2A]@row, [Date 2B]@row))

and that almost worked... but then I had "#INVALID DATA TYPE" when 1A and 1B were both empty (or 2A and 2B were both empty).

Here's another way that might work, although it's a bit messy - it checks if the 1A:1B pair is empty, and also 2A:2B pair. Someone else might be able to come up with something much neater?

=IF(ISDATE(MAX([Date 1A]@row, [Date 1B]@row)), IF(ISDATE(MAX([Date 2A]@row, [Date 2B]@row)), MIN(MAX([Date 1A]@row, [Date 1B]@row), MAX([Date 2A]@row, [Date 2B]@row)), MAX([Date 1A]@row, [Date 1B]@row)), MAX([Date 2A]@row, [Date 2B]@row))

That's not easy to read: it might work but doesn't seem like it should need a formula that complicated. I'd be interested in finding out the correct way of doing this too!!

Toufong Vang · 07/13/21

Try...

=MIN( IFERROR(DATEONLY(MAX([DATE 1A]@row, [DATE 1B]@row)),""), IFERROR(DATEONLY(MAX([DATE 2A]@row, [DATE 2B]@row)),""))

MIN( IFERROR(DATEONLY(MAX([DATE 1A]@row, [DATE 1B]@row)),""),
      IFERROR(DATEONLY(MAX([DATE 2A]@row, [DATE 2B]@row)),""))

This is a very interesting case.

I don't understand why your formula does not error outright since your COLLECT() statements do not follow the documented syntax for the function. Nevertheless, the parts essential to your approach/formula DO work.

MAX_of_Date1_pair = MAX(COLLECT([DATE 1A]@row, [DATE 1A]@row, @cell <> ""), COLLECT([DATE 1B]@row, [DATE 1B]@row, @cell <> ""))
MAX_of_Date2_pair = MAX(COLLECT([DATE 2A]@row, [DATE 2A]@row, @cell <> ""), COLLECT([DATE 2B]@row, [DATE 2B]@row, @cell <> ""))

Since, they work separately, it's reasonable to assume that you can use those two statements in a MIN() function. For example, MIN( MAX_of_Date1_pair, MAX_of_Date2_pair ). And it works except for when one pair is empty. When either (1A,1B) or (2A,2B) is empty, trying to get the MIN() from among the two statements results in #INVALID DATA TYPE.

Enclosing each of the two statements within IFERROR() does not work either. So this tells us that they're not erroring. There is only an issue as we're trying to get the minimum from among the two when one of the MAX() statements evaluates to empty.

Only when your statements are enclosed in IFERROR( DATEONLY( xx ) ) does using the MIN() function work.

MIN( IFERROR(DATEONLY(MAX(COLLECT([DATE 1A]@row, [DATE 1A]@row, @cell <> ""), COLLECT([DATE 1B]@row, [DATE 1B]@row, @cell <> ""))),""), 
     IFERROR(DATEONLY(MAX(COLLECT([DATE 2A]@row, [DATE 2A]@row, @cell <> ""), COLLECT([DATE 2B]@row, [DATE 2B]@row, @cell <> ""))),"")

I can only assume that it has something to do with the order of operations when the functions are combined in that manner.

Andrew Dehyd · 07/13/21