Bus-299: Excel Hw Need help with specific parts

1. (Last Collum Question) Eligible for scholarship if has words "good" or "excellent" in comment? - SOLUTION #2

2. (Other Page) In a textile company, machine ID incorporates its capacity (in pounds, of fiber produced per batch), what is the company's: Production per Day using MID function (Correct)? Alternate Solution Using RIGHT function (Easier and Correct)?

AutoSave OFF buca Ex-Text, date functions (version 1).xlsb - AutoRecovered Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Calibri (Body) 11 DO ab Wrap Text General VIL 28 O Format V V Sort & Filter Find & Select Analyze Data B D E H J L M N 0 P Q Q R Paste BI U * A = = = CEE Merge & Center $ % % Conditional Format Cell OOO Insert Delete Formatting as Table Styles IF x , ) fx =OR(NOT ISERROR SEARCH "Good", M18)),NOT ISERROR SEARCH "Excellent",M18)))) A F I 6 You need to extract following information from this using text and date functions. 7 Lastname - Just the last name of student 8 FirstName - Just the First name of student Last, First - Formatting so that there is a comma and a space between lastname and firstname. For example, Bauer, Julio Last 4 digits of class ID - Extracting last four digits from a student's class ID. Student's birthday today?- It should say "Happy birthday" ifit is a student's birthday today, otherwise should say "Not yet". 12 In Cell 115, using an Excel formula put the current date so everyday the day would be current. 13 In column "Eligible for scholarship if has word "good"in comment?", a student should be awarded scholarship if they have a "good"comments in their personnel file. Hint: Use ISERROR function also. 14 In column "Eligible for scholarship if has words "good" or "excellent" in comment?", a student should be awarded scholarship if they have a "good" or "excellent comments in their personnel file. Hint: Use ISERROR functions with AND function. 15 2/28/22 16 Date of birth (DoB) 3/1/00 1/25/97 9/9/99 9/14/03 2/9/01 9/9/03 10/30/99 9/14/00 12/23/02 17 CLASSID 18 XB058071 19 CY52246212 20 A27068035 21 OC6412003 22 DS2496169 23 BR4460409 24 AZ7716181 25 F53335848 26 LT3200109 27 28 29 30 31 Name Bauer, Julio Brumbler, Marc Clarkson, Reza Cronkite, Chris David, Leopold Eagad, Chris Goel, Sargam Leung, Xixi Sharma, Madhu Last Name LastName using Using MID LEFT function function Bauer Bauer Brumbler Brumbler Clarkson Clarkson Cronkite Cronkite David David Eagad Goel Goel Leung Leung Sharma Sharma First Name First Name using RIGHT Using MID function function Julio Julio Marc Marc Reza Reza Chris Chris Leopold Leopold Chris Chris Sargam Sargam XXI Xixi Madhu Madhu Last, First Bauer Julio Brumbler, Marc Clarkson, Reza Cronkite, Chris David, Leopold Eagad, Chris Goel,Sargam Leung Xixi Sharma, Madhu Last 4 digits of class ID 8071 6212 8035 2003 6169 0409 6181 5848 0109 Month of DoB 3 1 9 9 2 9 10 9 12 Is student's birthday today? FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE FALSE Is student at Comments on least 18 years students old? performance TRUE I Good, hard-working TRUE Excellent TRUE Good, but lazy TRUE Average student TRUE Excellent, good athlete TRUE Average student TRUE Good student TRUE Excellent student TRUE Good, hard working Step 2: use Eligible for scholarship if SERROR function has word "good" in as condition in IF comment? function Step 1: use SERROR to see if the word "Good" is Eligible for Eligible for scholarship if Eligible for scholarship if in comments or not. scholarship if has has words "good" or has words "good" or ISERROR is case word "good" in excellent" in comment? "excellent" in comment? insensitive. comment? SOLUTION 11 SOLUTION 12 TRUE Eligible "Excellent" M18)) FALSE Not Eligible TRUE TRUD Eligible TRUE FALSE Not Eligible FALSE TRUE Eligible TRUE FALSE Not Eligible FALSE TRUE Eligible TRUE FALSE Not Eligible TRUE TRUE Eligible TRUE Eagad solution 1=ORINOT(ISERROR(SEARCH("Good", M18))),NOT(ISERRG 32 33 34 35 36 37 38 39 40 41 42 43 44 45 Exercise Text Functions Date Functions Early Retirement Homework Name Logic HW Practice2 + Edit 90% AutoSave OFF ACO Ex- Text, date functions Q go Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Calibri (Body) v LG 11 . - A = = ab Wrap Text General A Mo Ayu Ou F Paste BI U A E Insert V Merge & Center Delete $ % ) Format V V .00 0 .00 Conditional Format Cell Formatting as Table Styles Sort & Filter Find & Select Analyze Data P18 X fx =OR(NOT(ISERROR(SEARCH("Good", M18))), NOT(ISERROR(SEARCH("Excellent", M18)))) I T J K L M N 0 Q R S T U V W X Y Z 1 1 2 3 4 students. Eligible for scholarship if has words "good" or "excellent in comment? - SOLUTION #2 6 7 8 8 9 Julio 10 11 ot yet". 12 13 y have a "good" comments in their personnel file. Hint: Use ISERROR function also. 14 holarship if they have a "good" or "excellent" comments in their personnel file. Hint: Use ISERROR functions with AND function. a 15 Step 2: Use Eligible for scholarship if ISERROR function 2/26/22 has word "good" in as condition in IF 16 comment? function Step 1: use ISERROR to see If the word "Good" is in " Eligible for Eligible for scholarship if Is student's Is student at comments or not. scholarship if has has words "good" or Last 4 digits Month of birthday least 18 years Comments on ISERROR is case word "good" in "excellent" in comment? 17 Last, First of class ID DoB today? old? old students performance insensitive. comment? SOLUTION #1 18 Bauer, Julio 8071 3 FALSE TRUE FALSE TRUE Good, hard-working TRUE Eligible TRUE 19 Brumbler, Marc 6212 1 1 FALSE TRUE Excellent FALSE Not Eligible TRUE 20 Clarkson, Reza 8035 9 03 FALSE TRUE TRUE Good, but lazy A TRDE Eligible TRUE TRUE 21 Cronkite, Chris 2003 2005 9 3 FALSE TRUE FALSE Average student FALSE FALSE FALSE 22 David, Leopold FALSE D10S 6169 2 FALSE TRUE TRUE Excellent, good athlete 4 TRUE Eligible TRUE TRUE 23 Eagad, Chris 0409 9 FALSE TRUE Average student FALSE Not Eligible FALSE 24 Goel,Sargam 6181 10 FALSE TRUE Good student TRUE Eligible TRUE 25 Leung,xixi 5848 9 FALSE TRUE Excellent student FALSE Not Eligible TRUE 26 Sharma, Madhu 0109 12 FALSE TRUE Good, hard-working TRUE Eligible TRUE 27 28 28 29 29 30 31 32 33 34 35 Not Eligible Exercise Text Functions Date Functions Early Retirement Homework Name Logic HW Practice2 + Ready Count: 9 100% AutoSave OFF D Ex- Text, date functions Q go Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments DI Calibri (Body) v 11 LG ~ = = = V General ab Wrap Text ' F IN Analyze Data Paste Insert Av BI Delete V Merge & Center $ %) Format V V .00 0 .00 Conditional Format Cell Formatting as Table Styles Sort & Filter Find & Select D11 fx =C11*B11 = A B C D E F G . H T J K L L M N O P P 25 1 2 In a textile company, machine ID incorporates its capacity (in pounds, of fiber produced per batch). 3 For example, machine ID of 1BBV 250 means that this machine can produce 250 pounds of fiber, per batch. 4 Joey has been asked by the Production Manager to calculate the total production per day by each machine. Joey knows the Machine ID (column A) and 5 how many batches of fiber are produced each day by each machine (column B). 6 Joey, however, did not have any experience with data analysis and has wrong calculations in column C and D. 7 We have to calculate the correct production output in pounds per day by each machine. We also have to calculate the total production capacity (in pounds) of this textile factory. 8 8 9 Total Factory Capacity in Pounds = 0 Machine capacity in Production per Production per Day Alternate Solution Using Machine ID Batches pounds per batch Day (Wrong using MID function RIGHT function (Easier and 10 Number Per Day (Wrong analysis) analysis) (Correct) Correct) 11 1BBV 250 4 25 100 12 2BBV 250 4 100 13 3BBV 500 4 50 200 14 4BBV 500 4 50 200 15 5BBV 750 3 75 225 16 6BBV 750 3 225 17 1MCBV 150 5 15 75 18 2MCBV 525 4 52 208 19 3MCBV 525 4 52 208 20 1MCPV 1100 3 110 330 21 2MCPV 1100 3 110 330 22 3MCPV 1550 155 465 23 4MCPV 1550 3 155 465 24 5MCPV 1550 3 155 465 25 6MCPV 2000 2 200 400 26 7MCPV 2000 2 200 400 27 SMCPV 5000 1 500 500 28 1YDFBV 550 4 55 220 29 2YDFBV 550 55 220 30 YDERV.550 220 75 3 4 4 55 Exercise Text Functions Date Functions Early Retirement Homework Name Logic HW Practice2 + Ready 3 130% AutoSave OFF D Ex- Text, date functions Q go Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Calibri (Body) v 11 LG ~ = = = ab Wrap Text General Ayu O FO IN Analyze Data Paste v Av BI E Insert E E V Delete Merge & Center $ $ % ) Format V .00 0 V .00 Conditional Format Cell Formatting as Table Styles Sort & Filter Find & Select D11 A x fx fx =C11*B11 B C D E F G H T j K L L M N O P P 4 208 4 52 52 110 208 3 330 3 110 330 3 155 465 3 465 155 155 3 465 2 200 400 2 200 400 1 500 500 4 55 220 4 55 220 4 55 220 366 3 122 3 122 366 A 18 2MCBV 525 19 3MCBV 525 20 1MCPV 1100 21 2MCPV 1100 22 3MCPV 1550 23 4MCPV 1550 24 5MCPV 1550 25 6MCPV 2000 26 7MCPV 2000 27 8MCPV 5000 28 1YDFBV 550 29 2YDFBV 550 30 3YDFBV 550 31 1YDFPV 1225 32 2YDFPV 1225 33 1GDBV 200 34 2GDBV 200 35 3GDBV 750 36 4GDBV 750 37 5GDBV 1200 38 6GDBV 1200 39 7GDBV 1200 40 1GGFB 200 41 2GGFB 1400 42 3GGFB 1400 43 4GGFB 2500 44 5GGFB 2500 45 6GGFB 5000 46 7GGFB 5000 47 48 49 4 20 80 4 20 80 3 75 225 3 75 225 3 120 360 3 360 120 120 3 360 5 20 4 140 4 140 250 3 100 560 560 750 750 1000 1000 3 250 2 500 2 500 Exercise Text Functions Date Functions Early Retirement Homework Name Logic HW Practice2 + Ready 3 130%