How to Incorporate OR with a COLLECT Formula
I have the following working formula that I need to modify to look for either two people in the "Resi  Onboarding Specialist" column in addition to meeting the date range. The formula below is working for one application but the next application needs to also include calculating the median if the "Resi  Onboarding Specialist" column contains either Bob Smith or Susan Jones
=MEDIAN(COLLECT({Resi  Pricing & Tools Duration}, {Resi  Enter Stage 7 Date}, AND(@cell >= $[Start Date]$1, @cell <= $[End Date]$1), {Resi  Onboarding Specialist}, @cell = "Bob Smith"))
I was thinking that there needs to be an OR statement but I just don't know where to add it. Several attempts only give me an #INVALID DATA TYPE error.
Best Answers

If the rest of the formula works, then to add the Or should just be this:
{Resi  Onboarding Specialist}, OR(@cell = "Bob Smith", @cell = "Susan Jones")

That shouldn't matter that they aren't found in the column. I would double check your formula. The end would follow the same pattern as above.
{Resi  Onboarding Specialist}, OR(@cell = "Bob Smith", @cell = "Susan Jones", @cell = "Cathy Morgan")

Thank you once again. It took me a while but I left off the quotes after "Morgan". I appreciate you keeping me sane on this one.
Answers

If the rest of the formula works, then to add the Or should just be this:
{Resi  Onboarding Specialist}, OR(@cell = "Bob Smith", @cell = "Susan Jones")

Thank you. I thought I added the "OR" in every place possible but obviously not. Your addition worked perfectly. Now, I am trying to add a 3rd person (Cathy Morgan) to the string but, their name is not currently found in the {Resi  Onboarding Specialist} range and am getting an #UNPARSEABLE error message instead of the median value for the combination of Bob Smith and Susan Jones (excluding Cathy Morgan).
I was expecting that the formula would give me the median for Bob and Susan and not include Cathy since she was not listed in the range. Is the #UNPARSEABLE error what I should expect if there are no entries for Cathy Morgan? How would I modify it to get the median for anyone and everyone who has entries in the column?

That shouldn't matter that they aren't found in the column. I would double check your formula. The end would follow the same pattern as above.
{Resi  Onboarding Specialist}, OR(@cell = "Bob Smith", @cell = "Susan Jones", @cell = "Cathy Morgan")

Thank you once again. It took me a while but I left off the quotes after "Morgan". I appreciate you keeping me sane on this one.
Help Article Resources
Categories
Check out the Formula Handbook template!