# Adding Checkbox Not Checked to Working Formula

Options
✭✭✭✭

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?

Tags:

• ✭✭✭✭✭✭
Options

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))))

If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

• ✭✭✭✭
Options

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.

• ✭✭✭✭✭✭
Options

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

If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

• ✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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.

If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!