SEC | S20W2 | Data Analysis with Google Sheets: (Advanced Excel formulas, and pivot tables.)

growwithme (68)in #spreadsheet-s20w2 • 2 months ago

Aslam Mu alikum! First of all i want to thanks for the all organizers and teachers for such a beautiful contest i have learned a lot by reading posts and preforming task. In my last entry i was in hurry and i did some mistakes but this time i try to avoid mistakes.

Homework

Explain what you understand by Advanced Excel Formulas, and show us where advanced formulas such as the lookup function, and logical function are found in Excel with clear screenshots.

In simple advanced formulas in spreadsheets are used to perform complex task, including data analysis, manipulation and many more. With the help of these formulas, user will be capable of handling large data, making complex calculations, and performing advanced data analysis flawlessly. These formulas help user for time saving and for increasing efficiency of work. VLOOKUP formula and the IF FUNCTION formulas are famous formulas that are used for looking values from larger datasets and applying logical operator, we will discus these operator in next question.

LOOKUP

Where to find lookup formula in excel?

Open excel sheet
Click on Formula button in ribbon as shown in below Screenshot
Click on Lookup & Reference button
You well get it

In first screenshot, there is yellow color button of Lookup & Reference. You don't find it yellow in spreadsheet, I did it for showing purpose and in second screenshot there is LOOKUP formula highlighted with blue color. There are also three same looking formulas shown in the Dropdown bar LOOKUP, VLOOKUP and HLOOKUP all of these have similar functions like LOOKUP formula is used to search for a value in a range (row or column), Vertical Lookup formula is used to search for a value in the first column of a range (or table), and finally HLOOKUP is used to search horizontal across the row.

Logical Function

Click on Formulas
Click on Logical button
You well get it

In dropdown bar there are logical function shown in screenshot, we will discus them later in next question.

Task 2

Write the IF Function formula to calculate the total, average score, and grade of students given in the table below.

IF function in Excel is a logical function used to perform conditional tests. It checks weather the given condition meets the requirement. In our case our IF Function will be applied on the student marks data, and check grade weather the student get A grade Or B, or C or D, on the basis of logical operator, that will be discussed in next question. For Appling IF Function to grade column, first we will need to complete total score and average score.

SUM Formula

To find the total score of student we will use SUM formula (=SUM(B2:E2)), that is shown in Screenshot step by step.

After Appling the formula, I dragged it down to apply on all column within range, and the results are shown below.

AVERAGE Formula

To calculate the average score of student we will apply the AVERAGE Formula (=AVERAGE(B2:E2)) in the cell of G2. Below the Applying methods and results are given step by step in Screenshots.

After applying the AVERAGE formula in the G2 cell, I dragged for applying on whole column within a it down within corresponding range, shown below.

IF Function formula

To calculate the grade of student we will apply IF Function formula (=IF(G2>=80, "A", IF(G2>=70, "B", IF(G2>=60, "C", IF(G2>=50, "D", IF(G2>=40, "E", "F")))))), Shown below.

Here is the result after dragged down of formula within corresponding range.

Task 3

Briefly discuss four IF function Operators that you have learned and tell us their functions and when we are to use them.

Here are the four operator of IF Function that, i will discusses (">", "<", "=", "<>").

Greater Than ( > )
As name is indicating the function of operator, greater than (>) operator is used to checks is one value is larger than other. The use case of this operator is check the given number is larger than the other number, shown below. For example we will check weather the student is pass or fail by using this operator.
(=IF(G2 > 70, "Passed", "Failed")), here if the student total average is greater then 70 then he/she is pass otherwise fail.

Less Than ( < )
This operator is totally similar to greater than (<) operator, the difference this is used to check weather the number is less then other number. we will use same case as we used above to demonstrate this operator.
=IF(G2<70, "Fail", "Pass")

Equal To ( = )
This is the simplest operator and use to check weather two value are equal. we will check the score of physics (D2 ) and chemistry (E2) are equal.
=IF(D2=E2, "Yes", "No")

Not Equal To ( <> )

This operator is used to checks if two values are not equal. The use case of this operator will be verifying if an item is not a specific category. for example to check weather 80 is not present in average column.
=IF(G2<>80, "No", "Yes")

Task 4

Based on the given data below: Create a pivot table that shows (see) total sales by product, by dragging the product to the Rows areas, Region to the Column area, and Sales to the Values area. Please we want to see the steps you take in adding your pivot table.

Pivot Table
To make this Pivot table first i set the table than click at the mid of table to select the entire table. Than click on the option of insert in ribbon to insert the pivot table, Than select the existing worksheet instead of new worksheet, after that i selected the range for pivot table, finally dragged all value to corresponding places as i instructed in task four. Below are the clear screenshot of this process.