Formula: Assign to Tiers or Cohorts

Combine ARRAYFORMULA, FILTER and a reference table for a cleaner way to assign tiers or cohorts.

Is this hospital "small", "big", "big-ish", "very_big" or "FREAKING YUGE"?

Let's say you are working with a list of hospitals located in Pennsylvania, and you want to assign them to two groups "big" and "small". "Big" hospitals have 1,000 beds or more, and "Small" hospitals have fewer than 1,000 beds. You create a new column called "Groups" and you use a simple formula to assign each hospital to one of the two groups. IF ( # >= 1000, "Big" , "Small"). Done and done. Easy peasy, lemon squeezy.

But, what if there aren't 2 tiers, but 12? Create an If statement with 12 clauses? NOPE.

Instead, create a "Tiers" reference table and then use this formula:

ARRAYFORMULA ( FILTER ( returnRange , value >= min_refRange , value < max_refRange ) )

Check out the example below. The "Tiers" reference table can be found in tab called "Ref - Tiers". And the above formula can be seen in action in column D of tab "Output".

EXAMPLE: HOSPITALS TIERED BY SIZE

Helpful? Not Helpful? Don't care.