How to Incorporate OR with a COLLECT Formula

GMichal

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.

Nic Larsen

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

Nic Larsen

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

GMichal

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.

Nic Larsen

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

GMichal

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?

Nic Larsen

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

GMichal

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.