Adding Checkbox Not Checked to Working Formula
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?
Best Answer
-
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.
Answers
-
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.
-
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.
-
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.
-
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
-
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.
-
@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?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!