Excel – Categorize the data into 3 groups

microsoft excel

I want to use a formula which allows me to categorize my data into 3 groups.
I want:

  • Units shipped <100 to be called Group "A"

  • Units shipped Between 100-999 to be group "B"

  • Units Shipped >1000 to be group "C"

Similar layout of data

I have tried:

IF(Sales<100,"A", IF(AND(Sales>=100, Sales<=999, "B", IF(Sales>1000, "C",0)))

But it keeps coming up with the:

we found a problem with this formula

error message and I can't work out what is wrong with the formula.

Best Answer

I notice you say you have worked it out, however there are a couple of things worth mentioning.

  1. There is a small error in your formulae (and your problem description). You want values grouped into: 'Less than 100', 'Between 100 and 999 inclusive', 'Over 1000'. What happens if the value is exactly 1000?

  2. The way you have written the formula will work, but it is worth knowing that multiple IF statements are the equivalent of 'IF-ELSE' in other languages. So, you could simplify the formula by only dealing with the upper bounds of your conditions:

    =IF(Sales<100, "A", IF(Sales <1000, "B", "C"))

is the shortest and easiest to read (imho) formula that you are looking for.

Related Question