Excel Advanced Formulas
Excel is a very powerful program that allows you to write formulas to help control your products and make them more flexible. Microvellum uses most of the Excel functions available. We will be discussing the most commonly used functions. It is necessary to understand the basics of Overdrive Pro before using or creating formulas. "IF" Statements An IF statement is the most commonly used expression that Microvellum uses when creating formulas. The IF statement is also one of the easiest expressions to understand. The IF statement simply looks to a condition that you specify; if your condition is true the expression will return one value, and if the condition is false the expression will return another value. A good example of this is the formula in the “qty” column of the parts. The formula states =IF(G!Base_Assembly=2,1,0). Lets break the formula down. First, we have the IF statement followed by the condition G!Base_Assembly=2. Then, we have what the formula will evaluate to depending on if the condition is true or false. So, if the global variable (Base Assembly) equals 2, then our formula will evaluate to 1. If the global variable (Base Assembly) doesn't equal 2, then our formula will equal 0. Formulas can evaluate to different combinations of values, but there are different requirements for each value. For instance, if we wanted to have an IF statement evaluate to a string, or word, then you would need to include that word in parentheses. If that string is a defined name, then you do not need to include it in parentheses because it needs to evaluate that defined name. Let's break this formula down: =IF(Left_Fin_End=0,Left_Fin_End,"Left_Fin_End"). If you look at what the formula will evaluate to, it looks the same expect for the false value is in parentheses and the true value is a defined name. If the condition is true then our formula will evaluate to the value of the defined name Left_Fin_End which would be 0. If the condition is false, then the formula will evaluate to the string "Left_Fin_End" and will not give the value of the Left_Fin_End defined name. "IF AND" Statements The IF statement can be used in combination with the AND statement. The AND statement allows you to look at multiple conditions instead of just one like the regular IF statement. The formula =IF(AND(Left_Fin_End=1,Right_Fin_End=1),"Two Finished Ends", "Less than two Finished Ends") is saying that if the prompt Left_Fin_End equals 1 AND the prompt Right_Fin_End equals 1, the AND statement is true so we want to return the first value "Two Finished Ends". If either prompt does not equal 1, then the AND statement is false and we want to return the second value "Less than two Finished Ends". Even if one of the prompts equaled 1 but the other prompt did not, this statement would still be false. "IF OR" Statements Just like the AND statement, the OR statement looks to two conditions and is used with the IF statement. Instead of both conditions having to be true to evaluate to the true value like the AND statement, the OR statement will evaluate to the true value if just one of the conditions is true. Let's look at the same formula as before but with the OR statement instead of the AND statement: =IF(OR(Left_Fin_End=1,Right_Fin_End=1),"At least one Finished End", "No Finished Ends"). This formula is saying that if Left_Fin_End OR Right_Fin_End equals 1, then display "At least one Finished End". If neither of them equal "1" then display "No Finished Ends". So, for this formula to evaluate to the false condition, both finished ends must be turned off. Nested "IF" Statements At
times you may find it necessary to use multiple or “Nested” IF
statements in a formula. An example of this might be to check if the
left side is a finished end; if it is, then check to see if the finished end
type is a type 3. To accomplish this, you would place a complete IF
statement in the value_if_true argument of the first IF statement. It would look like this: =IF(Left_Fin_End=1,IF(G!FE_Type=3,G!FE_Thickness,G!Side_Thickness),G!Side_Thickness). We
know that the syntax for an IF statement is
IF(logical_test,value_if_true,value_if_false). In the above example, Left_Fin_End=1 is the “logical
test”. The complete IF statement
IF(G!FE_Type=3,G!FE_Thickness,G!Side_Thickness)
is the “value if true” argument. Then the last G!Side_Thickness is the “value
if false” argument.
Look up Tables VLOOKUP In
the example below, there are a number of nested IF statements to facilitate
the placement of dowels depending on the depth of a cabinet. =IF(L!Depth<8,L!Depth-1.437-(1.2598*3),IF(L!Depth<9.259,L!Depth-1.437-(1.2598*4),IF(L!Depth<10.519,L!Depth-1.437-(1.2598*5),IF(L!Depth<11.779,L!Depth-1.437-(1.2598*6),IF(L!Depth<13.039,L!Depth-1.437-(1.2598*7),IF(L!Depth<14.299,L!Depth-1.437-(1.2598*8),IF(L!Depth<15.558,L!Depth-1.437-(1.2598*9),IF(L!Depth<16.818,L!Depth-1.437-(1.2598*10),IF(L!Depth<18.078,L!Depth-1.437-(1.2598*11),IF(L!Depth<19.338,L!Depth-1.437-(1.2598*12),IF(L!Depth<20.598,L!Depth-1.437-(1.2598*13),IF(L!Depth<21.857,L!Depth-1.437-(1.2598*14),IF(L!Depth<23.117,L!Depth-1.437-(1.2598*15),IF(L!Depth<24.377,L!Depth-1.437-(1.2598*16),IF(L!Depth<25.637,L!Depth-1.437-(1.2598*17),IF(L!Depth<26.897,L!Depth-1.437-(1.2598*18),Dim_To_Last_Const_Hole_Deep_Cabs)))))))))))))))) Another method to accomplish the same results would be to use a lookup table and the VLOOKUP function to evaluate the lookup table. The syntax for VLOOKUP is: VLOOKUP(lookup_value,lookup_table,col_index_num,range_lookup). Lookup_value is the value to be found in the first column of the lookup table. The lookup_value can be a value, a reference, or a text string. Lookup_table is the table of information in which data is looked up. Use a reference to a defined name, such as, LookUpTableDowelPlacement. If range_lookup is true, the values in the first column of the lookup table must be placed in ascending order (..., -2, -1, 0, 1, 2, ..., A-Z, false / true); otherwise, VLOOKUP may not give the correct value. If range_lookup is false, the lookup table does not need to be sorted. The values in the first column of the lookup table can be text, numbers, or logical values. Uppercase and lowercase text are equivalent. Col_index_num is the column number in the lookup table from which the matching value must be returned. A col_index_num of 1 returns the value in the first column in the lookup table; a col_index_num of 2 returns the value in the second column in the lookup table, and so on. If col_index_num is less than 1, VLOOKUP returns the #VALUE! Error valuel If col_index_num is greater than the number of columns in the lookup table, VLOOKUP returns the #REF! Error value. Range_lookup is a logical value that specifies whether you want VLOOKUP to find an exact match or an approximate match. If true or omitted, an approximate match is returned. In other words, if an exact match is not found, the next largest value that is less than lookup_value is returned. If false, VLOOKUP will find an exact match. If one is not found, the error value #N/A is returned. After defining the lookup table below as LookUpTableDowelPlacement, we can use the following VLOOKUP function in a formula to determine the dowel placement: =VLOOKUP(L!Depth,LookUpTableDowelPlacement,2,True). In the above formula if L!Depth evaluated to 14, then the result of the formula would be the result of = L!Depth-1.437-(1.2598*8). If L!Depth evaluated to 25, then the result of the formula would be the result of = L!Depth-1.437-(1.2598*17). LookUpTableDowelPlacement
"EQ" Statements Introduction It is sometimes convenient to design a product that contains parts with a varying quantity. A good example of this is an adjustable shelf. The problem is that no two shelves can occupy the same place in space. There are two potential solutions for this problem: 1) You could add the maximum number of shelves you would ever need, and use formulas in the quantity column to determine how many have a quantity of 1 based on the quantity of shelves the user specified, or 2) You could use a special function in the Z origin of the part that is capable of placing multiple parts in different places in space. Thus, even though only one part is specified, when the product is drawn or cut listed the number of parts actually produced will be reflected by the parameters of the function. The syntax for these four special functions is described below. EQ1 Function EQ1 is utilized when we want to space adjustable shelves or fixed shelves positioned vertically in a cabinet. This function consists of arguments that specify the start and stopping point of the available space. Please see the illustration below.
The EQ1 function will read the quantity and thickness of the part to determine the spacing. The syntax can be expressed as follows: EQ1 <space> starting point <comma> stopping point. The formula for the Z origin of the part could be something like this: ="EQ1 "&Toe_Kick_Height&","&Height. If the Toe_Kick_Height is 4” and the Height is 34½” the formula would evaluate to this: EQ1 4,34.5. EQ2 Function: EQ2 is utilized when you want to use multiple quantities for a part and locate two of the parts at specified locations, calculating the remainder of the space available for additional parts. A good example of this would be nailers in a storage cabinet. You may want to have a top and bottom nailer, and a varying number of intermediate nailers, depending on the height of the cabinet. The EQ2 function consists of arguments that specify the start and stopping point of the space that is available. Please see the illustration below.
The EQ2 function will read the quantity and thickness of the part to determine the spacing. The syntax can be expressed as follows: EQ2 <space> starting point <comma> stopping point. The formula for the Z origin of the part could be something like this: ="EQ2 "&Toe_Kick_Height+G!Base_Bottom_Thickness&","&Height-G!Nailer_Width. If the Toe_Kick_Height is 4", the Base_Bottom_Thickness is ¾", the Height is 34½", and the Nailer_Width is 4", the formula would evaluate to this: EQ2 4.75,30.5. The starting point is the bottom of the product, plus the toe kick height, plus the thickness of the bottom. The stopping point is the height of the product minus the width of the part you are using. If you only have a quantity of two parts, they will be spaced at the starting and stopping points. If the quantity is greater than two, the space available between the first two will be calculated, and the remainder of the parts will be equally spaced in that area. When using the EQ2 statement, it is important to remember the base point of the parts you are spacing. In this instance, the base point for the nailer was located at the bottom of the part.
EQH Function: EQH is utilized when you want to space horizontal parts; such as, adjustable shelves or fixed shelves between vertical partition(s). The EQH function consists of arguments that either specify the quantity of the divisions, or partition the thickness of the cabinet sides to deduct from the width of the unit and the thickness of the partition. Please see the illustration below.
The syntax can be expressed as follows: EQH<space> quantity <comma> left side thickness <comma> partition thickness < comma> right side thickness. The formula for the X origin of the part could be something like this: ="EQH”&Division_Qty+1&","&G!Side_Thickness+(G!Shelf_Clip_Gap/2)&","&G!Side_Thickness+(G!Shelf_Clip_Gap/2)&","&G!Division_Thickness+G!Shelf_Clip_Gap If the quantity of shelves needed (Division_Qty+1) is 2, the left thickness will be 0.78125”, the thickness of the partition will be ¾”, and the right thickness will be 0.78125”. The formula would evaluate to this: EQH 2,0.78125,0.75,0.78125. The EQH statement uses the width of the product along with the arguments described above to determine where to place the shelves in the X origin. EQV Function: The EQV function is utilized when you want to space vertical parts, such as partitions or sleepers, equally inside a cabinet. The EQV function consists of arguments that specify both the quantity of vertical partitions and the thickness of the cabinet sides. Please see the illustration below.
The syntax can be expressed as follows: EQV<space> quantity <comma> left thickness <comma> right thickness. The formula for the X origin of the part could be something like this: ="EQV "&Division_Qty&","&G!Side_Thickness&","&G!Side_Thickness If the Division_Qty is 1 and the Side_Thickness is 3/4”, the formula would evaluate to this: EQV 1,0.75,0.75. There are three optional arguments you can also use. Just like the other arguments, use commas to separate them. The first optional argument will specify the thickness of the part. If this argument is omitted, the thickness of the part will be determined using the material name. This provision is useful for positioning intermediate stiles within a face frame. Please see the illustration below.
In this case, you would need to consider the part’s width instead of its thickness. The second and third optional arguments specify the starting and stopping points. If these arguments are omitted, the product’s origin will be considered to be the starting point, and the product’s origin plus the product’s width will be considered the stopping point.
|
|||||||||||||||||||||||||||||||||||||||
|
|
