VIEWS: 59 PAGES: 81 POSTED ON: 12/1/2011
Here is a list of what a cell can contain: TEXT, NUMBER, TRUE or FALSE, Blank cell, FORMULA, Blank from formula, erro TRUE FALSE logic formulas in Excel like: =B23=C23. This formula asks the question is the content of B23 the same as C23? T because 15 <> 15.1. Case in words is not considered. For example Case = case is TRUE to Excel. Comparative Operators: =, <>, >, >=, <, <= AND function (2 up to 255 logical tests that all must be TRUE for the AND function to deliver a TRUE to the cell or OR function (2 up to 255 logical tests that if at least one is TRUE then the OR function delivers a TRUE to the cell o NOT function converts TRUE to FALSE and FALSE to TRUE IS functions deliver a TRUE or FALSE to a cell or formula. These are some IS functions: ISTEXT function, ISNUMBER function, ISBLANK function, ISERROR function, ISNONTEXT function IF function can put one of two things in a cell. The IF function has three parts: 1) Logical Test, 2) Value If True, 3) Va IFERROR function looks to see if there is an error from a formula. If there is an error it puts something in the cell, otherwise calculate. Logic, TRUE FALSE formulas, IS & IF functions List of what can go in a cell TEXT rad NUMBER 4.23 TRUE TRUE FALSE FALSE Error from formula #DIV/0! Logical Comparative Logical Question No. No. Formula Operator Is 15=15.1? 15.0 15.1 FALSE = Is 15<>15.1? 15.0 15.1 TRUE <> Is 15>15? 15.0 15.0 FALSE > Is 15>=15? 15.0 15.0 TRUE >= Is 15<15? 15.0 15.0 FALSE < Is 15<=15? 15.0 15.0 TRUE <= Customer Credit Score 3 Customer Last Year Sales $ 500,000.00 Company Credit Score Hurdle 3 Company Last Year Sales Hurdle $ 1,000,000.00 Rule 1: We have two logical tests and both must be met before we extend credit: AND function FALSE AND Rule 2: We have two logical tests and "one or the other or both" (also said in this way; 'at least one') can be met before we extend credit: OR function TRUE OR ISTEXT function rad TRUE TRUE Value refers to text. ISTEXT function 23 FALSE FALSE ISNUMBER function 12 TRUE TRUE Value refers to a number. ISNUMBER function Rad FALSE FALSE ISLOGICAL function TRUE TRUE TRUE Value refers to a logical value. ISBLANK function TRUE TRUE Value refers to an empty cell. ISERROR function #REF! TRUE TRUE Value refers to any error value (#N/A, #VALUE! ISERR function #N/A FALSE FALSE Value refers to any error value except #N/A. ISNA function #N/A TRUE TRUE Value refers to the #N/A (value not available) e ISNONTEXT function Excel FALSE FALSE Value refers to any item that is not text. (Note IF function can put one of two things in a cell. The IF function has three parts: 1) Logical Test 2) Value If True 3) Value If False. For the 'Value If True' and 'Value If False', you can put numbers, text (must be in double quotes), formulas, or cell ranges This example puts one of two words (text) into a cell Text or Number Text or Number Formula Big List Formula 1 2 word1 Text Text 12 Number Number word3 Text Text word4 Text Text 5896 Number Number word6 Text Text word7 Text Text This example puts one of two numbers into a cell Your Sales For Month $6,000.00 Do you get a bonus? You get Bonus if you sell $6,000.00 or more Bonus Hurdle $6,000.00 Bonus Amount $200.00 Your Bonus = $200.00 Put 1 of 2 Formulas in a cell Revenues $6.00 Expenses $6,000.00 Net Loss $5,994.00 Conditional Formatting (Home Ribbon in 2007, Format menu in 2003) uses Logical Formulas k cell, FORMULA, Blank from formula, error from formula n is the content of B23 the same as C23? The answer is FALSE example Case = case is TRUE to Excel. >, >=, <, <= ND function to deliver a TRUE to the cell or formula) he OR function delivers a TRUE to the cell or formula) and FALSE to TRUE ons: ISTEXT function, ISNUMBER function, ISLOGICAL function, SNONTEXT function parts: 1) Logical Test, 2) Value If True, 3) Value If False rror it puts something in the cell, otherwise it lets the formula Comparative Operator Equal Not Equal Great Than Greater Than Or Equal To Less Than Less Than Or Equal To ue refers to text. ue refers to a number. ue refers to a logical value. ue refers to an empty cell. ue refers to any error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!). ue refers to any error value except #N/A. ue refers to the #N/A (value not available) error value. ue refers to any item that is not text. (Note that this function returns TRUE if value refers to a blank cell.) DIV/0!, #NUM!, #NAME?, or #NULL!). unction returns TRUE if value refers to a blank cell.) Account Not Suspended, No Overdue Balance and Rule 1 Last Year's Sales > $50,000.00 Account Not Suspended and Both Credit Hurdles must be met and at least 1 of the remaining criteria Rule 2 must be met Formula Inputs Custome Accou Rating1 Rating2 Asset Last Over r Name nt Hurdle Hurdle Value Year's Due Yes 2.5 4.5 200,000 50,000 Yes Customer Credit Analysis For Accounts Receivable Accou Over Rule 1 nt Last Due Account Not Suspended, No Custome Suspe Asset Year's Balan Overdue Balance and Last r Name nded? Rating1 Rating2 Value Sales ce Year's Sales > $50,000.00 Cust01 5 7.4 208,339 90,550 Cust02 5 5.9 374,775 51,255 Yes Cust03 3.3 3.9 371,040 56,241 Cust04 Yes 1.2 2.4 331,439 69,920 Cust05 Yes 3.4 5.3 336,505 58,534 Yes Cust06 4.5 6 336,794 60,423 Yes Cust07 4.4 2.4 500,000 49,850 Cust08 2.9 5.5 375,800 62,100 Cust09 2.5 8.9 254,888 75,000 r Accounts Receivable Rule 2 Account Not Suspended and Both Credit Hurdles must be ONLY: Two met and at least 1 of the rules are false, remaining criteria must be NONE but Asset met Fail Both Rules Hurdle Met Decision Account Not Suspended, No Overdue Balance and Rule 1 Last Year's Sales > $50,000.00 Account Not Suspended and Both Credit Hurdles must be met and at least 1 of the remaining criteria Rule 2 must be met Formula Inputs Accou Over nt Last Due Custome Suspe Rating1 Rating2 Asset Year's Balan r Name nded? Hurdle Hurdle Value Sales ce Yes 2.5 4.5 200,000 50,000 Yes Customer Credit Analysis For Accounts Receivable Accou Over Rule 1 nt Last Due Account Not Suspended, No Custome Suspe Rating1 Rating2 Asset Year's Balan Overdue Balance and Last r Name nded? Hurdle Hurdle Value Sales ce Year's Sales > $50,000.00 Cust01 5 7.4 208,339 90,550 TRUE Cust02 5 5.9 374,775 51,255 Yes FALSE Cust03 3.3 3.9 371,040 56,241 TRUE Cust04 Yes 1.2 2.4 331,439 69,920 FALSE Cust05 Yes 3.4 5.3 336,505 58,534 Yes FALSE Cust06 4.5 6 336,794 60,423 Yes FALSE Cust07 4.4 2.4 500,000 49,850 FALSE Cust08 2.9 5.5 375,800 62,100 TRUE Cust09 2.5 8.9 254,888 75,000 TRUE r Accounts Receivable Rule 2 Account Not Suspended and Both Credit Hurdles must be ONLY: Two met and at least 1 of the rules are false, remaining criteria must be None but Asset met Fail Both Rules Hurdle Met Decision TRUE FALSE FALSE Approved TRUE FALSE FALSE Approved FALSE FALSE FALSE Take A Second Look FALSE TRUE TRUE Reject FALSE TRUE TRUE Reject TRUE FALSE FALSE Approved FALSE TRUE TRUE Reject TRUE FALSE FALSE Approved TRUE FALSE FALSE Approved Is Account Credit Credit Past Year's Suspended? Customer Rating 01 Rating 02 Asset Value Sales Not Suspended Customer 01 4 9 $159,441 $332,082 Not Suspended Customer 02 4 6 $796,571 $455,968 Not Suspended Customer 03 2 3 $1,243,611 $373,892 Suspended Customer 04 3 3 $4,939,327 $285,446 Not Suspended Customer 05 2 7 $2,647,055 $191,068 Not Suspended Customer 06 2 2 $1,409,633 $168,029 Not Suspended Customer 07 3 8 $4,350,814 $353,054 Not Suspended Customer 08 4 9 $2,395,694 $198,841 Not Suspended Customer 09 0 3 $1,629,030 $199,163 Not Suspended Customer 10 3 8 $207,371 $189,052 Suspended Customer 11 0 5 $4,857,701 $347,454 Not Suspended Customer 12 3 4 $1,968,563 $90,424 Not Suspended Customer 13 3 8 $3,183,148 $169,508 Not Suspended Customer 14 2 7 $3,898,837 $238,658 Not Suspended Customer 15 0 9 $4,643,757 $439,679 Assumptions Credit Criteria Credit Credit Credit 1 Criteria 2 Criteria 3 Credit Criteria 4 Criteria 5 Hurdle Hurdle Credit Credit Hurdle Current Over Rating 01 Rating 02 Asset Value Past Year's Sales Due Balance 2 4.5 $200,000 $50,000 0 Low 0 0 High 4 10 Rule 1 Rule 2 Not Suspended, No Both Credit Hurdles must Overdue Balance be met and at least 1 of NONE: 2 ONLY: Two rules are Current Over and Past sales > the remaining criteria rules not false, but Asset Due Balance $50,000.00 must be met met) Hurdle Met 0 1 1 1 0 0 1 0 1 1 1 1 1 0 1 Rule 1 Rule 2 Not Suspended, No Both Credit Hurdles must Overdue Balance be met and at least 1 of Is Account and Past sales > the remaining criteria Suspended? $50,000.00 must be met Suspended Not Suspended Is Account Credit Credit Past Year's Current Over Suspended? Customer Rating 01 Rating 02 Asset Value Sales Due Balance Not Suspended Customer 01 4 9 $159,441 $332,082 0 Not Suspended Customer 02 4 6 $796,571 $455,968 1 Not Suspended Customer 03 2 3 $1,243,611 $373,892 1 Suspended Customer 04 3 3 $4,939,327 $285,446 1 Not Suspended Customer 05 2 7 $2,647,055 $191,068 0 Not Suspended Customer 06 2 2 $1,409,633 $168,029 0 Not Suspended Customer 07 3 8 $4,350,814 $353,054 1 Not Suspended Customer 08 4 9 $2,395,694 $198,841 0 Not Suspended Customer 09 0 3 $1,629,030 $199,163 1 Not Suspended Customer 10 3 8 $207,371 $189,052 1 Suspended Customer 11 0 5 $4,857,701 $347,454 1 Not Suspended Customer 12 3 4 $1,968,563 $90,424 1 Not Suspended Customer 13 3 8 $3,183,148 $169,508 1 Not Suspended Customer 14 2 7 $3,898,837 $238,658 0 Not Suspended Customer 15 0 9 $4,643,757 $439,679 1 Assumptions Credit Criteria Credit Credit Credit Criteria Credit 1 Criteria 2 Criteria 3 4 Criteria 5 Hurdle Hurdle Credit Credit Hurdle Past Year's Current Over Is Account Rating 01 Rating 02 Asset Value Sales Due Balance Suspended? 2 4.5 $200,000 $50,000 0 Suspended Low 0 0 Not Suspended High 4 10 Rule 1 Rule 2 Not Suspended, No Both Credit Hurdles must Overdue Balance be met and at least 1 of NONE: 2 ONLY: Two rules are and Past sales > the remaining criteria rules not false, but Asset $50,000.00 must be met met) Hurdle Met TRUE TRUE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE TRUE TRUE FALSE FALSE TRUE TRUE TRUE TRUE FALSE FALSE TRUE FALSE FALSE FALSE FALSE TRUE FALSE FALSE TRUE TRUE FALSE FALSE FALSE FALSE TRUE TRUE FALSE TRUE FALSE FALSE FALSE FALSE TRUE TRUE FALSE FALSE TRUE TRUE FALSE TRUE FALSE FALSE TRUE TRUE FALSE FALSE FALSE FALSE TRUE TRUE Rule 1 Rule 2 Not Suspended, No Both Credit Hurdles must Overdue Balance be met and at least 1 of and Past sales > the remaining criteria $50,000.00 must be met Taxable Earnings Name Hours Wage Gross Beg Cumulative End Cumulative UnEm Employee 1 33 12.55 414.15 25,001.37 25,415.52 Employee 2 30 25.00 750.00 36,589.74 37,339.74 Employee 3 38 23.00 874.00 115,450.01 116,324.01 Employee 4 40 22.00 880.00 5,000.06 5,880.06 Employee 5 32 24.00 768.00 6,999.00 7,767.00 Employee 6 40 17.00 680.00 7,000.34 7,680.34 Employee 7 37 13.00 481.00 7,001.93 7,482.93 Employee 8 39 24.75 965.25 94,199.64 95,164.89 Employee 9 32 17.00 544.00 94,200.50 94,744.50 Employee 10 38 24.19 919.22 94,201.13 95,120.35 Employee 11 33 19.00 627.00 95,000.49 95,627.49 Employee 12 39 20.35 793.65 125,000.35 125,794.00 Employee 13 39 18.00 702.00 500.67 1,202.67 Employee 14 32 11.00 352.00 6,850.76 7,202.76 Employee 15 30 18.00 540.00 20,000.00 20,540.00 Employee 16 37 12.45 460.65 93,990.66 94,451.31 569 301.29 10,750.92 826,986.65 837,737.57 - Assumptions UnEm State UnEm Fed 5.40% 0.08% UnEm Fed Ceiling 7,000 105,000 73,900 67,000 62,000 57,000 55,000 5,000 Taxable Earnings Deductions SS Medicare SS Medicare Pension Total Ded. Net Pay Expense 414.15 - 6.01 8.28 14.29 399.86 414.15 750.00 - 10.88 15.00 25.88 724.12 750.00 874.00 - 12.67 17.48 30.15 843.85 874.00 880.00 - 12.76 17.60 30.36 849.64 880.00 768.00 - 11.14 15.36 26.50 741.50 768.00 680.00 - 9.86 13.60 23.46 656.54 680.00 481.00 - 6.97 9.62 16.59 464.41 481.00 965.25 - 14.00 19.31 33.31 931.94 965.25 544.00 - 7.89 10.88 18.77 525.23 544.00 919.22 - 13.33 18.38 31.71 887.51 919.22 627.00 - 9.09 12.54 21.63 605.37 627.00 793.65 - 11.51 15.87 27.38 766.27 793.65 702.00 - 10.18 14.04 24.22 677.78 702.00 352.00 - 5.10 7.04 12.14 339.86 352.00 540.00 - 7.83 10.80 18.63 521.37 540.00 460.65 - 6.68 9.21 15.89 444.76 460.65 - 10,750.92 - 155.90 215.01 370.91 10,380.01 10,750.92 Assumptions SS Medicare Pension 6.20% 1.45% 2% SS Ceiling 105,000 CURRENT Pay check 23100 9000 6,900 6900 5,000 5,000 2,000 50,000 5,000 Taxable Earnings Name Hours Wage Gross Beg Cumulative End Cumulative UnEm Employee 1 33 12.55 414.15 25,001.37 25,415.52 - Employee 2 30 25.00 750.00 36,589.74 37,339.74 - Employee 3 38 23.00 874.00 115,450.01 116,324.01 - Employee 4 40 22.00 880.00 5,000.06 5,880.06 880.00 Employee 5 32 24.00 768.00 6,999.00 7,767.00 1.00 Employee 6 40 17.00 680.00 7,000.34 7,680.34 - Employee 7 37 13.00 481.00 7,001.93 7,482.93 - Employee 8 39 24.75 965.25 94,199.64 95,164.89 - Employee 9 32 17.00 544.00 94,200.50 94,744.50 - Employee 10 38 24.19 919.22 94,201.13 95,120.35 - Employee 11 33 19.00 627.00 95,000.49 95,627.49 - Employee 12 39 20.35 793.65 125,000.35 125,794.00 - Employee 13 39 18.00 702.00 500.67 1,202.67 702.00 Employee 14 32 11.00 352.00 6,850.76 7,202.76 149.24 Employee 15 30 18.00 540.00 20,000.00 20,540.00 - Employee 16 37 12.45 460.65 93,990.66 94,451.31 - 569 301.29 10,750.92 826,986.65 837,737.57 1,732.24 Assumptions UnEm State UnEm Fed 5.40% 0.08% UnEm Fed Ceiling 7,000 106,000 1,000 105,000 82,900 22,100 73,900 23,100 67,000 62,000 57,000 55,000 5,000 Taxable Earnings Deductions SS Medicare SS Medicare Pension Total Ded. Net Pay Expense 414.15 414.15 25.68 6.01 8.28 39.97 374.18 414.15 750.00 750.00 46.50 10.88 15.00 72.38 677.62 750.00 - 874.00 - 12.67 17.48 30.15 843.85 874.00 880.00 880.00 54.56 12.76 17.60 84.92 795.08 880.00 768.00 768.00 47.62 11.14 15.36 74.12 693.88 768.00 680.00 680.00 42.16 9.86 13.60 65.62 614.38 680.00 481.00 481.00 29.82 6.97 9.62 46.41 434.59 481.00 965.25 965.25 59.85 14.00 19.31 93.16 872.09 965.25 544.00 544.00 33.73 7.89 10.88 52.50 491.50 544.00 919.22 919.22 56.99 13.33 18.38 88.70 830.52 919.22 627.00 627.00 38.87 9.09 12.54 60.50 566.50 627.00 - 793.65 - 11.51 15.87 27.38 766.27 793.65 702.00 702.00 43.52 10.18 14.04 67.74 634.26 702.00 352.00 352.00 21.82 5.10 7.04 33.96 318.04 352.00 540.00 540.00 33.48 7.83 10.80 52.11 487.89 540.00 460.65 460.65 28.56 6.68 9.21 44.45 416.20 460.65 9,083.27 10,750.92 563.16 155.90 215.01 934.07 9,816.85 10,750.92 Assumptions SS Medicare Pension 6.20% 1.45% 2% SS Ceiling 105,000 CURRENT Pay check 23,100 23100 9,000 9000 6,900 6900 5,000 5,000 2,000 50,000 5,000 Put 1 of 3 Percentages in a Formula Mutually Exclusive Categories Sales % Your Sales $6,000.00 0 >= Your Sales < 5000 $0 0.00% Your Bonus 5000 >= Your Sales < 10000 $5,000 2.00% Your Sales > = 10000 $10,000 4.00% Multiple IF Rule # 1: Be sure to start at top and go to bottom, or at the bottom and go to the top Multiple IF Rule # 2: If there are 3 possibilities, there are 2 IFs, If there are 4 possibilities, there are 3 IFs, etc. VLOOKUP to Replace IF Mutually Exclusive Categories Sales % Your Sales $6,000.00 0 >= Your Sales < 5000 $0 0.00% Your Bonus 5000 >= Your Sales < 10000 $5,000 2.00% Your Sales > = 10000 $10,000 4.00% Put 1 of 3 Percentages in a Formula Mutually Exclusive Categories Sales % Your Sales $6,000.00 0 >= Your Sales < 5000 $0 0.00% Your Bonus $120.00 5000 >= Your Sales < 10000 $5,000 2.00% Your Sales > = 10000 $10,000 4.00% Multiple IF Rule # 1: Be sure to start at top and go to bottom, or at the bottom and go to the top Multiple IF Rule # 2: If there are 3 possibilities, there are 2 IFs, If there are 4 possibilities, there are 3 IFs, etc. VLOOKUP to Replace IF Mutually Exclusive Categories Sales % Your Sales $6,000.00 0 >= Your Sales < 5000 $0 0.00% Your Bonus 120 5000 >= Your Sales < 10000 $5,000 2.00% Your Sales > = 10000 $10,000 4.00% 1) Range means a group of cells such as B12:E12 2) The Range B12:E12 can contain the values: 5, 7, 9, 11 An array (different than a range) means a group of things (values, text, 3) TRUE/FALSE) stored in a range of cells or in an array using array syntax Array syntax uses the: 1) curly bracket to contain the array, 2) comma 4) for column and 3) semi-colon for row, 4) Text is in quotes 5) An array could be: {5, 7, 9, 11} If the values did not need to be in cells, instead of =SUM(B12:E12), you 6) could use the formula =SUM({5, 7, 9, 11}) To create an array from a range of values, click in a cell, type the equal sign, highlight a range of cells, then highlight the range of cells in the formula, hit the F9 key, then Ctrl + C to copy the array with all the array 7) syntax. 8) An array could be: {''Week1'',''Week2'',''Week3'',''Week4'';5,7,9,11} Instead of the formula, =VLOOKUP(B20,$B$15:$C$17,2,0), you could use this (then the data is hard coded into formula), =VLOOKUP(B20,{""Part1"",25;""Part2"",10;""Part3"",5},2,0). This formula is not an Array Formula (because it does not use Ctrl + Shift + 9) Enter to put formula in cell), but it is a formula that uses an array. Week1 Week2 Week3 Week4 Total Total 5 7 9 11 Item Price Part1 $25.00 Part2 $10.00 Part3 $5.00 Product Price Part2 Different than regular formulas, array formulas can: 1) Do operations on arrays instead of individual cells 2) Allow you to enter functions such as TRANSPOSE, FREQUENCY and MMULT, which are by definition array formulas. 3) Allow ranges or arrays in function arguments where the function is expecting a single value Array Formulas can do operations on arrays such as 10) =SUM(B35:B38*C35:C38) In order to get Excel to recognize that your formula is an array formula, you must use the key strokes Ctrl + Shift + Enter to put the formula in 11) the cell or range of cells. When you use Ctrl + Shift + Enter to put an array formula into a cell or 12) range of cells Curley Brackets appear automatically around the formula. If you enter =SUM(B35:B38*C35:C38), {=SUM(B35:B38*C35:C38)} will 13) appear Array formulas can save spreadsheet real-estate. For example, instead of using an extra column to calculate sales for each transaction and then adding the total for each transaction, you can simply use an array 14) formula in one cell. In cell D40 create this formula: =SUM(B35:B38*C35:C38), then use Ctrl + Shift + Enter to create the Array Formula. The result will look like {=SUM(B35:B38*C35:C38)}. The curley brackets will be put in automatically. Units Price Sales 5 $22.00 What are we doing: multiply, then add 2 $23.00 Array formula advantage: use less space 4 $25.00 Array formula disadvantage: must use Ctrl + Shift + Enter and larger array formul 6 $24.50 Total $403.00 Total Array formula in 1 cell Total SUMPRODUCT multiplies the related ranges and then adds. SUMPRODUCT can b To Enter Array Formula you must use keystrokes: Ctrl + Shift + Enter In cell D53 create this formula: =MIN(C46:C51-B46:B51), then use Ctrl + Shift + Enter to create the Array Formula. The result will look like {=MIN(C46:C51- B46:B51)}. The curley brackets will be put in automatically. Sample Sample Weight Beg Weight End Change 1 10.00 lbs 11.20 lbs What are we doing: subtracting then find MIN 2 12.50 lbs 13.00 lbs Array formula advantage: use less space 3 9.00 lbs 10.00 lbs Array formula disadvantage: must use Ctrl + Shift + Enter and larger array formul 4 11.50 lbs 12.00 lbs 5 10.00 lbs 10.25 lbs 6 14.00 lbs 14.75 lbs Array formula in 1 cell To Enter Array Formula you must use keystrokes: Ctrl + Shift + Enter In cell D65 create this formula: =MAX(C58:C63-B58:B63), then use Ctrl + Shift + Enter to create the Array Formula. The result will look like {=MAX(C58:C63- B58:B63)}. The curley brackets will be put in automatically. Stock $ End Stock $ Beg Change 1 26 27 What are we doing: subtracting then find MAX 2 28 19 Array formula advantage: use less space 3 13 15 Array formula disadvantage: must use Ctrl + Shift + Enter and larger array formul 4 27 15 5 17 29 6 28 32.5 Array formula in 1 cell To Enter Array Formula you must use keystrokes: Ctrl + Shift + Enter Weight in Portfolio 0.333333 0.333333333 0.33333333 Economic Probability Stock1 Stock2 Stock3 Expected State of State Return Return Return Returns Weak 0.4 0.1 0.15 0.2 Boom 0.6 0.08 0.04 0 Array formula in 1 cell Don't forget Ctrl + Shift + Enter Weight in Portfolio 0.25 0.75 Economic Probability Stock1 Stock2 Expected State of State Return Return Returns Weak 0.55 -0.02 0.05 Normal 0.3 0.059 0.065 Boom 0.15 0.12 0.085 Array formula in 1 cell Don't forget Ctrl + Shift Does not require Ctrl + Shift + Enter Example of SUMPRODUCT formula that will not work: #VALUE! For SUMPRODUCT, you must use multiplication If you want to multiply ranges and add, instead of the SUM function with the keystrokes Ctrl + Shift = Enter, you can use the SUMPRODUCTS function. This is not an array formula because we do not use Ctrl + Shift + Enter. SUMPRODUCT can handle arrays without Ctrl + Shift + Enter. If the ranges used in SUMPRODUCT are the same size, use ranges and commas. If the ranges used in SUMPRODUCT are NOT the same size, 15) use ranges and the multiplication symbol. 16) See this video for more about SUMPRODUCT: Excel Formula Efficiency 1: SUMPRODUCT function 12 E 17) For other videos and video series about array formulas, see these: 06 Excel Series: Array Formulas Excel Magic Trick 313: Some Facts About Array Formula 07 Excel Formula Efficiency Series Weight in Portfolio 0.25 0.75 Economic Probability Stock1 Stock1 State of State Return Return Weak 0.55 -0.02 0.05 Normal 0.3 0.059 0.065 Boom 0.15 0.12 0.085 E. Returns and larger array formulas may take a long time to calculate ds. SUMPRODUCT can be used with commas when the ranges are the same size and larger array formulas may take a long time to calculate and larger array formulas may take a long time to calculate Ctrl + Shift + Enter Don't forget Ctrl + Shift + Enter Shift + Enter must use multiplication instead of ranges and commas because the size of the ranges/arrays are not the same. SUMPRODUCT function 12 Examples e Facts About Array Formulas 1) Range means a group of cells such as B12:E12 2) The Range B12:E12 can contain the values: 5, 7, 9, 11 An array (different than a range) means a group of things (values, text, 3) TRUE/FALSE) stored in a range of cells or in an array using array syntax Array syntax uses the: 1) curly bracket to contain the array, 2) comma 4) for column and 3) semi-colon for row, 4) Text is in quotes 5) An array could be: {5, 7, 9, 11} If the values did not need to be in cells, instead of =SUM(B12:E12), you 6) could use the formula =SUM({5, 7, 9, 11}) To create an array from a range of values, click in a cell, type the equal sign, highlight a range of cells, then highlight the range of cells in the formula, hit the F9 key, then Ctrl + C to copy the array with all the array 7) syntax. 8) An array could be: {''Week1'',''Week2'',''Week3'',''Week4'';5,7,9,11} Instead of the formula, =VLOOKUP(B20,$B$15:$C$17,2,0), you could use this (then the data is hard coded into formula), =VLOOKUP(B20,{""Part1"",25;""Part2"",10;""Part3"",5},2,0). This formula is not an Array Formula (because it does not use Ctrl + Shift + 9) Enter to put formula in cell), but it is a formula that uses an array. Week1 Week2 Week3 Week4 Total Total 5 7 9 11 32 32 Item Price 1 Part1 $25.00 1 Part2 $10.00 25 1 Part3 $5.00 3 Product Price Part2 10 10 Different than regular formulas, array formulas can: 1) Do operations on arrays instead of individual cells 2) Allow you to enter functions such as TRANSPOSE, FREQUENCY and MMULT, which are by definition array formulas. 3) Allow ranges or arrays in function arguments where the function is expecting a single value Array Formulas can do operations on arrays such as 10) =SUM(B35:B38*C35:C38) In order to get Excel to recognize that your formula is an array formula, you must use the key strokes Ctrl + Shift + Enter to put the formula in 11) the cell or range of cells. When you use Ctrl + Shift + Enter to put an array formula into a cell or 12) range of cells Curley Brackets appear automatically around the formula. If you enter =SUM(B35:B38*C35:C38), {=SUM(B35:B38*C35:C38)} will 13) appear Array formulas can save spreadsheet real-estate. For example, instead of using an extra column to calculate sales for each transaction and then adding the total for each transaction, you can simply use an array 14) formula in one cell. In cell D40 create this formula: =SUM(B35:B38*C35:C38), then use Ctrl + Shift + Enter to create the Array Formula. The result will look like {=SUM(B35:B38*C35:C38)}. The curley brackets will be put in automatically. Units Price Sales 5 $22.00 $110.00 What are we doing: multiply, then add 2 $23.00 $46.00 Array formula advantage: use less space 4 $25.00 $100.00 Array formula disadvantage: must use Ctrl + Shift + Enter and larger array formul 6 $24.50 $147.00 Total $403.00 $403.00 Total $403.00 Array formula in 1 cell Total $403.00 SUMPRODUCT multiplies the related ranges and then adds. SUMPRODUCT can b To Enter Array Formula you must use keystrokes: Ctrl + Shift + Enter In cell D53 create this formula: =MIN(C46:C51-B46:B51), then use Ctrl + Shift + Enter to create the Array Formula. The result will look like {=MIN(C46:C51- B46:B51)}. The curley brackets will be put in automatically. Sample Sample Weight Beg Weight End Change 1 10.00 lbs 11.20 lbs 1.20 lbs What are we doing: subtracting then find MIN 2 12.50 lbs 13.00 lbs 0.50 lbs Array formula advantage: use less space 3 9.00 lbs 10.00 lbs 1.00 lbs Array formula disadvantage: must use Ctrl + Shift + Enter and larger array formul 4 11.50 lbs 12.00 lbs 0.50 lbs 5 10.00 lbs 10.25 lbs 0.25 lbs 6 14.00 lbs 14.75 lbs 0.75 lbs 0.25 lbs 0.25 lbs Array formula in 1 cell To Enter Array Formula you must use keystrokes: Ctrl + Shift + Enter In cell D65 create this formula: =MAX(C58:C63-B58:B63), then use Ctrl + Shift + Enter to create the Array Formula. The result will look like {=MAX(C58:C63- B58:B63)}. The curley brackets will be put in automatically. Stock $ End Stock $ Beg Change 1 26 27 -1 What are we doing: subtracting then find MAX 2 28 19 9 Array formula advantage: use less space 3 13 15 -2 Array formula disadvantage: must use Ctrl + Shift + Enter and larger array formul 4 27 15 12 5 17 29 -12 6 28 32.5 -4.5 12 12 Array formula in 1 cell To Enter Array Formula you must use keystrokes: Ctrl + Shift + Enter Weight in Portfolio 0.333333 0.333333333 0.33333333 Economic Probability Stock1 Stock2 Stock3 Expected State of State Return Return Return Returns Weak 0.4 0.1 0.15 0.2 0.06 Boom 0.6 0.08 0.04 0 0.024 0.084 0.084 Array formula in 1 cell Don't forget Ctrl + Shift + Enter Weight in Portfolio 0.25 0.75 Economic Probability Stock1 Stock2 Expected State of State Return Return Returns Weak 0.55 -0.02 0.05 0.017875 Normal 0.3 0.059 0.065 0.01905 Boom 0.15 0.12 0.085 0.0140625 0.0509875 0.0509875 Array formula in 1 cell Don't forget Ctrl + Shift 0.0509875 Does not require Ctrl + Shift + Enter Example of SUMPRODUCT formula that will not work: #VALUE! For SUMPRODUCT, you must use multiplication If you want to multiply ranges and add, instead of the SUM function with the keystrokes Ctrl + Shift = Enter, you can use the SUMPRODUCTS function. This is not an array formula because we do not use Ctrl + Shift + Enter. SUMPRODUCT can handle arrays without Ctrl + Shift + Enter. If the ranges used in SUMPRODUCT are the same size, use ranges and commas. If the ranges used in SUMPRODUCT are NOT the same size, 15) use ranges and the multiplication symbol. 16) See this video for more about SUMPRODUCT: Excel Formula Efficiency 1: SUMPRODUCT function 12 E 17) For other videos and video series about array formulas, see these: 06 Excel Series: Array Formulas Excel Magic Trick 313: Some Facts About Array Formula 07 Excel Formula Efficiency Series Weight in Portfolio 0.25 0.75 Economic Probability Stock1 Stock1 State of State Return Return Weak 0.55 -0.02 0.05 Normal 0.3 0.059 0.065 Boom 0.15 0.12 0.085 -0.00275 0.020625 0.004425 0.014625 0.0045 0.0095625 E. Returns 0.050988 0.0509875 0.050988 0.0509875 and larger array formulas may take a long time to calculate ds. SUMPRODUCT can be used with commas when the ranges are the same size and larger array formulas may take a long time to calculate and larger array formulas may take a long time to calculate Ctrl + Shift + Enter Don't forget Ctrl + Shift + Enter Shift + Enter must use multiplication instead of ranges and commas because the size of the ranges/arrays are not the same. SUMPRODUCT function 12 Examples e Facts About Array Formulas SUMPRODUCT function SUM = add ==> The "sum" of 3 + 4 = 7 PRODUCT = multiply ==> The "product" of 1 * 3 = 3 The SUMPRODUCT function multiplies arrays of the same size and then adds the products note: The array arguments must have the same dim The SUMPRODUCT function multiplies 1st, then adds SUMPRODUCT treats array entries that are no SUMPRODUCT can deal with ranges or arrays 1 row by 2 column array * 1 row by 2 column array = A8:B8*A9:B9 = 1*3+2*2 = 7 1 2 3 2 1*3+2*2 = 7 SUMPRODUCT(A8:B8,A9:B9) = 7. Notice commas between arrays. You can do this: SUMPRODUCT(A8:B8*A9:B9) = 7 but for large spreadsheets it may take longer to calculate. 1 2 3 2 1*3+2*2 = 7 SUMPRODUCT(A14:B14,C15:D15) = 7. Notice commas between arrays. 1 2 3 2 The array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value. SUMPRODUCT(A19:B19,D19:D20) will not work because the arrays are not the same dimensions. The dimensions are 1 by 2 * 2 by 1. You can get around this by using a multiplication symbol instead of a comma. SUMPRODUCT(A19:B19*D19:D20) This is what it did: 1*3+2*3+1*2+2*2 = Rule about when to use comma: When ranges/arrays are same dimensions Rule about when to use multiplication symbol: When ranges/arrays are different dimensions SUMPRODUCT is great for dealing with arrays of TRUEs and FALSEs when you are doing conditional adding Although you can use *1, /1, +0 to convert TRUEs & FALSEs to 1s and 0s, the method that calculates fastest is double negative --. Month Feb Year 2009 Sales Rep Julie Add Count Records Date SalesRep # Phone Calls 1/2/2009 Mo 5 SUMIFS cannot do this because SUMIFS cannot handl 2/1/2009 Julie 2 =SUMIFS(C38:C45,--(TEXT(A38:A45,"mmmyyyy")),B3 1/15/2009 Mo 5 2/1/2009 Mo 3 1/30/2009 Julie 2 2/15/2009 Julie 2 2/15/2009 Mo 3 1/28/2009 Julie 3 SUMPRODUCT can deal with arrays, whereas the SUMIFS function can not (our TEXT example is proof of this). Note: SUMIFS see workbook references as arrays and so it is best to use SUMPRODUCT for conditional adding when you have workbook references. See this video for more about SUMPRODUCT and SUMIF: Excel Magic Trick 315: SUMIF Only Accept Ranges, Not Arrays must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value. s array entries that are not numeric as if they were zeros. Months Years SalesRep Jan 2009 Mo Feb Julie use SUMIFS cannot handle arrays. 38:A45,"mmmyyyy")),B32&B33,B38:B45,B34) SUMPRODUCT function SUM = add ==> The "sum" of 3 + 4 = 7 PRODUCT = multiply ==> The "product" of 1 * 3 = 3 The SUMPRODUCT function multiplies arrays of the same size and then adds the products note: The array arguments must have the same dim The SUMPRODUCT function multiplies 1st, then adds SUMPRODUCT treats array entries that are no SUMPRODUCT can deal with ranges or arrays 1 row by 2 column array * 1 row by 2 column array = A8:B8*A9:B9 = 1*3+2*2 = 7 1 2 3 2 1*3+2*2 = 7 7 SUMPRODUCT(A8:B8,A9:B9) = 7. Notice commas between arrays. 7 7 You can do this: SUMPRODUCT(A8:B8*A9:B9) = 7 but for large spreadsheets it may take longer to calculate. 1 2 3 2 1*3+2*2 = 7 SUMPRODUCT(A14:B14,C15:D15) = 7. Notice commas between arrays. 7 1 2 3 2 The array arguments must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value. SUMPRODUCT(A19:B19,D19:D20) will not work because the arrays are not the same dimensions. The dimensions are 1 by 2 * 2 by 1. #VALUE! You can get around this by using a multiplication symbol instead of a comma. SUMPRODUCT(A19:B19*D19:D20) 15 This is what it did: 1*3+2*3+1*2+2*2 = 15 Rule about when to use comma: When ranges/arrays are same dimensions Rule about when to use multiplication symbol: When ranges/arrays are different dimensions SUMPRODUCT is great for dealing with arrays of TRUEs and FALSEs when you are doing conditional adding Although you can use *1, /1, +0 to convert TRUEs & FALSEs to 1s and 0s, the method that calculates fastest is double negative --. Month Feb Year 2009 Sales Rep Julie Add 4 Count Records 2 Date SalesRep # Phone Calls 1/2/2009 Mo 5 SUMIFS cannot do this because SUMIFS cannot handl 2/1/2009 Julie 2 =SUMIFS(C38:C45,--(TEXT(A38:A45,"mmmyyyy")),B3 1/15/2009 Mo 5 2/1/2009 Mo 3 1/30/2009 Julie 2 2/15/2009 Julie 2 2/15/2009 Mo 3 1/28/2009 Julie 3 SUMPRODUCT can deal with arrays, whereas the SUMIFS function can not (our TEXT example is proof of this). Note: SUMIFS see workbook references as arrays and so it is best to use SUMPRODUCT for conditional adding when you have workbook references. See this video for more about SUMPRODUCT and SUMIF: Excel Magic Trick 315: SUMIF Only Accept Ranges, Not Arrays must have the same dimensions. If they do not, SUMPRODUCT returns the #VALUE! error value. s array entries that are not numeric as if they were zeros. Months Years SalesRep Jan 2009 Mo Feb Julie use SUMIFS cannot handle arrays. 38:A45,"mmmyyyy")),B32&B33,B38:B45,B34) Array Functions must be entered as array formulas with the key strokes Ctrl + 1) Shift + Enter TRANSPOSE (turns flips the range 90 degrees) is an array function that: 1) must be entered using Ctrl + Shift + Enter 2) All the cell must be highlighted before you create the formula. In the case of TRANSPOSE you must count the number of columns and rows in the original and then highlight a similar range, but flipped 90 degrees ==> 4 x 2 becomes 2 x 4. Item Price Part1 $50.00 Part2 $10.00 Part3 $5.00 To Enter Array Formula you must use keystrokes: Ctrl + Shift + Enter FREQUENCY is an array function that: 1) must be entered using Ctrl + Shift + Enter 2) All the cell must be highlighted before you create the formula. The FREQUENCY function uses two arguments: data_array and bins_array (see below) Data_array Bins_array (Upper (Numbers to be Limit of each counted) class) 5 10 <= 10 9 20 10 > and <= 20 10 30 20 > and <= 30 15 To Enter Array Formula you must use 22 keystrokes: Ctrl + Shift + Enter 21 4 Some Other Array Functions and videos to watch: FREQUENCY Excel Array Formula Series #8: FREQUENCY function TRANSPOSE Excel Array Formula Series #2: TRANSPOSE function MMULT Excel Array Formula Series 12: MMULT function Matrix Algebra ROW Excel Array Formula Series #11: ROW & COLUMN functions COLUMN Excel Array Formula Series #11: ROW & COLUMN functions MINVERSE LINEST Excel Array Formula Series #13: LINEST function UENCY function SPOSE function LT function Matrix Algebra W & COLUMN functions W & COLUMN functions Array Functions must be entered as array formulas with the key strokes Ctrl + 1) Shift + Enter TRANSPOSE (turns flips the range 90 degrees) is an array function that: 1) must be entered using Ctrl + Shift + Enter 2) All the cell must be highlighted before you create the formula. In the case of TRANSPOSE you must count the number of columns and rows in the original and then highlight a similar range, but flipped 90 degrees ==> 4 x 2 becomes 2 x 4. Item Price Part1 $25.00 Part2 $10.00 Part3 $5.00 Item Part1 Part2 Part3 Price $25.00 $10.00 $5.00 To Enter Array Formula you must use keystrokes: Ctrl + Shift + Enter FREQUENCY is an array function that: 1) must be entered using Ctrl + Shift + Enter 2) All the cell must be highlighted before you create the formula. The FREQUENCY function uses two arguments: data_array and bins_array (see below) Data_array Bins_array (Upper (Numbers to be Limit of each counted) class) 5 10 <= 10 4 9 20 10 > and <= 20 1 10 30 20 > and <= 30 2 15 To Enter Array Formula you must use 22 keystrokes: Ctrl + Shift + Enter 21 4 Some Other Array Functions and videos to watch: FREQUENCY Excel Array Formula Series #8: FREQUENCY function TRANSPOSE Excel Array Formula Series #2: TRANSPOSE function MMULT Excel Array Formula Series 12: MMULT function Matrix Algebra ROW Excel Array Formula Series #11: ROW & COLUMN functions COLUMN Excel Array Formula Series #11: ROW & COLUMN functions MINVERSE LINEST Excel Array Formula Series #13: LINEST function UENCY function SPOSE function LT function Matrix Algebra W & COLUMN functions W & COLUMN functions If you enter a range of values or an array into a function argument that is expecting a single value: 1) You must use Ctrl + Shift + Enter to put the formula in the cell 2) You will return an array of values. If the function is in one cell, it will only show the first value in the array. If the function is in a formula or function 1) that expects an array, the whole array will be used. TEXT function with custom number format to count Dates in Jan 2009 Criteria 1 Jan Criteria 2 2009 Count 3 Count 3 Sum Sales 10 Sum Sales 10 Dates Sales 1/25/2009 $5.00 2/20/2009 $2.00 7/1/2009 $5.00 5/2/2009 $6.00 1/2/2009 $1.00 1/18/2009 $4.00 4/23/2009 $2.00 Date Item VLOOUP Price 5/5/2009 Part1 Item Price 5/6/2009 Part2 Part1 $25.00 5/7/2009 Part2 Part2 $10.00 5/8/2009 Part3 Part3 $5.00 5/9/2009 Part1 Total Total Total SalesRep Sales Joe $5.00 Sioux $2.00 Joe $5.00 Sioux $6.00 Joe $1.00 Joe $4.00 Sioux $2.00 criteria Joe MEDIAN =MEDIAN(IF(A31:A37=B39,B31:B37)) MAX =MAX(IF(A31:A37=B39,B31:B37)) MIN =MIN(IF(A31:A37=B39,B31:B37)) Names Count Unique Records Tom =SUMPRODUCT(--(A45:A51<>""),1/COUNTIF(A45:A51,A45: Sioux Sue Sioux Jo Mo 2) Array Formulas: Add the 3 highest values 3) Remember to enter array formula with Ctrl + Shift + Enter 4) The formula is: =SUM(LARGE(D11:H11,{1,2,3})) Boomerang Maximun Time Aloft Throw 1 Throw 2 Throw 3 Throw 4 Throw 5 Sum Top 3 Thrower 1 35.1 42.3 43 16.5 22.15 Thrower 2 42.3 35.1 16.5 22.15 43 Thrower 3 6.87 38.2 47.8 27.76 20.34 Thrower 4 22.15 16.5 43 42.3 35.1 Thrower 5 26.36 49.8 11.97 27.46 19.96 Thrower 6 26.36 42.85 45.62 24.54 3.98 Thrower 7 28.18 17.03 35.21 32.38 6.66 Thrower 8 10.82 39.95 27.78 2.05 54.3 Thrower 9 35.1 42.3 16.5 43 22.15 Thrower 10 8.47 32.62 11.95 13.07 16.36 Warning about Array Formulas: In large spreadsheets array formulas can slow calculating speed down considerably. For tips about large spreadhseets, see this video Series: 07 Excel Formula Efficiency Series =SUM((TEXT(A12:A18,"mmmyyyy")=B4&B5)*B12:B18) =SUMPRODUCT(--(TEXT(A12:A18,"mmmyyyy")=B4&B5),B12:B18) =SUM(SUMIF(E22:E24,B21:B25,F22:F24)) =SUMPRODUCT(SUMIF(E22:E24,B21:B25,F22:F24)) B39,B31:B37)) 5:A51<>""),1/COUNTIF(A45:A51,A45:A51&"")) + Enter )) Rank =SUM(LARGE(B61:F61,{1,2,3})) If you enter a range of values or an array into a function argument that is expecting a single value: 1) You must use Ctrl + Shift + Enter to put the formula in the cell 2) You will return an array of values. If the function is in one cell, it will only show the first value in the array. If the function is in a formula or function that expects an array, the 1) whole array will be used. TEXT function with custom number format to count Dates in Jan 2009 Criteria 1 Jan Criteria 2 2009 Count 3 Count 3 Sum Sales 10 =SUM((TEXT(A12:A18,"mmmyyyy")= Sum Sales 10 =SUMPRODUCT(--(TEXT(A12:A18,"m Jan2009 Dates Sales 1/25/2009 $5.00 2/20/2009 $2.00 7/1/2009 $5.00 5/2/2009 $6.00 1/2/2009 $1.00 1/18/2009 $4.00 4/23/2009 $2.00 Date Item VLOOUP Price 5/5/2009 Part1 $25.00 Item Price 5/6/2009 Part2 $10.00 Part1 $25.00 5/7/2009 Part2 $10.00 Part2 $10.00 5/8/2009 Part3 $5.00 Part3 $5.00 5/9/2009 Part1 $25.00 Total $75.00 Total $75.00 =SUM(SUMIF(E22:E24,B21:B25,F22:F Total $75.00 =SUMPRODUCT(SUMIF(E22:E24,B21 SalesRep Sales Joe $5.00 Sioux $2.00 Joe $5.00 Sioux $6.00 Joe $1.00 Joe $4.00 Sioux $2.00 criteria Joe MEDIAN 4.5 =MEDIAN(IF(A31:A37=B39,B31:B37)) MAX 5 =MAX(IF(A31:A37=B39,B31:B37)) MIN 1 =MIN(IF(A31:A37=B39,B31:B37)) Names Count Unique Records Tom 5 =SUMPRODUCT(--(A45:A51<>""),1/COUNTIF(A45:A51,A45:A51&"")) Sioux Sue Sioux Jo Mo 2) Array Formulas: Add the 3 highest values 3) Remember to enter array formula with Ctrl + Shift + Enter 4) The formula is: =SUM(LARGE(D11:H11,{1,2,3})) Boomerang Maximun Time Aloft Throw 1 Throw 2 Throw 3 Throw 4 Throw 5 Sum Top 3 Rank Thrower 1 35.1 42.3 43 16.5 22.15 120.4 3.5 Thrower 2 42.3 35.1 16.5 22.15 43 120.4 3.5 Thrower 3 6.87 38.2 47.8 27.76 20.34 113.76 7 Thrower 4 22.15 16.5 43 42.3 35.1 120.4 3.5 Thrower 5 26.36 49.8 11.97 27.46 19.96 103.62 8 Thrower 6 26.36 42.85 45.62 24.54 3.98 114.83 6 Thrower 7 28.18 17.03 35.21 32.38 6.66 95.77 9 Thrower 8 10.82 39.95 27.78 2.05 54.3 122.03 1 Thrower 9 35.1 42.3 16.5 43 22.15 120.4 3.5 Thrower 10 8.47 32.62 11.95 13.07 16.36 62.05 10 Warning about Array Formulas: In large spreadsheets array formulas can slow calculating speed down considerably. For tips about large spreadhseets, see this video Series: 07 Excel Formula Efficiency Series XT(A12:A18,"mmmyyyy")=B4&B5)*B12:B18) ODUCT(--(TEXT(A12:A18,"mmmyyyy")=B4&B5),B12:B18) MIF(E22:E24,B21:B25,F22:F24)) ODUCT(SUMIF(E22:E24,B21:B25,F22:F24)) (A45:A51,A45:A51&"")) =SUM(LARGE(B61:F61,{1,2,3})) Succeeding in Business with MS Excel 2007, ISBN 978-1-4239-0605-6, Authors: Gross, Akaiwa, Nordquist PO527 Credit Analysis HW P242-243 WWC Hiring Analysis HW P276-278 Unpaid Invoice Penalties P257-259, Painting Job Estimator P 278-280 605-6, Authors: P 278-280 0e78ec83-c2d9-460e-ad73-11c07f401fc2.xls - P214-241 - Level 3 Rule #1 Rule #2 Rule #3 Reject FALSE FALSE FALSE Further Evaluate TRUE FALSE FALSE OR FALSE TRUE FALSE FALSE FALSE TRUE FALSE TRUE TRUE Inside an or Accept TRUE TRUE TRUE TRUE FALSE TRUE TRUE TRUE FALSE Accounts Receivable Department - Custo D&B D&B Composite Credit Credit Current Past Due Net Worth Rating Appraisal (1 Customer Name Current Credit Limit Previous Year's Sales Year's Sales Balance in (000) Class Best) Athletic Gear Corp. $ 9,000 $ 15,382 $ 11,952 $ 0 $ 450 BA 4 Baltimore O's 39,000 10,033 7,789 0 1,950 3A 1 Baseball & More 75,000 60,009 55,342 13,892 37,500 4A 2 Canadian Ski Club 33,000 35,039 50,921 495 1,650 BA 2 Concord Pro Shop 10,000 4A 1 Everything Golf 25,000 15,221 9,483 2,899 1,250 3A 3 Lake Pro Shops 42,000 80,498 81,126 0 2,100 3A 2 Mars Dept. Store 27,000 35,354 20,666 0 213 BB 3 RG Bradley 46,000 90,970 18,343 0 2,300 3A 1 RX for Sports 15,000 5,663 3,014 0 750 2A 1 School Sports Supply 45,000 50,278 32,338 0 2,250 3A 3 Ski World 26,000 25,864 28,154 0 300 BA 2 Sneaker Kingdom 45,000 40,157 25,379 0 2,250 3A 2 Sports & Stuff 15,000 15,898 14,732 14,383 450 BA 1 Toy Kingdom 22,000 10,073 1,047 0 1,100 3A 3 Under the Sea 45,000 95,411 64,418 0 150 CB 4 US Olympic Team 20,000 5,621 6,171 0 1,000 3A 1 WWW Sports Inc. 100,000 60,009 60,354 0 500,000 5A 2 Zip & Sons 10,000 15,490 22,760 0 620 1A 2 Formatting Rules Net worth is less than Rule 1 $1,000,000.00 $ 1,000,000.00 Net worth is greater than Rule 2 $10,000,000.00 $ 10,000,000.00 vable Department - Customer Credit Analysis Rule 1 (FALSE is Reject): Rule 2 (FALSE is Reject): D&B Past due balance is less Composite credit appraisal PAYDEX D&B Stress Risk Class than 10% of this year's value of 1 OR a PAYDEX (100 Best) (1 Best) total sales score over 90 15 3 TRUE FALSE 51 1 TRUE TRUE 70 1 FALSE FALSE 43 1 TRUE FALSE 91 1 TRUE 76 1 FALSE FALSE 87 1 TRUE FALSE 94 1 TRUE TRUE 21 1 TRUE TRUE 59 1 TRUE TRUE 91 1 TRUE TRUE 82 1 TRUE FALSE 71 1 TRUE FALSE 67 1 FALSE TRUE 14 1 TRUE FALSE 79 2 TRUE FALSE 87 1 TRUE TRUE 97 1 TRUE TRUE 96 1 TRUE TRUE Rule 3 (FALSE is Reject): Net worth of at least $500,000.00 AND a composite credit appraisal value less than or equal to 2 AND PAYDEX score over 70 Reject? Further Evaluate? AND a stress risk class of 1 Accept? FALSE Accept FALSE FurtherEvaluate FALSE Reject FALSE Accept TRUE FurtherEvaluate FALSE Reject TRUE FurtherEvaluate FALSE FurtherEvaluate FALSE FurtherEvaluate FALSE FurtherEvaluate FALSE FurtherEvaluate FALSE Accept TRUE FurtherEvaluate FALSE FurtherEvaluate FALSE Accept FALSE Accept TRUE FurtherEvaluate TRUE FurtherEvaluate TRUE FurtherEvaluate PO 527 Bidder List Financial Evalu D&B Composite Credit Net Worth Appraisal (1 Bidder List Previous Experience Grade (Dollars) Best) Logistic SB Inc. None $ 2,500,000 2 XBE Corporation Satisfactory 237,000,000 1 Software Solutions Satisfactory 1,823,000 3 Exceed RW Unsatisfactory 75,000 2 Roberts Wise Inc. Satisfactory 680,000 3 Did any evaluate to TRUE? Did all not evaluate to TRUE? Or Did all come Assumptions Rule 1 D&B Stress Risk Class (1 Best) Hurdle = 1 1 D&B PAYDEX (100 Best) is greater than 60 AND D&B Composite Credit Appraisal (1 Best) is less than or equal to 2 AND Net Worth (Dollars) are greater than Rule 2 $1,000,000.00 60 2 D&B PAYDEX (100 Best) is greater than or equal to 85 Rule 3 OR Previous Experience Grade is Satisfactory 85 Satisfactory Format if TRUE = Green TRUE Format if FALSE = Grey FALSE Format Bidder name who had Previous Experience Grade "Unsatisfactory" Unsatisfactory Note: Conditional formatting in column A is based on values in Column B. The formula is: =B3=$C$18 er List Financial Evaluation Rule 2: D&B PAYDEX (100 Best) is greater than 60 AND D&B Rule 1: D&B Composite Credit Appraisal (1 Rule 3: D&B PAYDEX (100 Best) D&B Stress Stress Risk Best) is less than or equal to 2 AND is greater than or equal to 85 OR D&B PAYDEX Risk Class Class (1 Best) Net Worth (Dollars) are greater than Previous Experience Grade is (100 Best) (1 Best) Hurdle = 1 $1,000,000.00 Satisfactory 85 1 TRUE TRUE TRUE 90 1 TRUE TRUE TRUE 55 2 FALSE FALSE TRUE 38 1 TRUE FALSE FALSE 43 1 TRUE FALSE TRUE TRUE TRUE TRUE to TRUE? Or Did all come out false? FALSE FALSE FALSE $ 1,000,000.00 TheZone Customer Accounts - Credit & Payme 60-Days 90-Days Customer Name Customer Type Current Balance Due 30-Days Past Due Past Due Past Due Athletic Gear Corp. A $ 8,612 $ 0 $ 0 $ 0 Baltimore O's B 0 0 0 0 Baseball & More A 2,345 3,473 5,557 4,862 Canadian Ski Club C 0 345 0 150 Everything Golf A 0 0 2,000 899 Sports & Stuff A 0 14,000 383 0 Assumptions Credit Approved Credit Denied Note: Order for non-mutually exclusive criteria matters here because you can have balances in all three categories (not mutua and so you must test Minimum before Minimum Surcharge % of 90 applying surcharge surcharge day balance If the 90-Days Past Due balance is greater than $200.00 then the large of $100.00 or 10% of 90 day balance $ 200 $ 100 10.00% Fee for 30-Days Fee for 60-Days Fee for 90-Days Past Due Past Due Past Due Alternative Penalty Scheme $ 25 $ 50 $ 100 Past Due Past Due Past Due Days 30 60 90 This example is of criteria that is mutually exclusive, the order of the logical tests does not matter. Late Fee Cust Type A Cust Type Late Fee 50 A B Cust Type Late Fee 100 B C Cust Type Late Fee 150 C VLOOKUP if often better than many Ifs. But if you use Ifs, better to go from low to high or high to low instead of betw Hurdle Penalty Level 1: less tha $1,000.00 $ 1,000 $ 25 Level 2: less than $5,000.00 and greater than or equal to $1,000.00 $ 1,000 $ 50 Level 3 greater than $5,000.00 $ 5,000 $ 100 Past Due =IF(A40<B$33,C$33,IF(A40<B$35,C$34,C$35)) =IF(A40>B$35,C$35,IF(A40>B$34,C$34,C$3 $ 670 $ 25.00 $ 25.00 $ 141 $ 25.00 $ 25.00 $ 3,893 $ 50.00 $ 50.00 $ 4,741 $ 50.00 $ 50.00 $ 8,434 $ 100.00 $ 100.00 $ 7,401 $ 100.00 $ 100.00 $ 4,819 $ 50.00 $ 50.00 $ 8,344 $ 100.00 $ 100.00 counts - Credit & Payment Status If the 90-Days Past Alternative Due balance is Penalty Current Credit Status (FALSE greater than $200.00 Scheme: 30 = means that they have been then the large of $25.00; 60 = Customer Total Past denied credit but may still Credit Approved $100.00 or 10% of 90 $50.00; 90 = Type Late Due Balance owe $) or Credit Denied day balance $100.00 Fee $ 0 TRUE Credit Approved $ - $ - $ - 0 TRUE Credit Approved $ - $ - $ - 13,892 TRUE Credit Approved $ 486 $ 100 $ 50 495 TRUE Credit Approved $ - $ 100 $ 150 2,899 FALSE Credit Denied $ 100 $ 100 $ 50 14,383 FALSE Credit Denied $ - $ 50 $ 50 all three categories (not mutually exclusive), al tests does not matter. h or high to low instead of between. >B$35,C$35,IF(A40>B$34,C$34,C$33)) =IF(AND(A40>=B$34,A40<B$35),C$34,IF(A40<B$33,C$33,C$35)) $ 25.00 $ 25.00 $ 50.00 $ 50.00 $ 100.00 $ 100.00 $ 50.00 $ 100.00 Fixed Fee & 90 Day Penalties Graduated Penalties - Range Penalty Fixed Fee Penalty $ 50 Graduated penalty < $ 1,000 $ 0 Hurdle for penalty $ 100 Graduated penalty >=1000 but < 5,000 100 90-day penalty percentage 10% Graduated penalty >= 5,000 200 TheZone Accounts Payable - Outstanding Balance Report Applies Total Past Fixed 30-Days 60-Days 90-Days Due Fixed Vendor Name Category Penalty Past Due Past Due Past Due Balance Penalty RTF Electric Utilities TRUE $ 16,254 $ 0 $ 0 $ 16,254 $ 50 Ross County Water & Sewer Utilities FALSE 1,435 0 0 1,435 $ 0 YNC Trucking Transportation TRUE 0 0 3,300 3,300 $ 50 Italian Leather Group Ltd. Raw Materials TRUE 6,756 0 5,674 12,430 $ 50 Union Plastics Raw Materials TRUE 0 436 0 436 $ 50 Freight to Go Transportation FALSE 0 0 873 873 $ 0 Temps R'Us Labor FALSE 2,700 0 0 2,700 $ 0 Notworth Telephone Telephone TRUE 0 0 2,345 2,345 $ 50 Category Penalties Utilities 10% Labor 15% Other 5% nce Report 90-Day Graduated Category Penalty Penalty Penalty $ 0 $ 200 $ 1,625 0 100 144 330 100 165 567 200 622 0 0 22 87 0 44 0 100 405 235 100 117 Appl Personal College Major Interview Work Name GPA Code Refer-ences Rating Experience Michael 3.6 2 3 5 TRUE Brian 3.9 18 3 3 FALSE George 2.1 15 3 2 FALSE Jasen 3.0 30 3 5 TRUE Cindy 3.2 100 4 3 TRUE Sara 3.7 4 3 6 TRUE Courtney 4.0 16 5 4 FALSE Scott 2.1 260 2 2 FALSE Mark 2.8 20 3 5 FALSE Assumptions Job Applicants must supply the following information Automatically disqualified Automa Valid GPA min 1.5 If any of these are TRUE If all of the Valid GPA max 4 Valid GPA min 1.5 Standardized universal Major Code Valid GPA max 4 Valid Employment Exam Engineering 1 Score min 200 Valid Employment Exam Business 2 Score max 800 Valid Standardized Economics 3 universal Major Code min 1 Valid Standardized Physical Science 4 universal Major Code max 250 Last one 250 GPA less than 2.5 Reference letters less than 3 Reference letters less than 3 Personal Interview rating Employment Exam less less than 3 than 600 Personal Interview rating Work Experience TRUE FALSE less than 3 Valid Employment Exam Score min 200 Valid Employment Exam Score max 800 Undergraduate school ranking Automatically disqualified Automatically hired Undecided If the applicant does not fit into one of the other categories Application for Employment School InvalidScores/ Automatically Employment ExamScore Rank Major? Automatically Disqualified Hired 700 12 FALSE FALSE FALSE 850 1 TRUE TRUE FALSE 780 5 FALSE TRUE FALSE 710 3 FALSE FALSE FALSE 600 26 FALSE FALSE FALSE 740 15 FALSE FALSE TRUE 720 8 FALSE FALSE FALSE 590 33 TRUE TRUE FALSE 760 6 FALSE FALSE FALSE Total 2 3 1 Total 2 3 1 All are invalid FALSE FALSE FALSE Any are invalid? TRUE TRUE TRUE Automatically hired If all of these are TRUE GPA score greater than 3.5 Major Code min 1 Major Code max 20 Undergraduate school ranking less than or equal to 25 Employment Exam greater than 700 Personal Interview rating greater than or equal to 4 Work Experience TRUE Not Automatically Not Automatically Disqualified Hired No Decision No Decision TRUE TRUE TRUE TRUE FALSE TRUE FALSE FALSE FALSE TRUE FALSE FALSE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE TRUE FALSE FALSE FALSE TRUE TRUE TRUE TRUE FALSE TRUE FALSE FALSE TRUE TRUE TRUE TRUE 6 8 5 6 8 5 FALSE FALSE FALSE TRUE TRUE TRUE RJ Construction - Painting Estimator Square Feet (SF) of Length in Width in Wall/Ceilin Wall New Color Room Feet Feet Height in Feet g Condition Lighter ? Kitchen 20 15 8 860 2 TRUE Bedroom1 16 12 8 640 1 FALSE Bedroom2 10 12 8 472 1 FALSE Bath 8 6 8 272 3 FALSE Total Total Discounted Price: Assumptions Wall Condition NO. Cost primer Cost of first coat Excellent 1 0 0.45 Reasonable 2 0 0.5 Poor 3 0.5 0.6 Cost per square foot for second coat when poor 0.35 New Color Lighter ? TRUE FALSE 0.3 Grade of Paint Cost Discounts Premium 0.15 0 0% Economy -0.1 1000 10% Superior 0 2500 12% 5000 15% High Price 450 Painting Estimator Wall Second Paint Repairs & First Coat Coat Quality Grade of Primer Painting Painting Adjustmen High Paint Costs Costs Costs t Total Costs Price? Premium - 430 258.00 129.00 817.00 TRUE Economy - 288 - (64.00) 224.00 FALSE Superior - 212.4 - - 212.40 FALSE Superior 136.00 163.2 95.20 - 394.40 FALSE 136.00 1,093.60 353.20 65.00 1,647.80 1483.02 alternative alternative alternative 430.00 129 1483.02 288.00 -64 212.40 0 163.20 0 PO 611 Bidder List Financial Evaluation Net Worth D&B CCAR (1 Customer Name (Dollars) Best) D&B PAYDEX (100 Best) BFF Industries $ 157,795 1 90 NRKK 387,000 4 83 Pergo Molding 775,961 2 98 Allma 17,043,973 3 55 JF & Sons 35,446,678 1 32 Argree Inc. 63,545,102 3 0 RGM Plastics 64,831,692 2 85 Soto Services 182,686,331 1 73 NE Plastic 239,227,375 2 79 SPDM 487,215,654 2 97 Assumptions (HURDLES) Total outcomes = Net Worth (Dollars) $ 500,000.00 D&B CCAR (1 Best) 1 Experiments D&B Stress Risk Class (1 Best) 1 Outcomes for each experiment D&B PAYDEX (100 Best) 90 Allow to bid Exclude Exclude Get more data Get more data Allow to bid al Evaluation D&B Stress Risk Class Final (1 Best) Rule #1 Rule #2 Rule #3 Recommendation 1 FALSE TRUE FALSE Get more data 3 FALSE FALSE FALSE Exclude 1 TRUE FALSE TRUE Allow to bid 2 TRUE FALSE FALSE Get more data 1 TRUE TRUE FALSE Allow to bid 1 TRUE FALSE FALSE Get more data 1 TRUE FALSE FALSE Get more data 1 TRUE TRUE FALSE Allow to bid 2 TRUE FALSE FALSE Get more data 1 TRUE FALSE TRUE Allow to bid 8 3 2 FALSE FALSE FALSE TRUE FALSE FALSE FALSE TRUE FALSE TRUE TRUE FALSE TRUE TRUE TRUE FALSE TRUE TRUE TRUE FALSE TRUE FALSE TRUE TRUE