Create a group column based on existing values

Let’s say you have a dataset containing information about a car fleet and you need to create a column with usage group based on number of KM each car was used.

I’ll use this file, sheet [2 Removing duplicates] in the following examples.

Step1: Calculate the sum per car

A simple sumif will give you the full distance.

 
 
  1. =SUMIF(A:A,A2,D:D)

Step2: Create groups

A. Using IF

Let’s say you need the following groups:
<=200 KM
201-300 KM
301-400 KM
>400 KM

 
 
  1. =IF(F2<=200,"<=200 KM",IF(AND(F2>=201,F2<=300),"201-300 KM",IF(AND(F2>=301,F2<=400),"301-400 KM",IF(F2>400,">400 KM",0))))

B. Using VLOOKUP

Using IF might give you headaches so you could use VLOOKUP instead.

You need to create a lookup table like this:

0 <=200 KM
200 201-300 KM
300 301-400 KM
400 >400 KM

Now, assuming you have the sum of KM in column F and the lookup table in columns I:J, you can use this formula:

 
 
  1. =VLOOKUP(F2,I:J,2)

Note that range_lookup argument is missing so it will be an approximate search, VLOOKUP returning the next lowest match to your search term.

Add a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.