Excel Question
Question 1 NameCourseGenderHeight (in cm)Weight (in kg)Marks MaryMBA BAFemale1976249 RobertMBA SHRMale11311539Question: Using Conditional Formatting, highlight those rows meeting all the following conditions: 1. Gender is Female 2. Height (in cm) is less than 100 3. Weight (in kg) is more than 85 Note: No marks will be given if the student is not writing formula. ElizabethMBA BAMale132125Absent JenniferMBA BFEFemale16212171 CharlesMBA LSMFemale1825740 LisaMBA SHRFemale17111938 DanielMBA LSMMale13611759 DonaldMBA BFEMale198113Absent GeorgeMBA LSMMale19210461 DonnaMBA LSMMale17310597 CarolMBA BAMale1506347Formula applied while applying Conditional Formatting RuthMBA LSMMale1367062 JasonMBA BFEMale199119Absent MatthewMBA SHRMale1667471 JessicaMBA BFEFemale1439852 GaryMBA BAMale1229183 JoseMBA BFEFemale18210264 BrendaMBA LSMMale15011592 FrankMBA SHRMale1907594 KathleenMBA BAMale188110Absent JoshuaMBA LSMFemale1468831 DebraMBA SHRMale1826884 JerryMBA BFEFemale1906849 AaronMBA LSMMale1585659 CarlosMBA SHRMale11312193 MarilynMBA BAFemale128101Absent Question 2 NameCourseGenderHeight (in cm)Weight (in kg)Marks MaryMBA BAFemale1976249 RobertMBA SHRMale11311539Question: Calculate the following using COUNTIF(), COUNTIFS(), SUMIF() and SUMIFS(). 1. Count the number of male students of height less than 150 and weight less than 100. 2. Sum of the marks of all students of height more than 125 and weight more than 90. 3. Average marks of the class. 4. Average marks of those students who scored more than 80 marks. Note: No marks will be given if the student is not writing formula. ElizabethMBA BAMale151125Absent JenniferMBA BFEFemale16212171 CharlesMBA LSMFemale1825740 LisaMBA SHRFemale17111938 DanielMBA LSMMale13611759 DonaldMBA BFEMale198113Absent GeorgeMBA LSMMale19210461 DonnaMBA LSMMale17310597 CarolMBA BAMale1506347 RuthMBA LSMMale1367062 JasonMBA BFEMale199119Absent MatthewMBA SHRMale1667471Formula applied JessicaMBA BFEFemale14398521. Count the number of male students of height less than 150 and weight less than 100.Answer GaryMBA BAMale1229183 JoseMBA BFEFemale182102642. Sum of the marks of all students of height more than 125 and weight more than 90.sumif(D2:D27,>125)+sumif(E4:E27,>90) BrendaMBA LSMMale15011592 FrankMBA SHRMale19075943. Average marks of the class.66.8235294118 KathleenMBA BAMale188110Absent4. Average marks of those students who scored more than 80 marks.Answer JoshuaMBA LSMFemale1468831 DebraMBA SHRMale1826884 JerryMBA BFEFemale1906849 AaronMBA LSMMale1585659 CarlosMBA SHRMale11312193 MarilynMBA BAFemale128101Absent Question 3 NameCourseGenderHeight (in cm)Weight (in kg)Marks LisaMBA SHRFemale17111938 GeorgeMBA LSMMale19210461 JasonMBA BFEMale199119Absent JoseMBA BFEFemale18210264 FrankMBA SHRMale1907594 MarilynMBA BAFemale128101Absent Question: Extract the data from worksheet titled "Question 2" and fill the above table using LOOKUP function only. Note: No marks will be given if the student has not filled the cells using LOOKUP function. Question 4 Product NameNo. of Units SoldMarket Share (%) A1378719% B1300218%Questions 1. Prepare the relevent graph for Number of Units sold of these products. 2. Calculate the market share of these products and make the appropriate graph for the same. C1189816% D1014814% E1382319% F1016414% Total72822 No. of Units Sold ABCDEF137871300211898101481382310164 Market Share (%) ABCDEF0.189324654637334860.178544945208865460.163384691439400180.139353492076570270.189819010738513090.13957320589931613 Question 5 You are required to prepare the loan amortization table on the basis of following information. A farmer has taken a loan of ₹10,00,000 at an annual interest rate of 6% for 5 years. He promises to repay the principal amount along with the interest on quarterly basis. Payment is made at the end of every quarter. Create a Loan Amortization Schedule as studied in Class. Amount of Loan1,000,000.00 Years5 Annual Interest Rate6.0000% Period Per Year12 Periodic Interest Rate0.5000%Total Interest$159,968.09 Number of Periods60Total Principal1,000,000.00 Payments$19,332.80Total Payments$1,159,968.09 PeriodPaymentInterestPrincipalBalance 01,000,000.00 1$19,332.805,000.00$14,332.80985,667.20 2$19,332.804,928.34$14,404.47971,262.73 3$19,332.804,856.31$14,476.49956,786.25 4$19,332.804,783.93$14,548.87942,237.37 5$19,332.804,711.19$14,621.61927,615.76 6$19,332.804,638.08$14,694.72912,921.04 7$19,332.804,564.61$14,768.20898,152.84 8$19,332.804,490.76$14,842.04883,310.80 9$19,332.804,416.55$14,916.25868,394.56 10$19,332.804,341.97$14,990.83853,403.73 11$19,332.804,267.02$15,065.78838,337.94 12$19,332.804,191.69$15,141.11823,196.83 13$19,332.804,115.98$15,216.82807,980.02 14$19,332.804,039.90$15,292.90792,687.11 15$19,332.803,963.44$15,369.37777,317.75 16$19,332.803,886.59$15,446.21761,871.54 17$19,332.803,809.36$15,523.44746,348.09 18$19,332.803,731.74$15,601.06730,747.03 19$19,332.803,653.74$15,679.07715,067.96 20$19,332.803,575.34$15,757.46699,310.50 21$19,332.803,496.55$15,836.25683,474.25 22$19,332.803,417.37$15,915.43667,558.82 23$19,332.803,337.79$15,995.01651,563.82 24$19,332.803,257.82$16,074.98635,488.83 25$19,332.803,177.44$16,155.36619,333.48 26$19,332.803,096.67$16,236.13603,097.34 27$19,332.803,015.49$16,317.31586,780.03 28$19,332.802,933.90$16,398.90570,381.13 29$19,332.802,851.91$16,480.90553,900.23 30$19,332.802,769.50$16,563.30537,336.93 31$19,332.802,686.68$16,646.12520,690.81 32$19,332.802,603.45$16,729.35503,961.46 33$19,332.802,519.81$16,812.99487,148.47 34$19,332.802,435.74$16,897.06470,251.41 35$19,332.802,351.26$16,981.54453,269.87 36$19,332.802,266.35$17,066.45436,203.41 37$19,332.802,181.02$17,151.78419,051.63 38$19,332.802,095.26$17,237.54401,814.09 39$19,332.802,009.07$17,323.73384,490.36 40$19,332.801,922.45$17,410.35367,080.01 41$19,332.801,835.40$17,497.40349,582.60 42$19,332.801,747.91$17,584.89331,997.72 43$19,332.801,659.99$17,672.81314,324.90 44$19,332.801,571.62$17,761.18296,563.73 45$19,332.801,482.82$17,849.98278,713.74 46$19,332.801,393.57$17,939.23260,774.51 47$19,332.801,303.87$18,028.93242,745.58 48$19,332.801,213.73$18,119.07224,626.51 49$19,332.801,123.13$18,209.67206,416.84 50$19,332.801,032.08$18,300.72188,116.12 51$19,332.80940.58$18,392.22169,723.90 52$19,332.80848.62$18,484.18151,239.72 53$19,332.80756.20$18,576.60132,663.12 54$19,332.80663.32$18,669.49113,993.63 55$19,332.80569.97$18,762.8395,230.80 56$19,332.80476.15$18,856.6576,374.15 57$19,332.80381.87$18,950.9357,423.22 58$19,332.80287.12$19,045.6938,377.53 59$19,332.80191.89$19,140.9119,236.62 60$19,332.8096.18$19,236.620.00 $1,159,968.09$159,968.09$1,000,000.00