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

Greetings Everyone! I hope everyone is enjoying their life with family and friends by the Grace of Allah Almighty. After the changes on Steemit respect to SEC and Teaching Lessons, this is my first post because last week I was suffering from high fever and flu. As I am a Data Entry expert worked with more than 100+ clients on Fiverr, I decided to first do Spreadsheet homework given by Teacher @josepha. So lets start!

Homework

Advanced Excel Formulas are the formulas that helps us solve complex tasks with just few steps. The famous ones are LOOKUP, IF AND logical function, IF OR, SUMIFS, INDEX and few more. These formulas help us to search data and make decisions on our conditions. It also let us analyze large amount of information efficiently.

To get into LOOKUP you need to:

1. Open Excel and Go to "Formulas" Tab on top.
2. Click on "Lookup & Reference".
3. You can use any of the formulas in that dropdown.

You may confuse yourself in LOOKUP, VLOOKUP and HLOOKUP. But they are same just VLOOKUP is for Vertical search and HLOOKUP is for Horizontal search. They both do same function as LOOKUP just row and column difference. We use LOOKUP when we want to search data in both rows and columns.

To get into Logical functions, you need to:

1. Open Excel and Go to "Formulas" Tab.
2. Click on "Logical" icon in purple.
3. From dropdown you can use any function.

You can see the attached screenshot for better understanding. From the dropdown you can choose any logical function that suits your work requirement. I will use 4 IF function operators in 3rd question so you will better understand them.

Here we cant use IF Function to calculate total and average score. The reason is IF function is used for conditional decisions not for arithmetic operators. So here we will use SUM for Total and AVERAGE for average score of the students. Yes we will only use IF function for Grade of students as it is a conditional statement which needs to be checked before giving answer.

Formula for Total:

=SUM(B2:E2) for first row and will add 1 point for each next row as we move downwards. See the attached screenshot please.

Formula for Average:

=AVERAGE(B2:E2) will be used for average score of each student. Please see the screenshot below for better understanding.

Formula for Grade:

=IF(G2>=70, "A", IF(G2>=60, "B", IF(G2>=50, "C", IF(G2>=45, "D", IF(G2>=40, "E", "F"))))) is used to calculate grade of students based on their average score.

In the lesson there are total 6 operators but as only four need to be discussed, I will choose equal to (=), greater than (>), less than (<), and not equal to (<>). Lets see them one by one:

1. Equal To (=):

This IF function operator is used to check if two values are same or not. It is used when we want to see if two values match each other or not. For this the formula is very simple:

=IF(B1=C2, "Yes", "No")

2. Greater Than (>)

Greater Than operator is another commonly used IF function operator in excel that is being used in offices, data entry, simple calculations, computer calculations etc. It is used to check one value is greater than another or not. Formula for it is:

=IF(B2>50, "Pass", "Fail")

3. Less Than(<)

This function operator is used to check if one value is smaller than another. It allows you to check if value is less than the specific number or not. Formula for it is:

=IF(B2<50, "Low", "High")

4. Not Equal To (<>)

This operator helps to check if two values are different. It is used when you want to see if a value doesnot match another. Formula for it is:

=IF(B2<>100, "Wrong", "Correct")

Lets make a pivot table that will show the total sales by product. I will share the steps in screenshots for better understanding.

Step 1

Step 2

Step 3

Step 4

This is all from me for the "SEC | S20W2 | Data Analysis with Google Sheets: (Advanced Excel formulas, and pivot tables.)" I invite @hudamalik20, @paholags, and @abdullahw2 to participate in this Spreadsheet Lesson.