HB473_2-2_IF_SEP9 - I F Funct ion ♦ Use d to e va lua te...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: I F Funct ion ♦ Use d to e va lua te d a ta a nd p ro vid e o utp ut ♦ O utp ut is d e fine d b a se d o n lo g ic a l te st ♦ Busine ss m a na g e rs o fte n use IF func tio n to hig hlig ht ke y sta tistic s re la te d to their a re a o re sp o nsib ility. ♦ The inventory control manager of a candy company m be required to maintain at least 10,000 pounds of eac ingredient used to produce a line of chocolate bars. e o utp ut l te st c tio n to eir a re a o f company might unds of each late bars. ♦ Use s thre e a rg um e nts: I F Funct ion ♦ logical_test ♦ is used for evaluating the data in a cell location based o test that you define; the results of this test will be eithe or false. A basic logical test usually starts with a cell re followed by a comparison operator. Examples of logical B9 > 25; H7 < D12; L8 = “ Car” ; D17 < (a9­ 12) * 10. ♦ [value_if_true] ♦ used for defining the output of the I F function if the res the logical test are true. This argument can be defined cell reference, formula, function, number or words. As logical_test argument, if you are going to use words, yo put them in quotation marks. ♦ [value_if_false] ♦ used to define the output of the function if the results o logical test are false. The options for defining this argu are identical to the [ value_if_true] argument. ation based on a t will be either tr ue with a cell reference les of logical tests: 9­ 12) * 10. tion if the results of n be defined with a or words. As in the use words, you must f the results of the ing this argument nt. I F Funct ion ♦ C o m p a riso n o p e ra to rs ♦ ♦ ♦ ♦ ♦ ♦ = Equal to > Greater than < Less than <> Not equal to >= Greater than or equal to <= Less than or equal to Item Description Sugar Cocoa Beans Cocoa Butter Peanuts Almonds Starch Inventory Pounds 10000 7500 15000 8000 5000 12500 Status I F Funct ion: Pract ice 1. Activate cell C2 5. Type “OK” to de argument. 2. Type an equal sign, the function name IF, and an open parenthesis ­ If the value in ce 10000, the messa 3. Type B2<10000 to define the logical­ in cell C2. test argument. 6. Type a closing p ­ Test if the value in cell B2 is less than the Enter Key. 10000. ­ Assume that inventory control manager 7. Copy cell C2, wh completed IF fu is maintaining at least 10000pounds of cells C3 through inventory for each item. ­ Since the cell re 4. Type “Low INV” to define the the logical_test ar [value_if_true] argument. automatically adju ­ If the value in cell B2 is less than 10000, number due to ral the message LOW INV will be displaeyd in cell C2. ­ Text message: quotation mark ♦ An inve nto ry c o ntro l m a na g e r c o uld use the IF w o rkshe e t to id e ntify item s in w hic h the inve nto 10,000 p o und s. ♦ Ho w to d o : Pract ice o uld use the IF func tio n in the ic h the inve nto ry is less tha n . Type “OK” to define [value_if_false] argument. ­ If the value in cell B2 is not less than . Type a closing parenthesis and press the Enter Key. . Copy cell C2, which now contains the completed IF function, and paste it to cells C3 through C7 ­ Since the cell reference B2 was used in 10000, the message OK will be displayed in cell C2. the logical_test argument, it will automatically adjust to the appropriate row number due to ralative referencing. Item Description Sugar Cocoa Beans Cocoa Butter Peanuts Almonds Starch Inventory Pounds 10000 7500 15000 8000 5000 12500 Status I F Funct ion: Pract ice ♦ IF func tio n is use d to c a lc ula te ho w m uc h inve nto ry b e p urc ha se d if a n ite m is b e lo w the 10000 p o und s t ♦ Ho w to d o : 1. Logical test: B2<10000. 2. [value_if_true]: 10000 ­ B2 I f the value in cell B2 is less than 10000, the functio calculate how many pounds need to be purchased t 10000 pound target. 3. [value_if_false): 0 I f the value in cell B2 is at or above 10000, the func display a value of 0, indicating there is no need to p additional inventory. a ct ice uc h inve nto ry ne e d s to 10000 p o und s ta rg e t. 000, the function will be purchased to reach the 10000, the function will e is no need to purchase Nest ed I F Funct ions ♦ IF func tio n p ro vid e s the e ntry o f o ne lo g ic a l te st a n o utp ut (va lue if true o r va lue if fa lse ) ♦ Ne ste d IF func tio ns a re use d w he n the func tio n sho d isp la y o ne o f m a ny p o ssib le outp uts ♦ O ne o f fo ur o utp uts a re ne e d e d : ♦ ♦ ♦ ♦ < 5000 display “Prod Risk” > 5000 and < 10000 display “Low” > 14000 display “Spoil Risk” Between 10000 and 14000 display “OK” ♦ Ne e d to c rea te a n IF func tio n w ith thre e lo g ic a l te ne ste d IF func tio n. ns ic a l te st a nd o ut unc tio n sho uld lo g ic a l te sts: a Item Description Sugar Cocoa Beans Cocoa Butter Peanuts Almonds Starch Inventory Pounds 10000 7500 15000 8000 1200 12500 Status Nest ed I F Funct ion: I nv ent or y ♦How to do: 1. Type B2<5000 to define the logical_test argument. 2. Type “Prod Risk” to define to define the [value_if_true] argument. 3. Type the function name IF followed by an open parenthesis to define [value_if_false] argument. 4. Type B2<10000 to define the logical_test argument of the second IF function. 5. Type “Low” to define the [value_if_true] argument of the second IF function. 6. Type the function nam an open parenthesis [value_if_false] argu second IF function. 7. Type B2>14000 to de logical_test argumen function. 8. Type “Spoil Risk” to d [value_if_true] argum function. 9. Type “OK” to define [value_if_false] argu IF function. n v ent or y Dat a pe the function name IF followed by open parenthesis to define the alue_if_false] argument of the cond IF function. pe B2>14000 to define the gical_test argument of the third IF nction. pe “Spoil Risk” to define the alue_if_true] argument of the third IF nction. pe “OK” to define the alue_if_false] argument of the third function. Item Description Sugar Cocoa Beans Cocoa Butter Peanuts Almonds Starch Inventory Pounds 10000 7500 15000 8000 1200 12500 Status The Set up of t he Nest ed I F Fun I nv ent or y Dat a IF (B2<5000, “Prod Risk”, IF (B2<10000, “Low”, IF (B2>14000, • A new IF function is started in the [value_if_f argument for the purpose of conducting multiple • Both [value_if_true] and [value_if_false] be used to add multiple logical tests. argu • The logical tests are in a sequence from lowe • It is important to note that a closing parenthes for every IF function you started (Three closing each item. • Nested IF function displays one of four potent t ed I F Funct ion: Dat a ”, IF (B2>14000, “Spoil Risk”, “OK”))) he [value_if_false] ucting multiple logical tests. if_false] arguments could ts. ce from lowest to highest. ing parenthesis is required hree closing parentheses). of four potential outputs for Logical Funct ions: AND OR ♦ Also use d to eva lua te d a ta b a se d o n lo g ic ♦ C a n b e use d ind e p e nd e ntly ♦ AND (B3=“NJ”, B3=“NY”, B3=“PA”) ♦ Ofte n use d in the lo g ic a l te st a rg um e nt o f a Func tio n ♦ If (AND(B3>1, D3>=12)=True, “good”, “bad” ND OR o n lo g ic a l te sts ) m e nt o f a n IF od”, “bad”) AND Funct ion: Cust om er Dem ogr aphic Dat ♦ In m a ny situa tio ns, b usine ss m a na g ers m ust d e c id e g ro up o f c o nd itio ns a p p ly to a sp e c ific a sp e c t o f th b usiness. ♦ Custo m e r d e m o g ra p hic d a ta fro m a c o m p a ny tha t se e king to se ll a new p ro d uc t to p e o p le w ho a re fe ha ve c hild ren, a nd a re le ss tha n 45 ye a rs o ld . ♦ Relevant for companies that make specialized products for specific target customer. ♦ The marketing manager of this firm would probably find it challenging to identify these people from a list of 100,000 p customers. ♦ AND function can automatically evaluate and highlight cus that meet all of the characteristics targeted by this product. ♦ AND function will be used to identify the target customers. ic Dat a ust d e c id e if a sp e c t o f the ir d products for a p a ny tha t is w ho a re fem a le , o ld . bably find it t of 100,000 potential highlight customers this product. t customers. Customer Detail Information Name Customer 1 Customer 2 Customer 3 Customer 4 Customer 5 Customer 6 Customer 7 Customer 8 Customer 9 Customer 10 Customer 11 Customer 12 Customer 13 Customer 14 Customer 15 State of Residence CA MO TX NJ FLA PA GA NH NY CO NJ NM NY VA PA Gender F M F M M F F F M F M F F M M Age 22 44 65 73 29 26 34 54 36 41 30 24 28 44 52 Children NO YES YES NO YES YES NO YES NO YES YES NO YES NO YES Target Customer AND Funct ion ( cont .) ♦ AND (C3=“F”, D3<45, E3=“Yes”) ♦ AND (lo g ic a l1, lo g ic a l2, lo g ic a l3, lo g ic a l4… ♦ And func tio n w ill p ro d uc e o ne o f tw o p o ssib o utp uts. ♦ If all logical tests are true, the function will display the TRUE. ♦ IF ANY of the logical tests are false, the function will display the word False. n t .) g ic a l4……) tw o p o ssib le ll display the word unction will Customer Detail Information Name Customer 1 Customer 2 Customer 3 Customer 4 Customer 5 Customer 6 Customer 7 Customer 8 Customer 9 Customer 10 Customer 11 Customer 12 Customer 13 Customer 14 Customer 15 State of Residence CA MO TX NJ FLA PA GA NH NY CO NJ NM NY VA PA Gender F M F M M F F F M F M F F M M Age 22 44 65 73 29 26 34 54 36 41 30 24 28 44 52 Children NO YES YES NO YES YES NO YES NO YES YES NO YES NO YES Target Customer OR Funct ion ♦ M a jo r d iffe re nc e fro m AND Func tio n: ♦ OR (C3=“F”, D3<45, E3=“Yes”) ♦ O R (lo g ic a l1, lo g ic a l2, lo g ic a l3, lo g ic a l4…… ♦ T O R func tio n Is use d w he re the e xiste nc e he o ne c rite rio n is re q uire d to trig g e r a d e c isio n ♦ If any logical test is true, word TRUE will be displayed ♦ A company may have a limited number of states whe and distribute products. ♦ The OR function can be used to identify if a customer the states within the company’s territory. ♦ This information can then be used to distribute marke information or promotional material relating to the com product line. ♦ A target customer is any person who lives in any of th states: NY, PA, or NJ. : OR (B3=“NY”, D3<“PA”, E3= ♦ This information can then be used to distribute marke information or promotional material relating to the com product line. ♦ A target customer is any person who lives in any of th states: NY, PA, or NJ. : OR (B3=“NY”, D3<“PA”, E3= ic a l4……) be displayed o n: e xiste nc e o f just a d e c isio n. f states where it can sell f a customer lives in one of ribute marketing ng to the company’s s in any of these three <“PA”, E3=“NJ”) ribute marketing ng to the company’s s in any of these three <“PA”, E3=“NJ”) Com bining AND, OR, and I F Fun ♦ T te c hniq ue o f c o m b ining the AND, O R a he IF func tio ns c a n b e used to te ll the m a rke tin m a na g e r w ha t p ro m o tio ns sho uld b e se nt t e a c h c usto m e r. ♦ The example assumes that a customer could receive of two discount coupons. ♦ 50% off coupon for customers who have been purchasing products from the company for more t one year and have not made a purchase in the las months. ♦ 10% off promotional coupon for all others. I F Funct ions ND, O R a nd m a rke ting b e se nt to uld receive one ve been y for more than se in the last 12 hers. Customer Reward Progam Name Customer 1 Customer 2 Customer 3 Customer 4 Customer 5 Customer 6 Customer 7 Customer 8 Customer 9 Customer 10 Customer 11 Customer 12 Customer 13 Years Purchasing 3 1 8 1 2 12 0 1 4 8 20 14 12 LY Spend $500 $1,200 $200 $$400 $1,800 $$125 $1,575 $450 $200 $$1,100 Months Since Last Purchase 4 2 10 3 1 5 2 6 2 7 9 24 2 Com bining ( cont .) ♦ T e B3>1 to d efine the first lo g ic a l test o f the AND func tio yp ♦ T e D3>=12 to d e fine the sec o nd lo g ic a l test o f the AND yp ♦ T e = T yp RUE fo llo w ed b y a c o m m a to c o m p lete the lo g ic a a rg um e nt o f the IF func tio n. IF (AND(B3>1, D3>=12)=T RUE, “Plea se Come Ba ck 50 “Customer Apprecia tion 10% off”) ♦ T e “ Plea se c o m e b a c k 50% o ff ” to d efine the [ va lue _if_ yp a rg um e nt o f IF func tio n. ♦ The logical test of the IF function will now evaluate if the output of is true. ♦ Quotation marks are not placed on the word TRUE. ♦ TRUE is an output of the AND function and not a text messag been types into a cell. ♦ T e “ Custo m e r a p p rec ia tio n 10% o ff ” to d e fine the [ va lu yp a rg um e nt o f IF func tio n. ♦ If the logical test of the IF function is true, then the output of the AN true. ♦ T e “ Custo m e r a p p rec ia tio n 10% o ff ” to d e fine the [ va lu yp a rg um e nt o f IF func tio n. true. Progam Customer Reward ining ( cont .) t lo g ic a l test o f the AND func tio n. sec o nd lo g ic a l test o f the AND func tio n. c o m m a to c o m p lete the lo g ic a l_te st . RUE, “Plea se Come Ba ck 50% Off”, 10% off”) ction will now evaluate if the output of the AND function ced on the word TRUE. e AND function and not a text message or word that has 0% o ff ” to d efine the [ va lue _if_true ] nction is true, then the output of the AND function is io n 10% o ff ” to d e fine the [ va lue_if_fa lse ] io n 10% o ff ” to d e fine the [ va lue_if_fa lse ] ...
View Full Document

Ask a homework question - tutors are online