Max of Children Formula

raymond.j.riosiii94806

Hi Formula Friends -

I'm trying to calculate the "next test date" based whether a cell is "Surveillance (Monthly)" or "Baseline (Initial)" or "Response (Weekly)".

In the formula, it must take into account the following:

If Test Strategy in Parent Row = Surveillance (Monthly) or Baseline (Initial), then the Next Test Date should be = Max(Children([Test Date]8 + 30 days)

If Test Strategy in Parent Row = Response (Weekly), then the Next Test Date should be = MAX(CHILDREN([Test Date]8 + 7 days)

The formula I have is currently: =IF(OR([Test Strategy]8 = "Surveillance (Monthly)", [Test Strategy]8 = "Baseline (Initial)", MAX(CHILDREN([Test Date]8) + 30), MAX(CHILDREN([Test Date]8 + 7))

How can I fix this?

Paul Newcome

There are parenthesis issues throughout. Try this...

=IF(OR([Test Strategy]8 = "Surveillance (Monthly)", [Test Strategy]8 = "Baseline (Initial)"), MAX(CHILDREN([Test Date]8)) + 30, MAX(CHILDREN([Test Date]8)) + 7)

You forgot to close off your OR function, and then you needed to adjust the parenthesis so that you are adding the number of days to the result of the MAX function.

MAX(CHILDREN([Test Date]8)) + 30

John Jonassen

It looks like you need to fix some indenting so you have Parents and Children.

raymond.j.riosiii94806

Hey John, I actually already have the Parent and Children rows set up. The blue line is the parent and the white are the children. I just cut them off in the screenshot because they contain patient/protected information.

raymond.j.riosiii94806

I've also tried this: =IF(OR([Test Strategy]8 = "Surveillance", [Test Strategy]8 = "Baseline"), MAX(CHILDREN([Test Date]8+30), MAX(CHILDREN([Test Date]8+7)

But it's not working... resulting in #UNPARSABLE.

raymond.j.riosiii94806

Also tried, unsuccessfully, this formula: =IF(OR([Test Strategy]8 = "Surveillance", [Test Strategy]8 = "Baseline"), MAX(CHILDREN([Test Date]8) + 30, MAX(CHILDREN([Test Date]8) + 7)))

Paul Newcome

There are parenthesis issues throughout. Try this...

=IF(OR([Test Strategy]8 = "Surveillance (Monthly)", [Test Strategy]8 = "Baseline (Initial)"), MAX(CHILDREN([Test Date]8)) + 30, MAX(CHILDREN([Test Date]8)) + 7)

You forgot to close off your OR function, and then you needed to adjust the parenthesis so that you are adding the number of days to the result of the MAX function.

MAX(CHILDREN([Test Date]8)) + 30