Download Excel Formulas: Logical Functions, Comparative Operators, and Conditional Formatting and more Exercises MS Microsoft Excel skills in PDF only on Docsity!
Here is a list of what a cell can contain: TEXT, NUMBER, TRUE or FALSE, Blank cell, FORMULA, Blank from formula, err 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 function (2 up to 255 logical tests that if at least one is TRUE then the OR function delivers a TRUE to the cell NOT function converts TRUE to FALSE and FALSE to TRUE 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) V List of what can go in a cell TEXT rad NUMBER 4. 1 1 0 0 Error from formula #DIV/0! Logical Question No. No. Is 15=15.1? 15.0 15.1 0 = Is 15<>15.1? 15.0 15.1 1 <> Is 15>15? 15.0 15.0 0 > Is 15>=15? 15.0 15.0 1 >= Is 15<15? 15.0 15.0 0 < Is 15<=15? 15.0 15.0 1 <= Customer Credit Score 3 Customer Last Year Sales $ 500,000. Company Credit Score Hurdle 3 Company Last Year Sales Hurdle $ 1,000,000. TRUE FALSE logic formulas in Excel like: =B23=C23. This formula asks the question is the content of B23 the same as C because 15 <> 15.1. Case in words is not considered. For example Case = case is TRUE to Excel. 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 IFERROR function looks to see if there is an error from a formula. If there is an error it puts something in the cell, otherw calculate.
Logic, TRUE FALSE
formulas, IS & IF
functions
Logical Formula Comparative Operator
0 AND
1 OR
ISTEXT function rad 1 1 Value refers to text. ISTEXT function 23 0 0 ISNUMBER function 12 1 1 Value refers to a number. ISNUMBER function Rad 0 0 ISLOGICAL function 1 0 0 Value refers to a logical value. ISBLANK function 1 1 Value refers to an empty cell. ISERROR function #REF! 1 1 Value refers to any error value (#N/A, #VALU ISERR function #N/A 0 0 Value refers to any error value except #N/A. ISNA function #N/A 1 1 Value refers to the #N/A (value not available) ISNONTEXT function Excel 0 0 Value refers to any item that is not text. (Not This example puts one of two words (text) into a cell Big List 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. Do you get a bonus? You get Bonus if you sell $6,000.00 or more Rule 1: We have two logical tests and both must be met before we extend credit: AND function 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 IF function can put one of two things in a cell. The IF function has three parts:
- Logical Test
- Value If True
- 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 Text or Number Formula 1 Text or Number Formula 2
nk cell, FORMULA, Blank from formula, error from formula , >, >=, <, <= 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 parts: 1) Logical Test, 2) Value If True, 3) Value If False Comparative Operator Equal Not Equal Great Than Greater Than Or Equal To Less Than Less Than Or Equal To tion is the content of B23 the same as C23? The answer is 0 example Case = case is TRUE to Excel. ons: ISTEXT function, ISNUMBER function, ISLOGICAL function, SNONTEXT function rror it puts something in the cell, otherwise it lets the formula
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.)
Rule 1 no over due balance Rule 2 Credit hurdles must be met Formula Inputs Yes 2.5 4.5 200,000 50,000 Yes Customer Credit Analysis For Accounts Receivable Rating1 Rating Cust01 5 7.4 208,339 90,550 1 Cust02 5 5.9 374,775 51,255 Yes 0 Cust03 3.3 3.9 371,040 56,241 1 Cust04 Yes 1.2 2.4 331,439 69,920 0 Cust05 Yes 3.4 5.3 336,505 58,534 Yes 0 Cust06 4.5 6 336,794 60,423 Yes 0 Cust07 4.4 2.4 500,000 49,850 0 Cust08 2.9 5.5 375,800 62,100 1 Cust09 2.5 8.9 254,888 75,000 1 Account Not Suspended, No Overdue Balance and Last Year's Sales > $50,000. Account Not Suspended and Both Credit Hurdles must be met and at least 1 of the remaining criteria must be met Custome r Name Accou nt Suspe nded? Rating Hurdle Rating Hurdle Asset Value Last Year's Sales Over Due Balan ce Custome r Name Accou nt Suspe nded? Asset Value Last Year's Sales Over Due Balan ce Rule 1 Account Not Suspended, No Overdue Balance and Last Year's Sales > $50,000.
last year sales > 50000 At least one of the remaining criteria must be met r Accounts Receivable Decision 1 0 0 APPROVED 0 1 0 0 APPROVED 1 0 0 0 TAKE A SECOND LOOK 0 0 1 1 REJECTED 0 0 1 1 REJECTED 0 1 0 0 APPROVED 1 0 1 1 REJECTED 0 1 0 0 APPROVED 0 1 0 0 APPROVED 0 Rule 2 Account Not Suspended and Both Credit Hurdles must be met and at least 1 of the remaining criteria must be met
NONE
Fail Both Rules ONLY: Two rules are false, but Asset Hurdle Met
r Accounts Receivable Decision 1 0 0 Approved 1 0 0 Approved 0 0 0 Take A Second Look 0 1 1 Reject 0 1 1 Reject 1 0 0 Approved 0 1 1 Reject 1 0 0 Approved 1 0 0 Approved Rule 2 Account Not Suspended and Both Credit Hurdles must be met and at least 1 of the remaining criteria must be met None Fail Both Rules ONLY: Two rules are false, but Asset Hurdle Met
Customer Asset Value Not Suspended Customer 01 4 9 $159,441 $332, Not Suspended Customer 02 4 6 $796,571 $455, Not Suspended Customer 03 2 3 $1,243,611 $373, Suspended Customer 04 3 3 $4,939,327 $285, Not Suspended Customer 05 2 7 $2,647,055 $191, Not Suspended Customer 06 2 2 $1,409,633 $168, Not Suspended Customer 07 3 8 $4,350,814 $353, Not Suspended Customer 08 4 9 $2,395,694 $198, Not Suspended Customer 09 0 3 $1,629,030 $199, Not Suspended Customer 10 3 8 $207,371 $189, Suspended Customer 11 0 5 $4,857,701 $347, Not Suspended Customer 12 3 4 $1,968,563 $90, Not Suspended Customer 13 3 8 $3,183,148 $169, Not Suspended Customer 14 2 7 $3,898,837 $238, Not Suspended Customer 15 0 9 $4,643,757 $439, Assumptions Credit Criteria 4 Past Year's Sales 2 4.5 $200,000 $50,000 0 Low 0 0 High 4 10 Is Account Suspended? Credit Rating 01 Credit Rating 02 Past Year's Sales Credit Criteria 1 Credit Criteria 2 Credit Criteria 3 Credit Criteria 5 Hurdle Credit Rating 01 Hurdle Credit Rating 02 Hurdle Asset Value Current Over Due Balance
Customer Asset Value 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 2 4.5 $200,000 $50,000 0 Suspended Low 0 0 Not Suspended High 4 10 Is Account Suspended? Credit Rating 01 Credit Rating 02 Past Year's Sales Current Over Due Balance Credit Criteria 1 Credit Criteria 2 Credit Criteria 3 Credit Criteria 4 Credit Criteria 5 Hurdle Credit Rating 01 Hurdle Credit Rating 02 Hurdle Asset Value Past Year's Sales Current Over Due Balance Is Account Suspended?
Rule 1 Rule 2 Rule 1 Not Suspended, No Overdue Balance and Past sales > $50,000. Rule 2 Both Credit Hurdles must be met and at least 1 of the remaining criteria must be met
NONE: 2
rules not met) ONLY: Two rules are false, but Asset Hurdle Met Not Suspended, No Overdue Balance and Past sales > $50,000. Both Credit Hurdles must be met and at least 1 of the remaining criteria must be met
Taxable Earnings Deductions SS Medicare SS Medicare Pension Total Ded. Net Pay Expense 414.15 - 6.01 8.28 14.29 399.86 414. 750.00 - 10.88 15.00 25.88 724.12 750. 874.00 - 12.67 17.48 30.15 843.85 874. 880.00 - 12.76 17.60 30.36 849.64 880. 768.00 - 11.14 15.36 26.50 741.50 768. 680.00 - 9.86 13.60 23.46 656.54 680. 481.00 - 6.97 9.62 16.59 464.41 481. 965.25 - 14.00 19.31 33.31 931.94 965. 544.00 - 7.89 10.88 18.77 525.23 544. 919.22 - 13.33 18.38 31.71 887.51 919. 627.00 - 9.09 12.54 21.63 605.37 627. 793.65 - 11.51 15.87 27.38 766.27 793. 702.00 - 10.18 14.04 24.22 677.78 702. 352.00 - 5.10 7.04 12.14 339.86 352. 540.00 - 7.83 10.80 18.63 521.37 540. 460.65 - 6.68 9.21 15.89 444.76 460.
- 10,750.92 - 155.90 215.01 370.91 10,380.01 10,750. Assumptions SS Medicare Pension 6.20% 1.45% 2% SS Ceiling 105, CURRENT Pay check 23100 9000 6,900 6900 5, 5, 2, 50, 5,
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. Employee 5 32 24.00 768.00 6,999.00 7,767.00 1. 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. Employee 14 32 11.00 352.00 6,850.76 7,202.76 149. 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. Assumptions UnEm State UnEm Fed 5.40% 0.08% UnEm Fed Ceiling 7, 106,000 1, 105, 82,900 22, 73,900 23, 67, 62, 57, 55, 5,
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% VLOOKUP to Replace IF 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 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. 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% VLOOKUP to Replace IF 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% Put 1 of 3 Percentages in a Formula Mutually Exclusive Categories 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. Mutually Exclusive Categories
