This is part 2 of a series of tutorials to show the power of Microsoft Excel. Here we will see some functions that were used in the first tutorial. The main focus of this walk through is to work with some new functions and to learn about how arrays can make life easier when working on large project.
- LARGE – Returns the k-th largest value in a data set. You can use this function to select a value based on its relative standing. For example, you can use LARGE to return the highest, runner-up, or third-place score – Read more here
- SUM – Adds all the numbers in a range of cells – Read more here
- INDEX – Returns a value or the reference to a value from within a table or range. There are two forms of the INDEX function: the array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.) form and the reference form – Read more here
- MATCH – Returns the relative position of an item in an array (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.) that matches a specified value in a specified order. Use MATCH instead of one of the LOOKUP functions when you need the position of an item in a range instead of the item itself – Read more here
- COUNTIF – Counts the number of cells within a range that meet the given criteria – Read more here
This tutorial was written for Excel 2007, but will work in most versions of Excel. Find more information about Microsoft Excel functions at the Microsoft Office website.
Here we begin with some test data on the Rail List tab. Download the test data here. This sheet looks similar to the blanks sheet. The rail list has the width and length of the rails in inches, along with wood specie and a column for a profile type.
Like with the blanks sheet, we want to display the size in millimeters. We can copy the equation directly from the blanks tab and paste into the rail sheet.
We will do the same thing with the part number, copy from the blanks sheet and paste onto the rails tab. Replace the "B" in the equation with the "Profile Type". Notice that the width column is column "D" for the blanks, and column "E" on the rail list. This means the equation will have to be modified slightly. For cell A2, C2 would need to be changed to D2, D2 to E2, and E2 to F2.
Now add a new named range, name it "Profile". This will be where we define the description for each profile type. We will start with 3 profiles.
Last we will copy the equation for the description. Similar to the part number, we will have to change the cells that are referenced and add a VLOOKUP to get the Profile Description. We can go back to the Creating A Parts Lookup Spreadsheet – Part 1 Setting Up Blanks tutorial to find out more on how to use VLOOKUP with a named range.
Now comes the meat of this project. On the "Ref" tab we will add an Oversize Width and Oversize Length, name these fields "oversize_width" and "oversize_length". When using blanks to make the rails, the blanks have to be larger than the rails by a specific amount. In this case the blank width must be larger than the rail by at least 1/8". The blank must be a minimum of 1/4" longer than the rail.
On the "Blanks" tab we will give the A1:A100 range the name "blank_partnumber", C1:C100 named "blank_material", D1:D100 and E1:E100 named to "blank_width" and "blank_length". We will use these ranges for the array look up. Make sure the range of cells selected is the same.
Back on the "Rail List" tab we start the LARGE function, using SUM to help us find the closest matching width of blank. The goal is to look at the rail width, and find the smallest blank width that can be used to make this rail. The equation in cell I2, the blank width look up, will look like:
Be sure to press Ctrl + Shift + Enter after editing to leave the cell, this must be done when working with arrays. This equation will find the smallest width part that is greater than or equal to the rail width + .125. The first part of the large function will filter the array so only matching material is looked at.
The second part of the LARGE function is another filter. It will figure how many items in the list matches the criteria.
The equation in cell J2, the blank length look up, will look like:
This equation will find the smallest length part that is greater than or equal to the rail length + .25. The first part of the large function will filter the array so only matching material and width is looked at. This is done using two IF functions. =IF(blank_material=D2,IF(blank_width=I2,blank_length,""),"") The second part of the LARGE function will figure the total number of items in the list that matches the criteria.
What is nice about this function is that it doesn't matter how the blank list is sorted, the LARGE function "resorts" the data from largest number to smallest number. By using SUM to find the quantity of blanks that meet the minimum requirements, it will pick the lowest item on the list, or the smallest number that meets the requirements.
Now we will use INDEX to find the blank part number starting in cell K2.
=INDEX( blank_partnumber , MATCH( D2 & I2 & J2 , blank_material & blank_width & blank_length , 0 ) )
The first part of the INDEX function is the range of blank part numbers. The second part of the function we will use the MATCH function. To learn more about the INDEX/MATCH combination, view the How To Lookup Data Using An Array In Excel tutorial.
Just to check our work and make sure we don't have a lot of waste, lets add a column to subtract the rail length from the blank. Looking down the list we can see there are some gaps in the blanks previously created. From here we can fill in those gaps on the blanks sheet.
Before filling those gaps, it would be nice to know how many times a chop is actually used. COUNTIF works great in this instance. On the "Blanks" sheet:
The first part of the COUNTIF function will be the column of blank part numbers on the "Rail List" sheet. Second, the part number from the blanks sheet is selected. This will give us the count of how many times the part number from the selected cell in column A of the blanks sheet appears on column K of the "Rail List" sheet.