# Adding Multiple AND Functions in a COLLECT Formula

✭✭✭✭

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?

• ✭✭✭✭✭✭

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.

• ✭✭✭✭✭✭