Data Analytics Modelling

Lab Notes (2022-23)(Q1-Q10)

B. Com ( Business Analytics)

Sem III

       Q1: Split the column Deptname-EmpID into two columns

        Step1: Copy the column Deptname – EmpID into new sheet.

        Step2: Select the column

        Step3:  Go to Data tab and click on Text to Columns. A dialogue box opens.

        Step4: Click Next and the select Space and Other. Fill  ‘ – ‘ in other box.

        Step5: Click next.

        Step6: Click finish.

        Q2: Format the Employee Name Column.

        Step1: Copy the Employee Name column in new sheet.

        Step2: In the column next to Employee Name column write the syntax 

             =TRIM(PROPER(A2)) and Enter.

        Step 3: The first letter of every word of the row will become capital and the extra                     spaced will be deleted.

        Step4: Select B1 and drag down.

        Q3: Format Gender Column

        Step1: Copy the Gender column in a new sheet.

        Step2: In the column next to Gender column, write the syntax =UPPER(A2) and                     enter

        Step3: The lower case letters will be converted to uppercase letter.

        Step4: Select B2 and drag down.

        Q4: Format the Date of Hiring to MM/DD/YYYY

        Step1: Copy the date of hiring column to a new sheet.

        Step2: In the column next to the Date of Hiring column write the syntax

                     = TEXT(A2, “YYYY/MM/DD”) and enter

        Step3: The date is formatted to YYYY/MM/DD format.

        Step4: Select B2 and drag down.

        Q5: Find the Number of years of service for an employee in the                                          organization as on date.

        Step1: Copy the Date of Hiring column in new sheet in column A.

        Step2: In the column B, insert today’s date.

        Step3: In the column C, write syntax   =DATEDIF(A2,B2,”Y”) and enter

        Step4: Select C2 and drag down.

        Step5:  In case if we want to know months, then in column D write syntax

                     = DATEDIF(A2, B2, “M”) and enter.

        Step6: Select D2 and drag down

        Q6: Insert the currency “$” symbol in the salary column.

        Step1: Select the Salary column and copy in a new sheet in Column A

        Step2: Select the column and Select Accounting Number format. 

        Step3: In the Drop-down box, select the $ symbol and enter

        Step 4: $ symbol is inserted before the numerals.

        Step5: In case we want to insert Rs. Symbol then select Accounting Number                      format and select Rupee symbol.  Rupee symbol will be inserted.

        Q7: Remove the decimal place in the salary column.

        Step 1:  Select Salary column and click twice on Right arrow decimals in the                         tool bar.

        Step2: Decimals will be removed from the salary column.

        Q8: Given the date of birth of the employees, and the retirement age is 60 years,                 find when will they retire.

        Step1: Copy the Date of Birth column in a New sheet

        Step2: In Cell B2 write the syntax = EDATE(A2, 12*60) and enter.

        Step 3: We get one number.

        Step 4: Select the column B and go to Number formats and select the Short                         date from the drop down.

        Step 5: We get the retirement date .

        Q9: Employees whose experience is greater than 10 years will get                                   bonus of 10%.  Calculate the bonus paid.

        Step 1: Select the columns Date of Hiring, Number of years of experience and                         Salary in Column A of a new sheet.

        Step2:  In the cell D3 write the syntax

                        = IF(B3>10, 0.1*C3, 0)

         

        Step3: Click enter.  This gives the details of the bonus to be paid to the                               employees as per the conditions.

        Step4: Select D3 and drag down to get the bonus paid to all the employees.

        Step5: In case we want to know the amount paid as salary and bonus to the                         employees, in cell E3, write the syntax

                    = IF(B3>10, (0.1*C3) + C3, C3)

        Step6: Click enter.  This gives the details of the total amount to be paid to the                         employees as per the conditions.

        Step7: Select E3 and drag down to get the amount paid to all the employees.

        Step8: By adding the C column we get the total amount paid to all the                                 employees.

        Q10: How many employees are there in each department and what is their                 total salary department wise.

        Step1: Copy the columns Deptname, Employee name and Salary in a new                             sheet in A, B, C columns.

        Step2: Select the data

        Step3: Click on Sort and filter.  A drop down box appears.

        Step4: Click on the down arrow in the Dept name column.

        Step5: Select Filter.

        Step6: Select any one Department and deselect the remaining.

        Step7: We get the details of the department chosen.

        Step8: Copy the data in another sheet and repeat the process till you get the                        details of all the departments.

        Step9: Add the Salaries individually of all the departments.