This tutorial is part one of a series of tutorials to show the surprising power of Microsoft Excel. We will begin this tutorial with a real life situation where I had to manage some parts for cabinet doors and face frame components.
Normally for something like this I would build it in Microsoft Access, after all, Access is database software perfectly suited to what I wanted to do. Unfortunately, I needed to build something that others in my company could use and edit, and that was Excel. I was not convinced that it was up to the challenge.
When I began, I limited myself by not adding any macros, VBA code, or pivot tables to my Excel sheet. Excel is designed to use these, but they always throw up annoying popup windows, or items do not always update correctly, and not everyone knows how to use these things. For this I wanted to use straight forward excel functions that update the workbook every time something is changed without having to run some special code.
- TEXT - Formats a number and converts it to text - Read more here
- ROUND - Rounds a number to a specified number of digits - Read more here
- ROUNDUP - Rounds a number up, away from zero - Read more here
- ROUNDDOWN - Rounds a number down, toward zero - Read more here
- CONCATENATE - Joins several text items into one text item - Read more here
- VLOOKUP - Looks in the first column of an array and moves across the row to return the value of a cell - 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.
On this first tutorial I will concentrate on some simple functions to set up chop blanks in maple, oak, and cherry. A chop blank is a rectangular block of wood approximately ¾” thick. They are called a blank because they will be made into something else later. Blanks are easier to manage when they are pre-cut to designated sizes to place into inventory and pulled later as needed. These chops will be supplied in two widths and various lengths to be molded into parts for cabinet doors or face frames. We will start with a list of sizes and woods available.
We know the sizes of the parts we need in inches, but for added complexity, some automated chop saws require the length in millimeters (mm). Let’s add a column that converts inches to millimeters so we have easy access to the dimensions in mm. It is a simple conversion by multiplying inches by 25.4.
= inches * 25.4
This gives us our metric dimension. However, measuring a part down past one decimal point in mm is unrealistic. The automated chop saw tolerance is not that small. We want to round that dimension to one decimal place.
The ROUND function does exactly what it sounds like. It will round a number to however many decimal places you specify. In this instance our number is 4.275. If we choose to round to 2 decimal places, the function would look like:
This would give us a result of 4.28.
ROUNDUP is written the same way, but the result is that the number is rounded up every time. We want to use the ROUNDUP function here because these are blanks that will be cut into something else later. It would be better for them to be oversized than undersized.
After the function is typed, we can drag the function to the right column. Then drag both cells down to the last row. This will give us the metric value for all the blanks.
Now that we know the sizes of the parts we need, we will need to create a part number. The part number can be anything, but for this example I would like to build a “Smart Part” based off the size of the blanks we want to use. First we will grab the information from each column that we will need to make each part number unique.
Starting the part with a “B” identifies the part as a blank, followed by the letter code for the material. Between the “B” and the material we add the “&” (and) symbol, which joins the text and the cell value together.
Ensure that the “B” has quotation marks around it as shown. This tells Excel that this is a static value and never changes. We will do this two more times to join the width and the length to the smart part.
As you can see this creates a string of alpha-numeric characters. This part number is not very useful with the periods in the middle of the part number.
By multiplying the width and the length by 1000, we create a number with an implied decimal place.
=(Cell * 1000)
An implied decimal place just means that we have a number, let’s say 4321. The implied decimal place is 3 numbers. So we would read that number 4321 as 4.321. This creates a part that is unique and can never be duplicated, as the dimension is part of the part number.
There is a small problem however. What if we make a blank that is cut to a 1/16”, .0625”? This part number is already lengthy; do we want to add another two characters, one to width, one to length to give it a forth implied decimal?
If we drop the fourth decimal from the part number, remembering that the chop saw being used to make these parts knows the true size of each part, we can keep the part number a little smaller. Here is an instance where ROUNDDOWN would work very well. We only want to remove the “.5”. If we use ROUND or ROUNDUP, it would change the BM162515437.5 to BM162515438, we want to view it as BM162515437. So we will ROUNDDOWN to 0 decimal places, which effectively drops the “.5” from the part number.
=ROUNDDOWN(Cell * 1000,0)
To keep the length of our part number uniform, we will need to make one more change to our formula. Because we don’t know if we will have a blank over 10” wide, or less than 10” long, let’s make sure we take those possibilities into account in this build using TEXT. Simply adding TEXT(VALUE,”00000”) to the formula ensures that the width and length each are 5 characters in length every time.
= TEXT(ROUNDDOWN(Cell * 1000,0),”00000”)
Now we need to build a description for these new parts. We will use the CONCATENATE function to build our description. After the CONCATENATE function, either type text, or select a cell to get the value from that cell. Separate the text and values using an “,” (apostrophe).
Here “BLANK,” was entered first, then the cell value for width and length. The Description now tells us this is a blank with the size of the part. Next we need to add something to tell us the material.
On a separate tab add the above text, label the page “Ref”. This reference page will be used for multiple lookup information. Highlight an area that gives enough room to add more information. Put an outside border around the area to make it easier to identify the lookup area we are defining.
Next type “Material” in the “Name Box” (pictured below). This will define an area with this name and make doing a lookup off this information easier.
Now we will use VLOOKUP to find the material abbreviation of the code we used to create our part number. After entering the VLOOKUP function, the first variable you must choose is what we want to look up. In this case we have the material code on the first page. We will test our lookup on column “H” of “Blanks” page.
Type VLOOKUP followed by selecting the cell that holds that material code and put in the “,” (apostrophe) that separates the variables in our function. Next we want to define the area that our lookup information is in. Notice that typing “m” gives us a pull down with the area we defined earlier. Select “Material” from the pull down.
The next variable is a column number. VLOOKUP can only work from left to right. So the information we are looking up will be a number of columns to the right from the lookup column. In this case column “A” is your lookup column, column “B” is the information we want. We then need to put a numerical value of “2” (column “A” is the first column, so it will have a numerical value of “1”).
Last we need to select TRUE or FALSE. TRUE will return an approximate value. We never know what this will bring and I would recommend never using this option. Select FALSE here to return an exact match on the values we are looking up.
Now we want to incorporate this material into our description. Copy the vlookup function from the formula bar and paste into the CONCATINATE function to add the material to the description.
Congratulations, we now have a list of the blanks that are available to us to build cabinet doors. In our next tutorial we will look at the door sizes we want to build and the parts we will need to build these doors.