Super Revolutionize the Way You Learn Excel Excel Become an Excel Superhero 1 Super Excel _ Modeling & Array Formulase Super Excel Revolutionize the Way You Learn Excel Become an Excel Superhero SUPER EXCEL INC. 77 CITY CENTRE DRIVE, SUITE SOL, EAST TOWER, MISISSAUGA ON LSB 1MS TEL: (67) 654-5535 WWMW.SUPEREXCEL.CA Table of Contents ‘SE3 Super Excel Modeling & Array Formulas Course Outline... What is an Excel Model?.. File #1... What is Array Formula?. an O uw MAX function against an array. File #2... Combining IF Array Formula with MAX function.. File #3 ‘Combining Multiple Layers of IF Array Formulas with SUM Function. File #4 Understanding Boolean Logic Formula Calculation Order. ‘SUM With AND and OR Criteria in One Dimension File #5 Understanding Two Dimensions Array Operations ‘SUM With AND and OR Criteria in Two Dimensions.. File #6. Change Formulas in Batch .. File #7... Understanding SUMPRODUCT function.. Use SUMPRODUCT instead of SUM for array formula File #8. Summary Reporting Model.. File #9... Array Formula to Deal with Dates. File #10... Look Up Multiple Items without Supporting Column... File #11e Super Excel Revolutionize the Way You Learn Excel Become an Excel Superhero [SUPER EXCEL INC. 77 CITY CENTRE DRIVE, SUITE 501, EAST TOWER, MISSISSAUGA ON LSB IMS TEL: (647) 654-5835 WNW. SUPEREXCELCA ‘Combining IF Array Formula with LARGE and SMALL functions . 34 File #12 INDIRECT Function File #13 30 formula, File #14 OFFSET Functior Range Name. Dynamic Range Name File #15 ‘Text Manipulation Model, File #16 Spin Button, Scroll Bar, Option Button Cascading Data Validation Dropdown List. File #17... Lookup and Return Multiple Records... File # 18... Count & Return Unique Records File #19... Exception Reporting Model SE1-A Super Excel Tables & Formatting Course Outline. SE1-B Super Excel Pivot Tables & Charts Course Outline SE2 Super Excel Data Processing & Analysis Course Outline...Super Revolutionize the Way You Learn Excel e pe Excel Become an Excel Superhero SUPER EXCEL INC. 77 CITY CENTRE DRIVE, SUITE 501, EAST TOWER, MISSISSAUGA ON LSB MS TEL: (647) 654-5835 WWWW.SUPEREXCEL.CA SE3 Super Excel Modeling & Array Formulas Course Outline 12-hour Hands-On Course Overview Business planning has become more complex while the turnaround time is getting shorter. Your business planning has to be more automated to meet the expectations. Excel could help you to automate many of the planning processes if you know the more advanced functions, array formulas, and the right structures to link the inputs, variables, formulas, and outputs together as integrated models. This hands-on course teaches you the more advanced functions and array formulas that are necessary for complex modeling. Combining with real case practices, you will gain the skills and ability to enable Excel models building and automate your business planning processes. Who Should Attend ‘This course is designed for people who have advanced level Excel skills, wish to be able to build effective Excel models with more advanced functions and array formulas to automate business planning. Attendees should be able to use VLOOKUP, INDEX/MATCH, SUMIFS, COUNTIFS, IF, ERROR, AND, OR, LEFT, RIGHT, MID, ‘SEARCH, and SUBSTITUTE functions individually as well as nest them together in multiple layers comfortably before taking this course. These pre-requisite knowledge and skills are covered in the SE2 Super Excel Data Processing & Analysis course. Topics Outline Module 1 ~ Build the foundation for all array formulas; use Boolean logic to build array formula with multiple criteria © Array formula in its simplest form * MAX function against an array Combining IF array formula with MAX function Combining multiple layers of IF array formulas with SUM function Understanding Boolean logic ‘SUM with AND and OR criteria in one dimension, Understanding two dimensional array calculations ‘SUM with AND and OR criteria in two dimensions Module 2 ~ Summary reporting model with Boolean calculations nested inside SUMPRODUCT; essential LARGE, SMALL, IF array formulas combination for multiple values extraction purpose © Change formulas in batch * Understanding SUMPRODUCT function ¢ Use SUMPRODUCT instead of SUM array formula@ Super Excel Revolutionize the Way You Learn Excel Become an Excel Superhero ‘SUPER EXCEL INC. 77 CITY CENTRE DRIVE, SUITE 501, EAST TOWER, MISSISSAUGA ON LSB 1MS TEL: (647) 654-5835 WWW ,SUPEREXCEL.CA © Summary reporting model Array formula to deal with dates © Look up multiple items without supporting column © Combining iF array formula with LARGE and SMALL functions © Create dynamic ranges with INDIRECT function Module 3 ~ Functions to create dynamic ranges and dynamic charts; structur variables and inputs with control buttons unstructured text; change model © Summarize multiple sheets data with 3-dimensional formula ‘© Create dynamic charts with OFFSET function and range names ‘© Structuralize unstructured text with text manipulation model ‘* Change model variables with spin button, option button, and scroll bar ‘* Change model inputs with cascading dropdown lists Module 4 ~ Powerful array formulas to extract all records meeting criteria; exception reporting model Array formula to look up and return multiple records Array formula to count unique records from transactional data Array formula to return unique records from transactional data Exception reporting modelSuper ] Revolutionize the Way You Learn Excel Exce Become an Excel Superhero SUPER EXCEL INC. 7 CITY CENTRE DRIVE, SUITE 501, EAST TOWER, MISSISSAUGA ON LSB IMS TEL: (647) 654-5835 WwW SUPEREXCEL.CA What is an Excel Model? It is a mechanism within Excel to transform structured data inputs into structured data outputs based on certain business rules automatically. Modeling process itself should follow a structured approach. Here within this course, we do not talk about the process of modeling, which is a management course. Instead, we focus on the various Excel functions/features that will help you to build Excel models that can automatically transform data inputs into data outputs based on given business rules. You can achieve Excel automation with formulas or VBA. With VBA, you are programming more like an IT professional; formulas are relative simpler and are understood by much broader populations of Excel users than VBA is. Within this course, we do not talk about VBA; we focus on using functions/formulas and other Excel features to build Excel models. Excel models can be used to generate periodic reports, transform raw data to a more usable structured format, conduct business planning, run what if analysis/sensitivity analysis to facilitate decision making, ‘An Excel mode! should have three clearly separate parts: INPUTS, PROCESSING, OUTPUTS. OUTPUTS are the ultimate results you would like to have by running the model. This could be in table format or chart format. INPUTS are the given data you will need to produce the outputs. Some inputs are constants and do not change, some inputs are variables and keep changing, and some other inputs are the large raw data stored in tables. PROCESSING are the formulas you build into the model to transform the input data into output data. With more complex business rules, your formula will become more complex accordingly. ‘A model should produce the outputs automatically, or as automated as possible. It should handle the various scenarios your business will encounter. When the input data changes, the output data should change automatically, and produce output correctly based on given business rules. To create an Excel! model, you should have clearly identified the OUTPUTS, INPUTS, and the business rules you will use for PROCESSING. The business rules have to be clearly documented, often times with flowcharts or similar diagrams. If it only exists in Your head, you do not really have a clear set of business rules. When you write them down on paper or type on computer, it will help you to solidify and clarify the rules and catch any loopholes. In order to get the Excel model more automated, you need to use more advanced Excel functions and their combinations to accomplish. Array formula will help you dramatically to achieve automation. You will learn more advanced formulas, techniques, and Excel features as you progress through the course materials.Super Revolutionize the Way You Learn Excel e ee Excel Become an Excel Superhero SUPER EXCEL INC. 77 CITY CENTRE DRIVE, SUITE 503, EAST TOWER, MISSISSAUGA ON LSB 1MS TEL: (647) 654-5835 WWW.SUPEREXCELCA File #1 What is Array Formula? Before we can understand what array formula is, let's first understand what array is, An array is a collection of two or more items. When you have one single cell or single value, it’s not an array, When you have two or more cells, or two or more values, it's an array. An array formula is a formula that contains an operation (calculation) on an array, and the operation (calculation) returns an array. Array worksheet 1 Select cell range H10:H14. If you started your selection from H40, then you will notice your cursor will stay in cell H10 after you select the cell range H10:H14. ‘Type equal sign "=", select the cell range E1:E24, type the multiply sign "*”, select F10:F24, then hold on Ctrl and Shift, then press Enter (Ctrl+Shift+Enter) Now you have entered an array formula. if you look at the formula bar, you will notice there is a pair of curly brackets wrapping the whole formula, {=€20:£14*F10:F14} The curly brackets signify this is an array formula. ‘The curly brackets in array formula cannot be entered with curly bracket keys directly, it can only be entered with Ctrl+Shift+Enter. With this array formula, it takes every cell in first array multiplies the corresponding cell in the second array, and returns five values. Now select any one of the cells within the array H10:H14, and try to delete the cell content with a Delete key, a ‘message pops up and advise you “You cannot change part of an array”. The H10:H44 range is one integrated piece (one array), you can select the whole array and delete, but you cannot change one part of the whole. Select cell H10, press F2 to edit the cell content, You see the formula =E10:€14*F20:F24 in the cell. fyou press F9. It displays =(10;40;90;160;250}. F9 key performs calculation of this formula, and display the formula calculation results, which is an array that comprises those five numbers. Press Esc to cancelll out and e 0 you do not change the formula. a. Instead of calculating the entire formula, you could select only part of the formula, then press F9, it will the cell calculate only the selected formula componet. Similarly, you can enter another array formula in cell range E20:620, which multiplies horizontal array E17:G17 with horizontal array £18:G18, In viewing the formula resultant array after pressing F9, you notice those numbers are separated with either a ‘comma (,) or a semicolon (;). When Excel display array values, it uses comma to separate values from different column and semicolon to separate different rows. To help you remember which one is for column and which one is for row, just think about both Column and Comma starts with C, so Comma is for Column. MAX function against an array MAX MIN Worksheet@ Super 1 Revolutionize the Way You Learn Excel Exce Become an Excel Superhero SUPER EXCEL INC. 77 CITY CENTRE DRIVE, SUITE 501, EAST TOWER, MISSISSAUGA ON LSB IMS TEL: (647) 654-5835 WWW/SUPEREXCEL,CA Here is a table shows the sales $ for each product in both TY and LY. You would like to know among all the products, what is the largest sales $ increase and what is the largest sales $ decrease when comparing TY with LY. Without the knowledge of array formula, you will ikely do the formulas in column G first to calculate the sales $ difference between the two years for each of the 140 products, then you use a MAX function (G6) to return the largest number based on the 140 difference cells, then use a MIN function (G7) to return the smallest number (decrease is a negative number, so the ‘smallest number is the largest decrease) based on the 140 difference cells. With array formula, you do not need to calculate the sales $ difference between the two years for each product, as this intermediate step can be done within the array formula without displaying it explicitly on the spreadsheet. In cell 16, you can enter an array formula =MAX(F11:F150-E11:£150), when you enter, do not simply press Enter key, instead, you hold on Ctrl and Shift, then press Enter (Ctrl+Shift+Enter). The formula result wil be the largest increase from LY based on all 140 products. After you entered the formula with CtrleShift+Enter, you will notice the formula bar shows the formula with a pair of curly brackets. If you use FZ to go into the cell, you do not see the curly bracket. if you simply enter the formula with Enter key, the formula result will be an error of #VALUEI, as you entered 2 formula with array operation but you did not enter it as array formula by Ctrl+Shift+Enter, so it returns you an error. if you use F2 again to go into the cell with the formula, then Ctrl+Shift+Enter, now you did enter the array formula in the right way, then it returns with the right result, A formula could contain multiple nested functions which conduct multiple operations (calculations). The result of an array formula does not necessarily have to be an array, as in this formula you just did, it performed a MAX function against the resultant array of F11:F150-E11:E150, and returns to you one single value to be the largest value out of the 140 values. This formula F11:F150-£11:E150 does perform an array operation, which use 140 cells minus another 140 cells, and returns to you 140 values. 150, essentially does 140 calculations (F11-E11, F12-E12, F13-E13...) 1150. Here however we do not The array operation (calculation) of F11:F150-€11:1 and returns the 140 values, which are equivalent to formula results on cell range G11: need to display the 140 values, we just need to return the largest value out of the 140 values. So we use a MAX function against the 140 values to return the largest number. To gain a visual sense what the array operation F1:F150-E11:E150 return, you can go into the formula cell by pressing F2, select F11:F150-£11:150, then press F9 (F9 will calculate the selected formula), it will display the calculation result Of the 140 values, which are same results as range G11:G150. It’s better to undo with Ctrl +Z or press Esc to escape after ‘otherwise your formula would be lost and only the values are kept in that formula. MAX(numbert, [number2},...) Returns the largest value in a set of values. Ian argument is an array or reference, only numbers in that array or reference are used. Empty cells, logical values, or text in the array or reference are ignored. MIN(numbert, [number2], Returns the smallest number in a set of values,© Super Excel Revolutionize the Way You Learn Excel Become an Excel Superhero SUPER EXCELINC. 7 CITY CENTRE DRIVE, SUITE 50, EAST TOWER, MISSISSAUGA ON L58 IMS TEL (647) 654-5835 WWW.SUPEREXCEL.CA Ifan argument is an array or reference, only numbers in that array or reference are used. Empty cells, logical values, or text in the array or reference are ignored. Bonus SUM worksheet AVERAGE worksheet The orray formulas within these two worksheets are very similar to MAX MIN worksheet, instead of performing MAX or ‘MIN function, you perform a SUM or AVERAGE function against an resultant array. Excel Modeling Techniques Model Structure INPUTS © No calculation. if there is any calculation, move it to the processing section. © Trytolimit number of manual entry. ifit is driven by another variable, identify the relationship and build a formula to calculate that, and it becomes part of the processing. PROCESSING © Formula only, no manual entry except headings © If the users do not need to see processing sheets, you may want to hide them so it looks simpler and cleaner. ourpurs ‘© Extract from processing worksheets ‘* Concatenate variables with "&” to create dynamic descriptions © Use charts to communicate results '* Use conditional formatting to highlight record values exceeding dynamic thresholds ‘© Use check box as the switch to turn on and off the conditional formatting if needed User interface © Depending on the nature of the model, may contain only the variable part of the inputs, and/or a brief summary ofthe outputs ‘© Use data vatidation for inputs to avoid typing error ‘© Use Spin button, scroll bar, check box, option button to change variables ‘© Removing the gridlines and headings so it more visually appealing Logic flow © Sheets will normally be tabbed with inputs first, processing second, and outputs last when you are building the ‘model.@ Super Excel Revolutionize the Way You Learn Excel Become an Excel Superhero SUPER EXCEL INC. 77 CITY CENTRE DRIVE, SUITE S04, EAST TOWER, MISSISSAUGA ON 158 1MS TEL: (647) 654-5835 WWW.SUPEREXCEL.CA ‘+ When the model is finalized, from user perspective, itis much preferable to have the outputs up front as the very {first sheet they see when they open the file; if there are just very few variable changes users need to make, they should be placed on the same output worksheet left or above the output results. © Ona processing sheet, the logic should be flowing from top to bottom and from left to right. © Refer values to the original cells, do not create duplicate values cells and reference to the duplicate. if you do not ] want to switch back and forth between different worksheets, name the ranges and use the names in the | formulas. Model Building Techniques © Changing formulas in batch Use range names to make formula writing simpler # Organize the spreadsheet into structured modules . ‘© Draw flowchart for complex business rules | © Donot hide rows or columns to avoid potential problem © Set different color for different tabs (inputs, processing, outputs) ‘* Turn auto calculation to manual so you do not have for wait for the calculation, calculate by pressing F9 when |” pucomplet a brge © Array formulas may not be able to handle the large amount of calculation and take long time to calculate. Very | careful testing it first, do not wait to the end to find out you have to switch from arrey formula method to regular formula approach, as this may require complete revamp of the model structure. © Document important data and formula * Version control - save copies of model development at various stages in case you need to revert back ‘* Use sheet protection to prevent unwanted changes. | Model Testing Techniques | © Bug is most likely in your models } © Use extreme input to test the results '® Show formula/ctrl# (Grove) ‘© Ctrl+® to find errors start with #, remember to find in values not formula © Watch Window © Formulas « Formula Auditing « Watch Window «> Add Watch © Click the cells you would like to J watch Add * Copy range of cells and paste as lve picture © Select the range, Ctrl + C to copy it, select the cell where you plan to paste, Home «> Paste Dropdown Arrow © Linked Picture | + F9 to calculate a part of the formula J * Formula auditing - tracing precedents and dependents ‘© Ctrl+[ to jump to direct precedent cells, F5, Enter, will go to previous cell, which allows to return to original cel. Ctrft] to jump to direct dependents. © Ctrl+{/} trace both direct & indirect precedents/dependentsD Super Revolutionize the Way You Learn Excel e i Excel Become an Excel Superhero SUPER EXCEL INC. 77 CITY CENTRE DRIVE, SUITE 501, EAST TOWER, MISSISSAUGA ON LSB 1MS TEL: (647) 654-5835 WWW.SUPEREXCELCA File #2 Combining IF Array Formula with MAX function MAX IF worksheet Here is a table shows the sales $ for each product in both TY and LY. You would like to know within each product family, what is the largest sales $ increase and what is the largest sales $ decrease when comparing TY with LY. if not using array formula, you most likely have to calculate the sales $ change for every single record, then sort the table by product family, then manually select the sales $ change cells range of each Product Family to perform a MAX function. With array formula, the whole thing can be done in one single cell, Before looking at the array formula, let's understand the IF function with omitted third argument. {n cell 124, it contains an IF function =IF(D11=$K$4,F11-E11). Different from what people would normally do, here this IF function does not have the third argument. In IF function, when the first argument criterion is being met, it returns second argument, otherwise it returns 3° argument or a logical value FALSE if the third argument is omitted. By coping this formula down for the rest of records, you performed the IF function 140 times, and only calculate the two years sales $ difference when product family is Frozen. For non-Frozen product family records, they all return FALSE. The MAX function in cell 111 will return the largest value in the given array and it ignores the FALSE logical values in the array or cell reference. Imagine you do the same IF formula, not against first record product family, and not using first record TY sales $ minus LY sales $, instead, you do that against the array for all 140 records product family, and all 140 records TY sales $ minus all 140 records LY sales $. ‘One record regular formula: IF(D11=$K$4,F11-E11) 140 records array formula: ($D$11:$D$150=5 K4,SF$11:$F$150-$E$11:$ES150) Then you put this IF array formula into the MAX function as in cell L4 =MAX(IF($D$11:$D$150=$k4,SFS11:$FS150- $E$11:$E$150)). In the above array formula, the part IF($D$11:$0$150=$k4,$FS11:$F$150-SE$11:$E$150) basically does IF function 140 times for the 140 records, and returns an array of 140 values, which are equivalent to range 111:1150. For those records that do not meet the criteria, the IF returns FALSE logical values. When we do a MAX function against range 111:1250, it returns the largest number in the range and ignores the logical value FALSE. To gain a visual sense of what values IF($D$11:$D$150=$K4,$F$11:$F$150-SE$11:SE$150) return, go to the cell La and press F2, select the part of IF($D$11:$D$150=$K4,SF$11:$FS150-SES11;$$150), press F9, this displays the calculated results from the IF array formula, which are equivalent to range 111:1150. You will see lots of FALSE values, which are the ones not meeting the criteria, You can apply this concept with many other scenarios, for example, you may do SUM, COUNT, VLOOKUP, INDEX/MATCH based on an array returned by IF function. 10e Super Revolutionize the Way You Learn Excel Excel Become an Excel Superhero SUPER EXCEL INC. 7 CITY CENTRE DRIVE, SUITE 501, EAST TOWER, MISSISSAUGA ON L58 1M TEL: 647) 654-5835 WWW SUPEREXCEL.CA ®@Bonus AVERAGE IF worksheet Array formulas in this worksheet ore very similar to array formulas in MAX IF worksheet. Instead of performing @ MAX Junction, here you do an AVERAGE function; in the array operation part, instead of using one array minus another array, you use the result of the first array minus the second array to be divided by the second array to calculate the percentages. File #3 Combining Multiple Layers of IF Array Formulas with SUM Function ‘SUM Multiple if t Here is a table contains sales $ in TY and LY for each of the four product families and in different weeks. For this company, fiscal weeks 11 to 14 are the four weeks falling within Christmas season. You are required to calculate the sales $ change between the two years during only Christmas season for each individual product family. Without using array formula, this will require lots of intermediate steps to get final results. With array formula, the whole thing can be done in one single cell. To help you to understand this array formula, let's look at the regular formulas in column F to |. {In cell F14, it contains a regular formula =IF(C11="Frozen",£11-D11), We already understood from previous exercises that when the third argument of an IF function is omitted, and the first argument criteria is not being met, it will return a logical value FALSE. With this formula copied and pasted to each record below, it evaluates each record Product Family. If Product Family is “Frozen”, it returns the sales $ difference between the TY and LY; when Product Family is not “Frozen', it returns logical value of FALSE. In cell G14, it contains a regular formula =IF(C11="Frozen",IF(B11>=11,E11-D11)). This formula is a two-layer IF functions nested together. (Understanding of multiple layers of nested IF formula is a pre-requisite of this course, so here we are not elaborating on the concept of multiple layers of nested IF formula.) Basically when Product Family is “Frozen” and Week Number is greater than or equal to 11, it returns the sales $ difference between TY and LY; otherwise, it returns logical value FALSE. In cell H44, it contains a regular formula =1F(C11="Frozen"IF(B11>=11,1F(B11<=14,€11-D11))). This formula is a three- layer IF functions nested together. Basically when Product Family is “Frozen”, Week Number is greater than or equal to 11, and Week Number is less than or equal to 14, it returns the sales $ difference between TY and LY; otherwise, it returns logical value FALSE, So when all three criteria are being met at the same time, it returns sales $ difference, otherwise, it returns FALSE. After you copy and paste this formula down for all 140 records, you have performed the ‘same calculation for each of the 140 records. You will notice most cells return FALSE; only cells H120:H123 return the sales $ difference, because those are the only four records that meet all three criteria. With the result in range H11:H150, you can easily do a SUM function in cell 111 to sum range H11:H150, SUM function only adds numeric values in the range, logical values are ignored. So finally you get the sales $ change in Christmas season between TY and LY for Frozen Product Family after 140 formulas in column H and one formula in cell 111. SUM(numbert, [number?],...) a@ Super Excel Revolutionize the Way You Learn Excel Become an Excel Superhero SUPER EXCELINC.77 CIT CENTRE DAVE, SUITE 50, EAST TOWER, MISSISSAUGA ON 158 1MS TEL: (647) 654-5835 WWW SUPEREXCEL.CA The SUM function adds all ofits arguments, Ian argument is a cell range or reference, only numeric values in the reference or range can be added. Empty cells, logical values like TRUE, or text are ignored. Instead of doing cell #11 formula =IF(C11="Frozen”,IF(B11>=11,1F(B11<=14,£11-D11)) in 140 cells, you can do the 140, calculations in one single cell with an array formula. You will do essentially the same multiple layers of IF function as, =IF(C11="Frozen" |F(B11>=11,1F(811<=14,£11-D11)), but instead of using single cell reference like C11, B11, E11, D1, you use array reference C11:C150, 811:B150, £11:£150, 011:0150. The formula becomes an array formula F($C$11:$CS150=15 IF($BS11:$8$150>=11,F($BS11:$B$150<=14,$E$11:$25150-$D$11:$0$150))). Here we have all those array reference to be locked absolute, because we are going to copy the formula down from J5 to J9 and wish the array reference remain the same. This array formula essentially does =IF(C11="Frozen",IF(B11>=11,1F(B11<=14,£11-D11))) regular formula 140 times respectively for the 140 records. Its result is the same as the range H11:H150. IF you select cell 5, press F2 to edit, select the part IF($C$11:$C$150=15,1F($B$11:$8$150>=11,IF($B$11:$B$150<=14,$E$11:$€$150-$0$11:$0$150))), then press F9. It calculates this array formula and displays the array results, which is equivalent to range H11:H150. Now when you use a SUM function to sum that array results from the IF array formula, you are essentially sum those four records’ sales $ difference. (The four records meeting all three criteria) =SUM(IF(SC$11:$C$150=L5, 1F($B$11:$B$150>=11,1F($B$11:$8$150<=14,$E$11:$E$150-$D$11:$D$150)))) ‘When you do the multiple layers of IF array formula, it returns you an array for those records meeting all the criteria at the same time. if your criteria is either or of the multiple criteria, you cannot use the multiple layers nested array IF formulas. You have to use a different approach, which will be discussed later. ®Bonus AVERAGE Multiple IF worksheet Array formulas in this worksheet are very similar to array formulas in SUM Multiple IF worksheet. Instead of performing @ SUM function, here you do an AVERAGE function; in the array operation part, instead of using one array minus another array, you use the result of the first array minus the second array to be divided by the second array to calculate the percentages. File #4 Understanding Boolean Logic Boolean Logic worksheet Boolean data type is a data type that has only two possibilities, either TRUE or FALSE. 2Super ] Revolutionize the Way You Learn Excel Exce Become an Excel Superhero ‘SUPER EXCEL INC. 7 CITY CENTRE DRIVE, SUITE 501, EAST TOWER, MISSISSAUGA ON LS® IMS TEL: (47) 654-5835 WWW SUPEREXCEL.CA TRUE and FALSE are two logical values. Within Excel, TRUE represents 1, FALSE represents 0, In Boolean logic, any non-O number also represents logical value TRUE. Please note logic values are not texts, and they are not numbers either. They are logic values You can convert Boolean logical values to numbers by performing any math operation on them. In cell ES and E6, when you enter a formula with double negation against the logical value, they return numbers, To prove any non-O number is TRUE, in cell E7, you type the formula =1F(D7, TRUE). D7 contains number 5, which represents TRUE, when the first argument of IF function is TRUE, it returns the second argument, which is TRUE; when you change D7 cell content to 0, the formula in cell E7 returns FALSE, this also proves O represents FALSE. You can do few other math operations (multiplication, division, addition, etc...) in range C12:D15 to turn the two logical values in cell C11 and D414 into numbers. With the Boolean logic, when you multiply criteria against each other, it serves as the AND criteria; when you add criteria together, it serves as the OR criteria, In cell G19, when you multiply 019 with £19 and then multiply F19, essentially TRUE*TRUE*TRUE=1*1*1=1=TRUE. In cell G20, when you multiply D20 with E20 and then multiply F20, essentially TRUE*FALSE*TRUE=1*0*1=0=FALSE. When you use multiplication on Boolean values, only when every value is TRUE, it will return TRUE; if any value is FALSE, it will return FALSE. So Boolean values multiplication serves as AND criteria. In cell G24, when you add D21 with E21 and then add F21, essentially TRUE+TRUE*TRUE=1+1+1=3-TRUE, 40+1=2=TRUE, In cell G22, when you add D22 with E22 and then add F22, essentially TRUE+FALSE+TRUE=! In cell G23, when you add D23 with £23 and then add F23, essentially FALSE+FALSE+FALSE=0+0+0=0-FALSE. When you use addition on Boolean values, only when every value is FALSE, it will return FLASE; if any value is TRUE, it ill return TRUI jon ser OR criteria. We will be doing lots of calculations with Boolean values later on. You need to be aware of that when you sum the Boolean values, SUM function treats non-numerical values as 0, In cell 119, enter the formula =SUM(D19:F19). In the range D19:F19, it comprises three logical values, which are not ‘numerical, then SUM function treats them as 0, the formula returns 0. If you wish those logical values are treated as numbers in SUM function, you need to convert them to numbers first, before summing them, in cell 19, enter the formula =SUM|--D19:F19) with CtrlsShift+Enter. it returns 3. Ths time, itis ‘not purely summing the three cells. It first performs the double negation against an array D19:F19, which converts the logical values to numbers and returns an array of {1,1,2}; then the SUM function sums those three numbers. Once you perform any math operation on logical values, they will be converted to numbers automatically; you have no need to apply double negation again before summing. Only when you sum logical values directly, you have to convert them to numbers first. BSuper Revolutionize the Way You Learn Excel e "Excel Become an Excel Superhero SUPER EXCELINC. 7 CITY CENTRE DRIVE, SUITE S01, EAST TOWER, MISSISSAUGA ON (58 1MS TEL: (647) 654-5835 WWW SUPEREXCEL.CA Formula Calculation Order Formula Calculation shee! When there are lots of operations within a long formula, you need to understand the order of different operations to make sure the formula is calculating in the way you intended. The operations are calculated in below order. 1. Parentheses/brackets: () =(1+2)*3=9, 1+2*3=7 2. Reference operators: colon =A1:A10+5 3. Negatio! (442) 4, Perce 1%=2.01, (2+1)%=0.03 5. Exponentiation: &=14342=10, (143)42=16 6. Multiplication and Divis , left to right 7. Addition and Subtraction: +, -, left to right 8, Ampersand: & =3+18 9. Comparison operator SUM With AND and OR Criteria in One Dimension ‘SUM 10 worksheet Here is a simplified table in the range of N48:R26, showing sales $ by Region by Product Category and by Week. You need to perform two tasks: Task #1 - Calculate Week 2 Sales $ if Region is B AND Category is AB: You can accomplish this in many different ways. In cell R13, you can enter a regular formula of SUMIFS function =SUMIFS(Q19:026,N19:N26, Week 2 sales $ based on two criteria, ,019:026,"AB") to sum In cell R14, you can enter an array formula that you just learned in previous file to get the results, =SUM(IF(N19: ‘aB",219:026))) Now you will learn two new ways of array formula to accomplish this. In cell R15, you can enter the array formula of =SUM(IF((N19:N26="B")*(019:02 'AB"),019:026)). The SUM function is to sum an array. The array Is produced by the IF array formula, which are the week 2 sales $ for those records meeting the two criteria of Region is B and Category is AB. Here we did not use the two layers of nested IF function, we only used one IF function, however the first argument is an array operation Using the first criteria multiplies the second criteria, From previous Boolean math exercise, we understood that, ‘multiply two logical values serves as AND criteria. When both criteria are being met, this first argument returns ‘TRUE, then second argument would be returned, otherwise, FALSE would be returned as third argument is. omitted. This IF array formula essentially returns to you an array that is equivalent to range 119:126, so, there is no need to do multiple layers of IF functions for multiple AND criteria, you just need to use one IF function, and use one criteria multiplies another criteria as the first argument. 4Super Revolutionize the Way You Learn Excel e P Excel Become an Excel Superhero SUPER EXCELINC. 77 CITY CENTRE DRIVE, SUITE 501, EAST TOWER, MISSISSAUGA ON LS® 1MS TEL: (647) 654-5835 WWW.SUPEREXCEL.CA | } Inceli R16, you can enter the array formula of "AB")*Q19:026), UNA: *(019:02 The SUM function is to sum an array. The array is produced by array operations, where the first criteria multiplies the second criteria and then multiplies the actual sales $. When both criteria are being met, these two array operations are essentially doing the 1*1*sales $, which will return the sales §; if any one of the two criteria is not being met, these two array operations are essentially doing 1*0*sales $, which will return 0. These two multiplication array calculation essentially return to you an array that Is equivalent to range H19:H26, so the ‘SUM function only sum the one record sales § that its Region is B and Category is AB. In this array formula, we did not put the range Q19:026 into parentheses because the colon reference operator will be calculated first before the multiplication, so adding parentheses for the range Q19:026 is not necessary, but will not cause any problem either. if you do not remember the order of the different operations, and need to complete a formula immediately, then go ahead to put in the parentheses so you are sure everything within the parentheses will be calculated first. Task #2 - Calculate Week 2 Sales $ if either Region is B OR Category is AB: y You can accomplish this also in different ways, but not as many as in the task #1 As indicated in cell R30, this cannot be done with SUMIFS function as SUMIFS function only sum when all criteria are being met. As indicated in cell R34, this cannot be done with multiple layers of IF array formula either. It evaluates true when all nested criteria are being met. Now you will learn two new ways of array formula to accomplish this. In cell R32, you can enter the array formula of =SUM(IF((N19:N26="B")+(019:026="AB"),019:026)). The SUM function is to sum an array. The array is produced by the IF array formula, which are the week 2 sales $ for those records meeting criteria of either Region is B or Category is AB. Here we used one IF function, however the first argument is an array operation using the first criteria plus the second criteria. From previous Boolean math exercise, we understood that adding two logical values serves as OR criteria. When either one of ) the two criteria are being met, this first argument returns TRUE, then second argument would be returned, otherwise, FALSE would be returned as third argument is omitted. This IF array formula essentially returns to you an array that is equivalent to range G19:G26. In cell 33, if you enter the array formula of =SUM(((W¥19:NN26="B"}+(019:026="AB"))" Qu 26), this will be WRONG, ‘The SUM function is to sum an array. The array is produced by array operations, where the first criteria plus the second criteria and then use that result multiplies the actual sales $. ‘* When any one of the two criteria is being met, these two array operations are essentially doing (140)*sales $ or (O+41)*sales $, which will return sales $; + When none of the two criteria is being met, these two array operations are essentially doing (0+0)*sales $, which will return 0; 15@ Super Excel Revolutionize the Way You Learn Excel Become an Excel Superhero SUPER EXCEL INC. 77 CTY CENTRE DRIVE, SUITE 501, EAST TOWER, MISSISSAUGA ON LSB 1MS TEL: 647) 654-5835 WWW. SUPERENCELCA ‘When both criteria are being met, these two array operations are essentially doing the (1+1)*sales $, which will return double of the sales $. This is causing the sales $ be 1g doubled, which is wrong, ‘These array calculations essentially return to you an array that is equivalent to range F19:F26, so the SUM. function sums double of the two records sales $ that their Region are B and Category are AB, So, how to avoid this problem? In cell R34, enter the array formula of problem. UM ((((N19:N26="B")+(019:026="AB"))>0)*Q19:026), this will solve the The SUM function is to sum an array. The array is produced by array operations, where the first criteria plus the second criteria and then evaluate if that addition result is greater than 0; finally use this result multiplies the actual sales $, When any one of the two criteria is being met, the addition array operation is essentially doing (110) or (0+2), which will return 1; when we further evaluate this result (1) is greater than O, it does return 2 (TRUE}; finally take this 1 multiplies the sales §, it returns the sales «when none of the two criteria is being met, the addition array operation is essentially doing (0+0), which returns 0; when we further evaluate this result (0) is greater than 0, it does return O (FALSE); finally take this 0 multiplies the sales $, it returns 0. ‘© when both criteria are being met, the addition array operation is essentially doing the (1+1), which returns 2; when we further evaluate this result (2) is greater than 0, it does return 1 (TRUE); finally take this 1 multiplies the sales $, it returns the sales ‘© So this means when you have multiple OR criteria (regardless it’s two, three, five, 10 criteria), you can always turn that into 1 or O by adding them all together, then evaluate the addition result is greater than 0 These array calculations essentially return to you an array that is equivalent to range E19:E26, so the SUM function sums only the two records sales $ that either their Region are B or Category are AB. @Bonus SUM AND OR worksheet Array formulas in this worksheet are exactly the same concepts to array formulas in SUM 1D worksheet. Here we just have more realistic data set which is larger than data in SUM 1D worksheet, where we used very simple and small data sset to demonstrate the concepts. File#5 Understanding Two Dimensions Array Operations In previous file, you learned to sum based on multiple AND/OR criteria, but all those arrays are in one dimension ~ they are all single vertical range. Now you will learn how it works when you perform two dimensions array operations. 20 Array worksheet 16Super 1 Revolutionize the Way You Learn Excel e Exce Become an Excel Superhero SUPER EXCEL NC. 77 CITY CENTRE ORIVE, SUTE 501, EAST TOWER, MISSISSAUGA ON LSB JMS TE: (647) 654-5835 WJW.SUPEREXCEL.CA First example; enter the array formula =SUM(810:814*C9:E9) in cell C16. What does it really do? Let's select the cell C16, press F2 to editit, then you see the formula. Select the part of the formula reads 810:B14*C9:£9, then press F9 to calculate. It displays an array as {20,40,60;40,80,120;60,120,180;80,160,240;100,200,300}. This is what the SUM function is summing. As we learned before, comma separates columns, semicolon separates rows. This array has five rows and three columns as below: 20,40,60; 40,80,120; 60,120,180; 80,160,240; 100,200,300 This array operation takes each cell in the first vertical array multiplies first cell in the second horizontal array; then take each cell in the first vertical array multiplies second cell in the second horizontal array; and then take each cell in the first vertical array multiplies the third cell in the second horizontal array. It does 15 calculations. This is essentially doing the 15 regular formulas in the range G10:114. You can actually calculate the array formula }10:B14*C9:E9 and return the results in 15 cells, Select the range G17:I21, }10:814*C9:E9, enter the formula by Ctrl+Shift+Enter. The same 15 values are returned in the range Second example: enter the array formula =SUM(L10:L14*M9:09*M10:014) in cell M16. What does it do? Let's select the cell M26, press F2 to edit it, then you see the formula. Select the part of the formula reads L10:L14*M9:09*M10:014, then press F9 to calculate. It displays an array as {20,240,660;80,560,1440;180,960,2340;320,1440,3364 °00,2000,4500}. This is what the SUM function is summing, 1. First row cell in first vertical array a. This array operation takes first cell in the first vertical array multiplies first cel in the second horizontal array, then multiplies the first row first column cell in the third matrix array; It takes first row cell in the first vertical array multiplies second column cell in the second horizontal array, then multiplies the first row second column cell in the third matrix array. It takes first row cell in the first vertical array multiplies third column cell in the second horizontal array, then multiplies the first row third column cell in the third matrix array. 2. Second row cell in first vertical array a. It takes second row cell in the first vertical array multiplies first column cell in the second horizontal array, then multiplies the second row first column cell in the third matrix array. b. It takes second row cell in the first vertical array multiplies second column cell in the second horizontal array, then multiplies the second row second column cell in the third matrix array. It takes second row cell in the first vertical array multiplies third column cell in the second horizontal array, then multiplies the second row third column cell in the third matrix array, ”7e Super Excel Revolutionize the Way You Learn Excel Become an Excel Superhero SUPER EXCELINC. 77 CITY CENTRE DRIVE, SUITE 503, EAST TOWER, MISSISSAUGA ON LSB 1MS TEL: (647) 654-5835 WWW.SUPEREXCEL.CA, It continues on in the same pattern until finish all five row cells in the first vertical array. It does 15 calculations. This is essentially doing the 15 regular formulas in the range Q10:514. You can actually calculate the array formula =110:L14*M9:09*M10:014 and return the results in 15 cells. Select the range Q17:521, type the formula =L10:L14*M9:09*M10:014, enter the formula by Ctrl+Shift+Enter. The same 15 values are returned in the range Q17:S21. Now you understand when you perform two-dimension multiple array operations, what it actually does. In above examples, you did multiplication; when you do addition, subtraction, division, they are being done in the same structure. SUM With AND and OR Criteria in Two Dimensions SUM 20 Array worksheet Here is a simplified table in the range of N18:R26, showing sales $ by Region by Product Category and by Week. You need to perform two tasks: Task #1 - Calculate total Sales $ if Region is B AND Category is AB AND Week Number is greater than or equal to 2: This is very similar to what you learned in previous file, but now two criteria are evaluated against vertical range, one criteria is evaluated against horizontal range. THIS CANNOT BE ACCOMPLISHED WITH SUMIFS FUNCTION as it requires all evaluation ranges and sum ranges to be same number of cells. Here the evaluation ranges N19:N26, 019:026, P18:R18, and sum range P19:R26, are definitely not the same size. You can accomplish this in many other different ways. {In cell R42, you can enter an array formula as =SUM(IF(N19:N26="B",1F(019:026: get the results. "6 (P18:R18>=2,P19:R26))) to In cell R13, you can enter the array formula of =SUM(IF((N29:N26="B")*(019:026="AB")*(P18:R18>=2),P19:R26)). This, formula essentially does the same thing as above formula. One use multiple layer of IF approach to evaluate the AND criteria, another use multiplication of logical value approach to evaluate the AND criteria The SUM function is to sum an array. The array is produced by the IF array formula, which are the sales $ for those records meeting the three criteria of Region is B and Category is AB and Week Number is greater than or equal to 2, When all three criteria are being met, this first argument returns TRUE, then second argument would be returned, otherwise, FALSE would be returned as third argument is omitted. This IF array formula essentially returns to you an array that is equivalent to range T29:V26. You need to apply the knowledge you learned in 2D Array worksheet to visualize what this IF two-dimension multiple arrays formula returns. In cell R44, you can enter the array formula of =SUM{(NI9:N2 }*(P18:R18>=2)*P19:R26). ‘The SUM function is to sum an array. The array is produced by array operations, where the first criteria multiplies the second criteria and multiplies the third criteria then multiplies the actual sales $. When all three criteria are being met, these array operations are essentially doing the 1*1*1*sales $, which will return the sales 18In cell R34, you can enter the array formula of In cell R35, enter the array formula of =SUM((((NL! Super Revolutionize the Way You Learn Excel € P Excel Become an Excel Superhero SUPER EXCEL INC. 77 CITY CENTRE DRIVE, SUITE 503, EAST TOWER, MISSISSAUGA ON LS 1MS TEL: (647) 654-5835 WWW. SUPEREXCEL.CA $: if any one of the three criteria is not being met, these array operations are essentially doing 1*0*1*sales $, which will return 0. These multiplication array calculation essentially return to you an array that is equivalent to range X19:226, so the SUM function only sum the two cells sales $ that its Region is B and Category is AB and Week Number is greater than or equal to 2, Task #2 - Calculate total Sales $ if (either Region is B OR Category is AB) AND Week number is greater than or equal to UIMI(IF(((N19:N26="B"}+(019:026="AB"))*(P18:R18>=2),P19:R26)). The SUM function is to sum an array. The array is produced by the IF array formula, which are the sales $ for those records meeting criteria of (either Region is B or Category is AB) and Week Number is greater than or equal to 2. Here we used one IF function, however the first argument is an array operation using the first criteria plus the second criteria, then take that resultant array multiplies the third criteria. From previous Boolean math exercise, we understood that adding two logical values serves as OR criteria, and multiplying two logical values serves as AND criteria. © When either one of the two criteria are being met, and the third criteria is being met, this first argument returns (140)*1, which is TRUE, then corresponding sales $ would be returned. ‘+ When both of the first two criteria are being met, and the third criteria is being met, this first argument returns (1+1)*1=2, which is stil TRUE, then corresponding sales $ would be returned. When both of the first two criteria are not being met, and the third criteria is being met, this frst argument returns (0+0)*1=0, which is still FALSE, then FALSE would be returned. When the third criteria is not being met, regardless if first two criteria are being met or not, this first argument returns (#+#)*0=0, which is FALSE, then FALSE would be returned, ‘© This IF array formula essentially returns to you an array that is equivalent to range B19:D26. "AB"))>0)*(P18:R18>=2)*P19:R26). 26="B" }+(019:02 ‘The SUM function is to sum an array. The array is produced by array operations, where the first criteria plus the second criteria and then evaluate if that addition result is greater than 0; then use the addition result to multiply the third criteria, and finally multiplies the actual sales $. ‘+ When any one of the two criteria is being met, the addition array operation is essentially doing (1+0) or (0+4), which will return 1; when we further evaluate this result (1) is greater than O, it does return 1 (TRUE); © When the third criteria is being met (which returns 1), the >=0 comparison result 1 multiplies the third criteria result 1 then multiplies the sales S, itreturns the sales 6 ‘© When the third criteria is not being met (which returns 0), the >=O comparison result 1 multiplies the third criteria result 0 then multiplies the sales §, it returns 0. * when none of the two criteria is being met, the addition array operation is essentially doing (0+0), which returns 0; when we further evaluate this result (0) is greater than O, it does return 0 (FALSE); (© Regardless the third criteria is being met or not (1 or 0), the >=0 comparison result O multiplies the third criteria result 1/0 then multiplies the sales $, it returns 0. 19@ Super Excel Revolutionize the Way You Learn Excel Become an Excel Superhero SUPER EXCEL INC. 77 CITY CENTRE DRIVE, SUITE 5, EAST TOWER, MISSISSAUGA ON LSB 1MS TEL: (667) 654-5835 WWW.SUPEREXCEL.CA © when both criteria are being met, the addition array operation is essentially doing the (1+1), which returns 2; when we further evaluate this result (2) is greater than O, it does return 1 (TRUE); © When the third criteria is being met (which returns 1), the >=0 comparison result 1 multiplies the third criteria result 1 then multiplies the sales , it returns the sales $. © When the third criteria is not being met (which returns 0), the >=0 comparison result 1 ‘multiplies the third criteria result 0 then multiplies the sales , it returns 0. © These array calculations essentially return to you an array that is equivalent to range F19:H26, so the SUM function sums only the four celis sales $ that (either their Region are B or Category are AB) and Week Number is greater than or equal to 2. le #6 Change Formulas in Batch In the Jan, Feb, and Mar worksheets, each contains the sales $ for same item numbers for the respective month. In the Jan Formula worksheet, there are five formulas in cell E12, E12, E13, £14, E15 respectively calculating the SUM, AVERAGE, COUNT, MAX, and MIN of the 10 records in Jan worksheet. ‘A quick way to view the formutasin all cells is to hold on Ctrl and press Grave (") key, which is the key below Esc and left to number 1. You will see the formula in the cells instead of the resulting value, You can also accomplish this by going to Formulas tab, click on Show Formulas button in the Formula Auditing group. Click this button again or Ctrl + * again will change them back to show formula resulting values. Now, you need to do the same five formulas in Feb Formula worksheet based on the 10 records in Feb worksheet, ‘The formulas are very similar. The 10 records are in the same cell range between the two worksheets, the only difference is the worksheet. One is Jan and another is Feb. You do not need to re-type the five formulas individually, or SO, 500 formulas in your real life, They can be changed in a batch quickly. Jf you simply copy the formulas in range E11:€45 of the Jan Formula worksheet, and then paste to range C13:C17 of Feb Formula worksheet, the referred worksheet name will stay as Jan, the cell references will change accordingly too since they are relative references. Here are the steps for change formulas in a batch. 1, Change original formulas to text a. Select the range E11:€15 of the Jan Formula worksheet Hold on Ctrl, then press H, which brings up Find and Replace dialog box, and active tab is on Replace tab. ‘Type equal sign “=” in the Find what box, type the exponent caret sign "” in the Replace with box Hold on Alt, then press A (or click on Replace All button). Message box pop up to advise you five replacements being made. Click OK and close dialog box i, Now the equal sign “=” in all five cells are replaced with exponent caret sign “A”. They are not formulas anymore as they do not have equal sign in the front; they are simply a whole bunch of text, 20Super Revolutionize the Way You Learn Excel e " Excel Become an Excel Superhero SUPER EXCEL INC. 77 CITY CENTRE DRIVE, SUITE 501, EAST TOWER, MISSISSAUGA ON LSB 1MS TEL: (67) 654-5835 WWW.SUPEREXCEI.CA 2. Copy the five cells which contains pure text, and paste them to the five yellow cells in Feb Formula worksheet 3. The five exact texts are pasted in Feb Formula worksheet, nothing changed as they are not formulas and pure text, 4, Changed pasted text back to formulas Select the five yellow cells in Feb Formula worksheet, Ctri+H to bring up Find and Replace dialog box 1 b. Type exponent caret sign “A” in the Find what box, type the equal sign “=” in the Replace with box ¢. Hold on Alt, then press A (or click on Replace All button). d, Message box pop up to advise you five replacements being made. Click OK and close dialog box Now the exponent caret sign “” in all five cells are replaced with equal sign “=”. They are back as formulas, and they are exactly the same five formulas as in Jan Formula worksheet based on Jan worksheet 10 records. Change Jan to Feb in the new formulas a, Select the five yellow cells again, Ctri+H to bring up Find and Replace dialog box b, Type Jan in the Find what box, type Feb in the Replace with box Hold on Alt, then press A (or click on Replace All button). 4d, Message box pop up to advise you five replacements being made. Click OK and close dialog box | i. Now all the text Jan in the five formula cells are replaced with Feb : il. Your formulas here are calculating the same 10 records in Feb worksheet 6. Change original formulas from text back to formula Select the five yellow cells (which are text now) in Jan Formula worksheet, Ctri+H to bring up Find and Replace dialog box b. Type exponent caret sign “A” in the Find what box, type the equal sign “=” in the Replace with box Hold on Alt, then press A (or click on Replace All button). d. Message box pop up to advise you five replacements being made. Click OK and close dialog box i. Now the exponent caret sign “” in all five cells are replaced with equal sign "=". They are back a formulas, and they are exactly the same as the original five formulas. You do not have to use the exponent character caret "*", you can use any other character that you believe that does exist already in your formulas, otherwise, it may change other things you may intend to change. This method can be applied in many different scenarios. You can change the worksheet names, you can change the cell ranges, but you need to be very careful When you use Ctrl+H method to change the formula © Youneed to select the range of cells you want to change first © Ifyou do not select the range of cells, the Ctri+H will make replacements within the whole worksheet ‘© If you only want to change one cell formula (which could be super long, and the text you are trying to replace may appear 10 or more times longer than this one formula), you need to select this one cell and another adjacent blank cell to perform Ctri+H ‘©. IF you leave your cursor in this one single cell and perform Ctrl+H, Excel does not consider you selected one cell; instead it consider no range is selected, it wll perform replacement within the whole . worksheet 2@ Super Excel Revolutionize the Way You Learn Excel Become an Excel Superhero ‘SUPER EXCEL INC. 77 CITY CENTRE DRIVE, SUITE S01, EAST TOWER, MISSISSAUGA ON LSB 1MS TEL: (647) 654-5835 WWW.SUPEREXCEL,CA * You need to pay attention to the message box after the replacement, so you know how many replacements have been made. If it makes more replacements than you anticipated, that means: © Itprobably makes replacements somewhere else where you did not intend to change. You should undo with Ctrl42 to revert them back to previous stage. ‘0 Maybe your estimated number of occurrence of that text within the formulas is wrong, If you are absolutely certain, you can accept the replacement and proceed; if you are not, it’s better to undo and Use Ctrl+F to find where and how many times the text you are trying to replace has appeared in the range you have selected You need to be extremely cautious if you are replacing small length of characters. Say, you use Ctrl+H to find “c” and replace with “d”, then there will be lots of “c” will be replaced even though you do not intend to. COUNT function will be changed to dOUNT besides all the C5:C14 changed to D5:024. ‘Ifa worksheet name contains space, then the reference address in that worksheet will have that worksheet, 14 to name enclosed with a pair of single quotes. So if you are changing the formula reference from JanICé ‘Feb Data’!C5:C14, you need to find Jan and replace with ‘Feb Data’ (with single quotes). File #7 Understanding SUMPRODUCT function SUMPRODUCT worksheet Here you have the prices and sales units for each of the 10 products in each of the four months, you need to calculate the total sales $, By now, you already knew you can do a simple array formula to get the result instead of doing formulas in 42 cells. In cell 424, enter the array formula =SUM(B12:E21*G12:121), it returns you the total sales $ of $265,587. This array formula is equivalent to sum the range 112:021, The range L12:021 would be the result array of B12:E21*G12:121. We understood from previous files, with array operation of B12:€21*G12:121, it takes every cell in first array multiplies the corresponding cell in the second array. The array formula =SUM(812:E21*G12:121) can be accomplished in a different way ~ the SUMPRODUCT function. The ‘SUMPRODUCT function is a regular function that does array operation. You enter this function just by press Enter. No need to do Ctrl+ShifteEnter. SUMPRODUCT(array1, [array2], [array3], ...) Multiplies corresponding components in the given arrays, and returns the sum of those products. Arrayl Required. The first array argument whose components you want to multiply and then add. Array2, array3,... Optional. Array arguments 2 to 255 whose components you want to multiply and then add. ‘The array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value. 2Become an Excel Superhero SUPER EXCEL INC. 77 CITY CENTRE DRIVE, SUITE 501, EAST TOWER, MISSISSAUGA ON LSB 1MS TEL: (647) 654-5825 WWW. SUPEREXCEL.CA SUMPRODUCT treats array entries that are not numeric as if they were zeros. @ Super Excel Revolutionize the Way You Learn Excel With the SUMPRODUCT function, it takes every single cell in first array multiplies the corresponding cell in the second array, and then sum up all the multiplication results. PRODUCT here means multiplication of all corresponding cells, and ‘SUM means to sum all those PRODUCTs. So the array formula =SUM(812:E21*612:J21) can be done with regular formula in cell 126 as | UMPRODUCT(B12:E21,G12:121), and you do not need to do CtrlsShift+Enter, In SUMPRODUCT function, only first argument is required, the 2” to the 255" argument are optional and can be omitted. If there is only one argument (array 1) in the SUMPRODUCT function, that means there is nothing to multiply the array 1 with. The SUMPRODUCT function does not multiply array 1 with anything and essentially only sums the array 1. Now enter the formula =SUMPRODUCT(L12:021) in cell 128, Since there is only one argument, which is array L12:024, it simply sums the values in all the cells within range L12:021, which totals at 265,587. This means SUMPRODUCT function can sum an array if you put in only first argument array within the function. The first argument array can be an array reference (e.g. L12:021) you select from the worksheet, it can also be a resultant array from one or more array operations. e.g. A1:A10*B1*B10, A1:C5*0.13, B12:E21"G12:121 etc... Now enter the formula =SUMPRODUCT(B12:E21*G12:J24) in cell 130. There is only one argument in this function as there is no comma used to separate the different arguments. Here the only argument is actually an array operation, taking array B12:€21 multiplies array G12:s21, which produces a resultant array that is equivalent to range L42:021. The SUMPRODUCT function simply sums this resultant array which isin the memory and not displayed in any cell The regular formula =SUMPRODUCT(812:€21*612:121) in cell 30 does exactly the same thing as array formula ‘=SUM(B12:E21"G12:121) in cell 424. The good thing is SUMPRODUCT function can handle array directly, and you do not need to do Ctrl+Shift+Enter. If you create a template with array formulas for other people to use, and if they do not understand the array formula, and not knowing about Ctrl#Shift+Enter, then they will ikely encounter problem after editing an array formula and simply press Enter. Use SUMPRODUCT instead of SUM for array formuta SUMPRODUCT 20 Array worksheet The case in this worksheet is exactly the same as previous file 2D Array worksheet. Last time we did SUM array formula to handle that, now understanding the regular formula SUMPRODUCT can sum an array without Ctrl+Shift+Enter, you can get the same thing done with just SUMPRODUCT. In cell R14 before you entered SUM array formula =SUM((N19:N26="B")*(019:026="AB")*(P18:R18>=2)*P19:R26) to calculate the result. Now, you can enter the regular SUMPRODUCT formula =SUMPRODUCT((N19:N26="B")*(019:026="AB")*(P18:R18>=2)*P19:R26) in cell R13 to get the same result. 23@ Super Excel Revolutionize the Way You Learn Excel Become an Excel Superhero ‘SUPER EXCEL INC. 77 CITY CENTRE DRIVE, SUITE 501, EAST TOWER, MISSISSAUGA ON LSB 1M5 TEL: (647) 654-5835 WWW.SUPEREXCEL.CA In cell R35 before you entered SUM array formula =SUM((((N19:N26="B")+(019:024 to calculate the result. Now, you can enter the regular SUMPRODUCT formula |UMPRODUCT {((N19:N26="B"}+(019:026="AB")}>0)*{P18:R18>=2)*P19:R26) in cell R34 to get the same result. "AB"))>0)*(P18:R18>=2)*P19:R26) Here, you will notice, within the SUM and SUMPRODUCT function, there is only one argument, which are quite a few array operations, and they are exactly the same between the two functions. if you do not remember the logics on those array operations, please refer to the notes under SUM With AND and OR Criteria in Two Dimensions section @Bonus SUMPRODUCT 4 Criteria Worksheet Array formulas in this worksheet are exactly the same concepts to array formulas in SUMPRODUCT 20 Array worksheet. Here we just have more realistic data set which is larger than data in SUMPRODUCT 2D Array worksheet, where we used very simple and small data set to demonstrate the concepts. File #8 Summary Reporting Model Introduction Here you need to produce a report every month to shows the sales $ for different region and product family for three different time frames: a single month, Year-To-Date, Rolling 12 Months. For each time frame, you are always comparing the actual sales $ against the plan sales $, and against last year the same time frame. You are given plan sales $ by region by product category (product family information also provided) for the whole year already at beginning of this year. Every month you will query your company data warehouse system to obtain the sales $ history by region by product category. Product family information is also available from this query. You need to produce multiple reports and some of them may require product category level detail. For this particular report in this exercise only product family level details are required. Essentially, you need to sum certain records sales $ based on three types of criteria: for a particular region, a particular product family, within a particular range of months, but this will not be done by SUMIFS function directly. SUMIFS function requires the sum range and evaluation ranges to be the same size. Here the region and product family reside in one column each, but month data resides in a row, and the sales $ sum range resides in a two dimensional table, they are definitely not the same size. Without array formula, you may have to create many supporting columns to perform lots of intermediate steps, before you can finally use SUMIFS functions to calculate final results. Now, armed with the array formula knowledge you have, you do not need to have those intermediate steps, as they will be done by the array formulas within the memory, and directly calculated to get final results Fort ‘the Month, Year-To-Date, and Rolling 12 Months First you need to specify what the month range will be for each of the three times frames as each month goes by. 24Become an Excel Superhero SUPER EXCEL INC, 77 CITY CENTRE DRIVE, SUITE 503, EAST TOWER, MISSISSAUGA ON LSB IMS TEL: (647) 654-5635 WWW. SUPEREXCEL.CA 1n the Variable worksheet, you will enter the reporting month in cell G1 every month, you wish there will be formulas to calculate automatically what the month range will be for both TY and LY and for single Month, Year-To-Date, and Rolling 12 Months. @ Super Excel Revolutionize the Way You Learn Excel In this particular case, this company always use the first day of the month to represent the month. So 9/1/2022 represent that reporting month is September. In the actual tab and plan tab, the month numbers are all dates being the first day of the months. You need to calculate the begin date and end date for the three ranges for both TY and LY based on the reporting month in cell G2. To calculate dates, you need to understand few date related functions, which are the prerequisite for this course. in case you do not know them, here below are the descriptions of the DATE, YEAR, MONTH, DAY functions. DATE(year,month,day) ‘The DATE function returns the sequential serial number that represents a particular date. For example, the formula =DATE(2008,7,8) returns 39637, the serial number that represents 7/8/2008. Year Required. The value of the year argument can include one to four digits. Month Required. A positive or negative integer representing the month of the year from 1 to 12 (January to December). f month is greater than 12, month adds that number of months to the first month in the year specified. For example, DATE(2008,24,2) returns the serial number representing February 2, 2009. |f month is less than 1, month subtracts the magnitude of that number of months, plus 1, from the first month in the year specified. For example, DATE(2008,-3,2) returns the serial number representing September 2, 2007. Day Required. A positive or negative integer representing the day of the month from 1 to 31. If day is greater than the number of days in the month specified, day adds that number of days to the first day in the month. For example, DATE(2008, 1,35) returns the serial number representing February 4, 2008. If day is less than 1, day subtracts the magnitude that number of days, plus one, from the first day of the month specified. For example, DATE(2008,1,-15) returns the serial number representing December 16, 2007. Excel stores dates as sequential serial numbers so that they can be used in calculations, January 1, 1900 is serial ‘number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900, YEAR(serial_number) Returns the year corresponding to a date. Serial_number Required. The date of the year you want to find, MONTH(serial_number) 25Super Revolutionize the Way You Learn Excel e “Excel Become an Excel Superhero ‘SUPER EXCEL INC. 77 CITY CENTRE DRIVE, SUITE 501, EAST TOWER, MISSISSAUGA ON LSB 1MS TEL: (647) 654-5835 WWW SUPEREXCEL.CA Returns the month of a date represented by a serial number. The month is given as an integer, ranging from 1 (anuary) to 12 (December). Serial_number Required. The date of the month you are trying to find. DAY(serial_number) Returns the day of a date, represented by a serial number. The day is given as an integer ranging from 1 to 31. Serial_number Required. The date of the day you are trying to find, For a single month in TY, it begins in the reporting month date, and ends in the reporting month date, so the formula in cell €7 and F? will just be =G1. For a single month in Ly, it begins in one month, and ends in the same month, but this month will be one year earlier than the reporting month cell in Ga. So you need to create a new date for that, which can be accomplished with DATE function as long as you know what the year, month, and day numbers will be for that date. ‘This date will have the same month and day as reporting month date G1, but year number will be 1 year earlier. So the formula in cell E8 and F8 will be =DATE(YEAR(G1)-1, MONTH(G1),DAY(G1)), where you use MONTH function to obtain the month number from reporting month date G2, and use DAY function to obtain the day number from reporting month date G1; however for the year number, you will use YEAR function to obtain the year number from reporting month date G1, then subtract 1 from it, to get 1 year earlier year number. Having all the three components, the DATE function will give you the correct date, which is last year the same month and day as reporting month date. For the LY YTD begin date, it is always the first day of the first month of previous year from the reporting month date. ‘The formula in cell E14 is =DATE(YEAR(G1)-1,1,1), where you set the month and day numbers as hardcoded 4 and 1, and only use YEAR function to obtain the year number of the reporting month date and subtract 1 from it, to get pervious year year number; and the DATE function create that date, which is always January 1 of the year that is one year earlier than reporting month date year number. For the LY Rolling 12 Months begin date, if the reporting month date is 9/1/2022, then it should begin on 10/1/2021; if the reporting month date is 2/1/2022, then it should begin on 3/1/2022. The pattern is that the year number will be 1 year earlier than reporting month date year number, the month number will be 1 month later than the reporting month date month number, and the day number will be always 1. So the formula in cell E14 is =DATE(YEAR(G1)- 2,MONTH{(G1}+1,DAY(G1)), where you use YEAR function to get reporting month date year number and subtract 1 from it; use MONTH function to get reporting month number and add 1 to it; use DAY function to get the same date as reporting month date day, Finally the DATE function will create the date based on 1 year earlier and 1 month later from the reporting month date, and same day as reporting month date, Having all those formulas in the six yellow highlighted cells, now when you change the reporting month date in cell 61, ‘you will gt the right begin date and end date for any of the month ranges. Create names for the dynai However itis very inconvenient when you do the formulas in the report worksheet, if you have to switch back and forth to come to the Variables worksheet to select the month range begin and end cells. Defining those cells under range names will greatly ease your formula creation and future testing and debugging of the model, 26