Instruction : From cell C6 to F13, kindly fill up any number. Sample number values are given in C4 TO F5 and E11 value retain as it is
A. Using Functions, perform the following:
NO CRITERIA Mark
1 In G4:G13, get the Total for each year by using an appropriate function. 1
2 In the Status column, H4:H13, if the Total is greater than 500 display “HIGH”; otherwise, display “LOW”. 1.5
3 In Region Code column I4:I13, join the Region_Name (B column) and Order_Priority (C column) by using an appropriate function in given format Region_name order_priority for example “Africa_AF” 1
4 Find the Average of 2016 in C15 1
5 Find the Integer of the Average of 2016 in C16 1
6 Find the Roman equivalent of the “Integer of Average of 2016” in C17 1
7 Find the number of blanks from 2016 to 2019 (column C to Column F)and display in
F18 1
8 Search for the Region Name Middle_East from the table and find out the corresponding 2018 value using the appropriate function. Display the result in C19. 1.25
9 Find the total sales of Africa in 2018 where Region_Name is Africa and display it in E20. 1.25
10 Display the second lowest sales in 2016 in C21 1.25
11 Find the most frequent sales value for 2018 and display the results in E22 1
12 Find the Standard Deviation of sales in 2019 and display the result in F23 1
B. Perform the following. (Functions may not be used)
13 Find the result under Sales £ for each Region by dividing “Total” with Density given in J14 (use absolute reference) and display the results in J4:J13 in pound (£) format. 1.5
14 Type your FirstName_ID in G24. Create a link to website “https://www.gmail.com”
1
15 Highlight all sales from C4:F13 that are more than or equal to 2000 with red text and fill light green color. 1.25
16 Create a column spark line for each year in the locations C14:F14 1.25
17 Define a name “Sales_Total” for the range G4:F13 1.25
SECTION B [15.5 Marks] - In your sheet SECTION B – 1 you can see the data as shown below. Replace the question marks (?) in the Answer column with the appropriate function as provided in the Question column.
[3.75 Marks] - In your sheet SECTION B – 2, you can see the data as shown below. Answer the following using the appropriate function [11.75 Marks]
Instruction : From cell B3 to E8, kindly fill up any number. Sample number given in B2 to E2
No. CRITERIA Marks
a. Apply Sort A1:E8 range based on Items column in Ascending order 1. 5
b. Using AND function, check the condition (D2 is greater than 1500, E2 is less than 10000), and display the result in cell F2 1.25
b. Using OR function, check the condition (B8 is less than 5000, C8 is greater than 1000), and display the result in cell F3 1.25
c. Using NOT function to check the condition (E4 is less than B7) and display the result in cell F4 1
d. Apply Freeze panes in Second Row and Column B 1
e. Perform regression analysis for the given data using Muscat values as Y- Range and Nizwa values as X – Range. Display the normal probability plot in a new sheet.
• Apply the border on Plot area with red color and width 4pt.
• Add Trend line – Exponential.
• Data labels = center.
• Fill the Chart Area with yellow color.
• Chart title “Regional Sales Report”. 5.75
Sample Solution
