Automatic classification based on a table of relationships or typology

Last time, I talked about using VLOOKUP to automatically assign the grade equivalent of a raw score. This time, we will use VLOOKUP to classify non-numerical items.

Let's say that you have a table as follows:

Power Plant	Capacity (In MW)	Energy Resource	Fossil or Renewable?
APEC	50	Coal
Bacman	130	Geothermal
Bajada DPP	58.7	Oil Based
Bakun	70	Hydroelectric
Bangui Wind Power	33	Wind
Botocan	22.8	Hydroelectric
Calaca	600	Coal
Calibu DPP	30	Oil Based
Caliraya	35	Hydroelectric
Casa Bioenergy	15	Biomass
Casecnan (NIA)	165	Hydroelectric
CEDC Coal	246	Coal
CEMEX	66	Oil Based
Crystal Sugar	35.9	Biomass
DESCO Natural Gas	1	Natural Gas
First Farmers	21	Biomass

So you want the spreadsheet to enter in the fourth column if the power plant is renewable or fossil fueled. Assuming you know that  fossil means power plant using coal, oil-based or natural gas, and renewable uses everything else, you can create the following reference table (again, I recommend that you use a separate sheet within the same file):

Biomass	Renewable
Coal	Fossil
Geothermal	Renewable
Hydropower	Renewable
Natural Gas	Fossil
Oil	Fossil
Solar	Renewable
Wind	Renewable

You want to tell the program to do the following in your list of power plants:

1. Search for the type of energy resource in the list of power plants in the reference table, then
2. Enter the corresponding type of energy resource.

To do that, you type the following in the cell in the "Fossil or Renewable?" column:

=VLOOKUP(Cell_in_EnergyResourceRow, Range_of_ReferenceTable, Column_Number_of_Types_in_ReferenceTable)

Just do this in one column, change the references in the reference table to absolute, then copy the formula to the other cells in the "Fossil or Renewable?" column.

For reference, you can see this file.

Note: Your reference table may have multiple columns, because something may be classified in more than one way. For example, the power plant may be classified also based on if the energy resource if clean (all renewable energy plus natural gas) and traditional. To do this, just add the third column in the reference table, use VLOOKUP, and in the list, indicate the third column as the column number to be used.

Automatically assign grades based on raw scores

Teachers frequently use Excel to record performance of students. Using formulas, teachers calculate ratio (or percent) of each output to the overall grade, usually within the range of 0 to 100. These raw scores are usually converted into particular grading systems, such as into A to E or 1.00 to 4.00.

There is a function in most spreadsheet programs (e.g., Microsoft Excel, LibreOffice Calc, OpenOffice.Org Calc, or Google Sheets) that will automatically search the raw score's equivalent grade, meaning convert a 65 to, say, "E." This is VLOOKUP.

To use VLOOKUP, you must first create a table which the spreadsheet program will use as reference. VLOOKUP looks up a table vertically for values based on an item you told the computer to look for, and returns a corresponding value, based on the reference table. The value you tell the computer maybe an actual value (i.e., you enter the value manually within the cell) or a reference cell.

For example, you have a table of students with three columns. The first column is Name, the second column is raw grade (in number), and the third column is where you intend to enter the grade equivalent (e.g., A to E).

You enter the student records (Student Name and Raw Grade). Here's how it should look like:

Name	Raw Score	Grade
Student A	61
Student B	75
Student C	92
Student D	68
Student E	91

Then you create the reference table (preferably in another sheet, so that you can re-arrange your student record sheet without affecting the reference table). Your reference table should have three columns: 1) Minimum Grade; 2) Maximum Grade; and 3) Grade Equivalent.

You should arrange the grade ranges from lowest to highest going down. Here's an example:

Grade Minimum	Grade Maximum	Grade Equivalent
0	60	E
61	70	D
71	80	C
81	90	B
91	100	A

After you do this, you return to your student record sheet. In the third column (you do this in one cell only, then just copy the formula to the other rows), you use the VLOOKUP function to tell the computer to do the following (the formula will follow, don't worry):

1. Find the cell in the column Raw Grade in the reference table (which, again, is recommended placed in another sheet)
2. When found, return the Grade Equivalent in this cell.

To do this, you use this formula:

=VLOOKUP(Put_here_the_cellreference_of_the_raw_grade, Reference_table, Column_Number_of_the_Grade_Equivalent)

In most spreadsheet programs, you don't need to type the whole function manually. You can start with typing the function, then use the mouse to identify the references, particularly the reference table which is in another sheet.

Check that the formula you enter returns the correct grade equivalent. Once you verify that it is correct, you have to ensure that the reference for the reference table is absolute, meaning you use the $ sign for the reference key.

=vlookup(B2,'Grade Conversion Key'!A$2:C$6,3)

(The 'Grade Conversion Key' is the name of the second sheet. When you enter the variables of the function, you can use your mouse to point to the location of the cell or range you want.)

After you make sure the reference for the reference table is already absolute, you can now copy the formula to the rest of the rows in the Grade column, as follows:

Name	Raw Score	Grade
Student A	61	=vlookup(B2,'Grade Conversion Key'!A$2:C$6,3)
Student B	75	=vlookup(B3,'Grade Conversion Key'!A$2:C$6,3)
Student C	92	=vlookup(B4,'Grade Conversion Key'!A$2:C$6,3)
Student D	68	=vlookup(B5,'Grade Conversion Key'!A$2:C$6,3)
Student E	91	=vlookup(B6,'Grade Conversion Key'!A$2:C$6,3)

Notice here that while the reference for Raw Score cell changes, the reference for the reference table doesn't. Here's how it should look like:

Name	Raw Score	Grade
Student A	61	D
Student B	75	C
Student C	92	A
Student D	68	D
Student E	91	A

The formula works for Microsoft Excel, LibreOffice Calc, OpenOffice.Org Calc, and Google Sheets.

To see how it work in Google Sheets, click here.

ADDENDUM: If your Raw Score has more decimal places than those in your reference table (e.g., your reference table values have up to 2 decimal places but your raw scores in your cell references have 3 or 4 decimal places), you need to round off the values according to the number of decimal places in your reference table before using the VLOOKUP process. To do so, you need to use the ROUND function of your spreadsheet application. The formula would be like this, if you need to round off to 2 decimal places:

=vlookup(ROUND(B2,2),'Grade Conversion Key'!A$2:C$6,3)