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

in spreadsheet-s20w2 •  3 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.



image.png

Screenshot (5).png



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.

Screenshot (6).png



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.


image.png


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


image.png


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.


|image.png


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.


image.png


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


image.png



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.


image.png


image.png



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")



image.png
image.png


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")


image.png


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")


image.png



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.


image.png


image.png


I invite to @rafk @dave-hanny and @amnasafdar to participate in this challenge.

It's @growwithme©

Special & Original Work!

Authors get paid when people like you upvote their post.
If you enjoyed what you read here, create your account today and start earning FREE STEEM!
Sort Order:  
Loading...

@tipu curate

Holisss...

--
This is a manual curation from the @tipU Curation Project.

Your post has been rewarded by the Seven Team.

Support partner witnesses

@seven.wit
@cotina
@xpilar.witness

We are the hope!

CONGRATULATIONS!!

Your post has been supported by TEAM SHINING STARS. We support quality posts, good comments anywhere, and any tags.


1000152665.gif

Curated by : @aviral123