Adding Multiple AND Functions in a COLLECT Formula

GMichal

I have an existing working formula that will calculate the median of a column of numbers which are within a certain date range (date range is an input from a dashboard). I would now like to add another criteria to that existing formula to allow some data to be excluded from the median calculation (is also an input from a dashboard) if there is a check in another column. Here is some sample data from a source sheet:

Here is a look at the metric sheet where I am running my median calculation in the shaded cell:

Here is the existing working formula to calculate the median of the 'Resi - Financial Approval to BR' if the date in the 'Resi - Enter Stage 3 Date' falls between the date range of 07/18/24 to 07/31/24:

=MEDIAN(COLLECT({Sample Sheet #1 - Resi - Fin Apprvl to BR}, {Sample Sheet #1 - Resi - Enter Stage 3 Date}, AND(@cell >= $[Start Date]$1, @cell <= $[End Date]$1)))

Now I want to be able exclude some of the 'Resi - Financial Approval to BR' data from the median calculation if there is a check in the 'Resi - HOLD' column in the source sheet, which would exclude the values of 0 and 3 from the calculation making the new median '5'. I tried to add to the existing AND function of the working formula like this:

=MEDIAN(COLLECT({Sample Sheet #1 - Resi - Fin Apprvl to BR}, {Sample Sheet #1 - Resi - Enter Stage 3 Date}, AND(@cell >= $[Start Date]$1, @cell <= $[End Date]$1, {Sample Sheet #1 - Resi - HOLD}, [Exclude HOLD Data Value]1)))

but get an #INVALID DATA TYPE error. What am I doing wrong?

Adam Murphy

Don't add to the AND function as that is the criteria for the previous range (Sample Sheet #1 - Resi - Enter Stage 3 Date), you want to add the new RANGE and CRITERIA to the COLLECT function instead. Try this: =MEDIAN(COLLECT({Sample Sheet #1 - Resi - Fin Apprvl to BR}, {Sample Sheet #1 - Resi - Enter Stage 3 Date}, AND(@cell >= $[Start Date]$1, @cell <= $[End Date]$1), {Sample Sheet #1 - Resi - HOLD}, [Exclude HOLD Data Value]1)) I think that should work.

Adam Murphy

Don't add to the AND function as that is the criteria for the previous range (Sample Sheet #1 - Resi - Enter Stage 3 Date), you want to add the new RANGE and CRITERIA to the COLLECT function instead. Try this: =MEDIAN(COLLECT({Sample Sheet #1 - Resi - Fin Apprvl to BR}, {Sample Sheet #1 - Resi - Enter Stage 3 Date}, AND(@cell >= $[Start Date]$1, @cell <= $[End Date]$1), {Sample Sheet #1 - Resi - HOLD}, [Exclude HOLD Data Value]1)) I think that should work.

GMichal

Yes, that was it! Thank you. Staring at the same formula over and over again, it never dawned on me that I didn't want it as a part of the AND function but it would be included as a criteria outside of the closed parenthesis.