(a) Using the IF or IFS function, write a formula in F15 to determine the activity status. Use the As on Date value given in cell E11. Copy the formula to cells F16 to F23.Screenshots attachedthanks ABDF1 You are creating a simple project tracking template to help you understand the current project status on a given date.2 Table 2 has the project activities, the project is going as per the planned start and end date, and there is no deviation.3 Any activity has three statuses, viz. Completed, In-progress, and not started.4 An activity's status is (i) Completed if "As on Date" > End Date (ii) In-progress if Start Date <= "As on Date" and End Date >= "As on Date" (iii) "Not Started" if Start Date > "As on Date".56910(a) Using the IF or IFS function, write a formula in F15 to determine the activity status. Use the As on Date value given in cell E11. Copy the formula to cells F16 to F23.(b) Apply conditional formatting to the status column.(i) If the Status text is Completed, then apply "Highlight Cell Rules", "Text that contains" with "Green Fill with Dark Green Text".(ii) If the Status text is In-Progress, then apply "Highlight Cell Rules", "Text that contains" with "Yellow Fill with Dark Yellow Text".(iii) If the Status text is "Not started" then apply "Highlight Cell Rules", "Text that contains" with Custom format, Grey fill (it can be any grey color of your choice).You are analyzing the budget overrun for the activities; this analysis is not helpful for an activity that is not yet completed; hence you decide to assign "NA" for such activities.An activity has overshot the budget if (i) it is completed and (ii) Actual Amount spent on the activity > Budget Allocated.7An activity is within Budget if (i) it is completed and (ii) Actual Amount spent on the activity < Budget Allocated.8 (c) Using the IF or IFS function, write a formula in 115 to determine if the activity has overshot its budget. Copy the formula to cells 116 to 123.(d) Apply conditional formatting to the Budget status column.(i) If the Budget Status text is Overshot, then apply "Highlight Cell Rules", "Text that contains" with Custom format and fill with "Red Color" (Any red color of your choice).(ii) If the Budget Status text is Within Budget then apply "Highlight Cell Rules", "Text that contains" with Custom format and fill with "Green Color" (Any green color of your choice).1112GAs on DateH2/12/2021KLM 910111213ABDEFHJYou are analyzing the budget overrun for the activities; this analysis is not helpful for an activity that is not yet completed; hence you decide to assign "NA" for such activities.An activity has overshot the budget if (i) it is completed and (ii) Actual Amount spent on the activity > Budget Allocated.An activity is Within Budget if (i) it is completed and (ii) Actual Amount spent on the activity < Budget Allocated.78 (c) Using the IF or IFS function, write a formula in 115 to determine if the activity has overshot its budget. Copy the formula to cells 116 to 123.(d) Apply conditional formatting to the Budget status column.(i) If the Budget Status text is Overshot, then apply "Highlight Cell Rules", "Text that contains" with Custom format and fill with "Red Color" (Any red color of your choice).(ii) If the Budget Status text is Within Budget then apply "Highlight Cell Rules", "Text that contains" with Custom format and fill with "Green Color" (Any green color of your choice).1415161718192021222321As on DateActivity Activity descriptionABсDEFGHIDetermine user needsReview software and languagesPurchase softwareDesign format and style.Write programsReview outcome product with userMake revisionsSelect server siteInstall on server and test2/12/2021Table 2: Activity Status of the projectStart Date2/1/20212/1/20212/3/20212/4/20212/7/20212/12/20212/13/20212/13/20212/15/2021End Date2/3/20212/3/20212/4/20212/7/20212/12/20212/13/20212/15/20212/14/20212/17/2021StatusBudgetAllocated$$$$$ 1,000 $200 $250 $99 $250 $100 $200 $500 $200 $$$$$ActualAmountSpent1201504452501,100100-KBudget StatusL

Required Formula is: =IFS($D$11>E15, "Completed", AND(D15<=$D$11, E15>=$D$11),…

Answered: Using the IF or IFS function, write a…

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE