Thursday, October 16, 2014

Classify numbers in a Range


Suppose we have data in excel. It has ticket ids in column A, column B is the status of ticket, column C is the Request date of the ticket, column D is the Latest update, column E is the Ticket age (days) and we need to classify Ticket age into different ranges in column F (Ticket age - Range) as follows: ">300 days", ">200 days", ">100 days", "100 days or less"

We can achieve this using If condition in cell F2:

=IF(E2>300,">300 days",IF(E2>200,">200 days",IF(E2>100,">100 days",IF(E2<=100,"100 days or less","")))) 

You then simply have to drag the formula down to the very last row having data to get the Ticket age - Range for all the rows with data.

Check out the image below which displays the final result: