Adding Checkbox Not Checked to Working Formula

GMichal

I have a working formula that I'd like to add a condition to, if possible. Here is the working formula:

=IF(ISERROR(MEDIAN(COLLECT({Resi - Pricing & Tools Duration}, {Resi - Enter Stage 7 Date}, AND(@cell >= DATE(2023, 7, 1), @cell <= DATE(2023, 7, 31))))), "", MEDIAN(COLLECT({Resi - Pricing & Tools Duration}, {Resi - Enter Stage 7 Date}, AND(@cell >= DATE(2023, 7, 1), @cell <= DATE(2023, 7, 31)))))

where I am taking the median of any values that fall within the month of July 2023 and displaying a blank cell if there is no data that matches the criteria.

I'd now like to also make sure to include only those values in the median calculation where the "Resi - HOLD" column is not checked. I thought that it would be a simple addition but have been receiving an #INVALID OPERATION error. Here is how I modified the formula to include the criteria with the "Resi - HOLD" column:

=IF(ISERROR(MEDIAN(COLLECT({Resi - Pricing & Tools Duration}, {Resi - Enter Stage 7 Date}, AND(@cell >= DATE(2023, 7, 1), @cell <= DATE(2023, 7, 31))))), "", MEDIAN(COLLECT({Resi - Pricing & Tools Duration}, {Resi - Enter Stage 7 Date}, AND(@cell >= DATE(2023, 7, 1), @cell <= DATE(2023, 7, 31), ({Resi - HOLD} = 0)))))

Thoughts on what might be missing?

Mark.poole

try removing the desk - hold from the end function and instead tacking it to the end of the collect function.

=MEDIAN(COLLECT({Resi - PRCC Ready Duration}, {Resi - Enter Stage 7 Date}, AND(@cell >= DATE(2023, 7, 1), @cell <= DATE(2023, 7, 31),{Resi - Hold},0)))

One issue I saw is that your criteria range {Resi - Enter Stage 7 Date} won’t have the check box in it. So you have to move it from the collect function.

Mark.poole

you have an un needed ( infront of your Resi-hold that i bolded. as well as an extra ) due most likely to the first issue.

=IF(ISERROR(MEDIAN(COLLECT({Resi - Pricing & Tools Duration}, {Resi - Enter Stage 7 Date}, AND(@cell >= DATE(2023, 7, 1), @cell <= DATE(2023, 7, 31))))), "", MEDIAN(COLLECT({Resi - Pricing & Tools Duration}, {Resi - Enter Stage 7 Date}, AND(@cell >= DATE(2023, 7, 1), @cell <= DATE(2023, 7, 31), ({Resi - HOLD} = 0)))))

=IF(ISERROR(MEDIAN(COLLECT({Resi - Pricing & Tools Duration}, {Resi - Enter Stage 7 Date}, AND(@cell >= DATE(2023, 7, 1), @cell <= DATE(2023, 7, 31))))), "", MEDIAN(COLLECT({Resi - Pricing & Tools Duration}, {Resi - Enter Stage 7 Date}, AND(@cell >= DATE(2023, 7, 1), @cell <= DATE(2023, 7, 31), {Resi - HOLD} = 0))))

GMichal

Removing those ( ) didn't seem to fix the error so I stripped it down, removed all of the error-handling portion, and was left with this formula. It is giving me an #INVALID OPERATION error:

=MEDIAN(COLLECT({Resi - PRCC Ready Duration}, {Resi - Enter Stage 7 Date}, AND(@cell >= DATE(2023, 7, 1), @cell <= DATE(2023, 7, 31), {Resi - HOLD} = 0)))

I figured that I could easily add the error-handling back in but need to be successful with the core formula.

Mark.poole

what format is the date that’s in well the date column of your formula?

GMichal

Date format:

The original formula works but then does not when I add the part to look at another column to verify that there is no checkmark in that column. This portion is what I was trying to add: {Resi - HOLD} = 0

Mark.poole

try removing the desk - hold from the end function and instead tacking it to the end of the collect function.

=MEDIAN(COLLECT({Resi - PRCC Ready Duration}, {Resi - Enter Stage 7 Date}, AND(@cell >= DATE(2023, 7, 1), @cell <= DATE(2023, 7, 31),{Resi - Hold},0)))

One issue I saw is that your criteria range {Resi - Enter Stage 7 Date} won’t have the check box in it. So you have to move it from the collect function.

GMichal

@Mark.poole , that was it! Thank you! The new working formula is:

=MEDIAN(COLLECT({Resi - PRCC Ready Duration}, {Resi - Enter Stage 7 Date}, AND(@cell >= DATE(2024, 2, 1), @cell <= DATE(2024, 2, 29)), {Resi - HOLD}, 0))

Continuing the development of this formula, the checkbox range {Resi - HOLD} gets checked or not from a dashboard input so that the user can calculate the median of {Resi - PRCC Ready Duration} in the selected date range including the data that has a check in the {Resi - HOLD} column (=0) or excluding the data based upon the check in the {Resi - HOLD} column (=1).

Is it possible to have this 0 or 1 value to populate the formula from the sheet as in {Resi - HOLD}, {value from sheet})) with the 'value from sheet' being a 0 or 1?