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?
