_{Made in Canva}

Understanding Advanced Excel Formulas

Advanced Excel formulas are complex functions that help users perform sophisticated data analysis. They automate tasks and manage huge data sets efficiently. These go far beyond ordinary mathematics as they include various types of conditions like conditions, logic data, lookups, string manipulation, data analysis tools, etc. Because of them, our work becomes automatic. And there are some types of advanced Excel formulas.

Lookup Function

These functions are functions that allow users to search and retrieve data from any table or range. According to the given criteria it works. Some of their popular functions are listed below:

VLOOKUP:
Its function is to find the value from the first column and then return the value from the second column in the same row.

HLOOKUP:
Its function is to find the value from the first row and then return the value from the second row in the same column.

XLOOKUP:
It is the most powerful and most flexible function that searches in both vertical and horizontal ranges.

Logical Functions

IF:
This function means that it returns one value if one condition is true and another value if it is false.

AND:
If all the conditions are true then it returns the value true otherwise it returns false.

OR:
This is a function that returns true if a value is true otherwise it returns false.

Where to Find Advanced Formula in Excel

Lookup Functions:

When we open our Excel sheet, first we are on the home page. The first thing you need to do to find these formulas is to go to the Formulas tab. When you go into it and click on it, different types of formulas will open below. From this, we have to click on Lookup and Reference, and we will see these formulas.

Here is a screenshot for clarity.

Logic Functions:

We also have to adopt the SIM procedure to find the logic function. When we are at the top of the home, the first thing we have to do is click on Formulas. After clicking on Formulas below we will see a Logical tab. Above that we can find these formulas by clicking on them.

Here is the screenshot.

By following the given steps I will solve my problem in which we will find the total average and grade system.

Steps:

Total:
The sum of all subject's marks.

Average:
Total marks are divided by the number of subjects.

Grade:
For the grading system, we use the following criteria.

Total:

We use the formula given below for the sum of all subjects

Formula: =SUM(B2:E2)

With the help of this, we can find the sum of all the students of Math English Physics, and Chemistry.

Average:

We use the formula given below for the average.

Formula: =F2/4

With its help, we can calculate our average. The total has to be divided by four because we have four subjects.

Grade:

We use the formula given below for the grades of all students.

Formula: =IF(G2>=80, "A", IF(G2>=70, "B", IF(G2>=60, "C", IF(G2>=50, "D", "F"))))

This formula checks the average marks of students and assigns them grades accordingly.

Here is our test this part ends in this we have found the total average and grade with great ease. Now we move to our second task.

After Applying on All Students

The IF function has four common operators that we commonly use in Excel. Those four functions are as follows.

Equal To ( = )

This operator checks whether two values are equal or not. It is used when we have to compare two values and returns the result based on that.

Formula: =IF(B2=50, "Pass", "Fail")

Greater Than ( > )

It is also an IF function operator that is used in Excel to check whether one value is greater than another.

Formula: =IF(B2>75, "Excellent", "Good")

Less Than ( < )

It is also an operator of the if function. Its purpose is to check whether one value is less than another. We use it when we want to check the result based on whether one value is less than another or not.

Formula: =IF(B4<50, "Fail", "Pass")

Not equal to ( <> )

It is also the operator of the if function and checks that two values are different. This is used when you want to perform an action where two values are different.

Formula: =IF(D5<>100, "Needs Improvement", "Perfect")

These are the four IF function operators that we generally use in our Excel sheets for data entry and various tasks.

First of all, I make a simple table

Now select the table and in the insert portion add the pivot table

Now press ok not change any setting

Now set this setting given below in the screenshot

Here is the final look at the pivot table

Thanks for visiting my second task. I invite @neelofar, @abdullahw2, and @rumaisha to join this challenge.

Cc:
@josepha, @simonnwigwe

_{Dated: 20-09-2024 About Me}