Excel formulas to set categories by value scope using functions!

News Fetcher4 hours ago

0 0 3 minutes read

Appointment of a category based on the scope of numbers is a common practice everywhere in schools, colleges and offices. You can do this in Excel as well. Using the correct Excel functions and formulas, you can set categories of values based on their cells. In this tutorial, I will share the three work methods Class set or The value is based on the numbers in Excel.

Jobs used in this tutorial are:

The functions used	used to
if	To conduct logical tests and return different values based on whether the test TRUE or bloomer.
vlookup	To search for value in The first column From a table and Return In the same row from another column.
IFS (later 2019 or Office 365)	To test Multiple conditions And return the value of the corresponding to the first real condition.

Set a category to the numbers range using the IF function:

the if A job in Excel helps you to perform a Logical test And return One or more values Depending on whether the test TRUE or bloomer.

Let’s say that we have a schedule with the list of students’ names and their total marks. Now, we will use the IF function to classify its performance into three categories, such as poorand justiceOr good, Based on their total signs.

Using IF function to set performance categories for students based on their total signs.

The formula used,

=IF(C4<=200, "Poor", IF(C4<=400, "Fair", IF(C4<=600, "Good", "Excellent")))

If the input value is 200 or less, it appears.poor“.
If it is more than 200 but 400 or less, it appears.justice“.
If it is more than 400 but 600 or less, it appears.good“.
If it is more than 600, it appears “excellent“.

Note: This method is suitable for short conditions and small data groups in Excel worksheets.

Set a category to the numbers range using Vlookup function:

Using vlookup A job for this task is a professional approach. First, you need to create an auxiliary schedule like the schedule shown in the image below. The second table must have Mark domains And the interview performance Categories.

Create an auxiliary schedule to set the category using a Vlookup function.

Now, I will use the Excel format to set the performance category based on the total signs of students using a Vlookup function.

 =VLOOKUP(C4,$F$4:$G$7,2)

C4: This is it Cell that contains value You want to search.

$ F 4 $: $ G $: This is the range of cells where vlookup You will search for value. It is “closed” to the brands of the dollar, so it does not change if you copy the formula.

2: This tells Excel to return the value of the second column of Research range.

The implementation of the above formula will immediately set the performance category for students based on the signs with the help of the assistant schedule.

Using Vlookup function to set performance categories for students based on their total signs.

That’s it; This is how you can use Vlookup to set categories based on the inputs in Excel.

Set a category to the numbers range using IFS:

Using IFS function To do this, it will remove You need to repeat the jobAs we did with the IF job above.

The formula used here is:

=IFS(C4<=200,"Poor",C4<=400,"Fair",C4<=600,"Good",C4>600,"Excellent")

Using IFS function to set performance categories for students based on their total signs.

Difference B/w if and ifs function:

If it is a job	IFS function
Checks One case At a time.	Checks Multiple conditions In one formula.
To check multiple conditions, Overlapping ifS need, which can become complicated.	No need for NestingEasier to read and write.
Available in All EXCEL versions.	Available only in Excel 2016 and then.
A value can be returned if TRUE And another if bloomer.	You can return Various results for each case.
It is difficult to manage When there are many conditions (overlas).	The easiest in management When there are many conditions.
Flexible In some cases when you only need one case.	More efficient To assess multiple conditions simultaneously.