Undoubtedly the SI function of Excel is one of the most used. And if you use it nested you can make this function even more powerful. In this tutorial we are going to show you how to use the SI function with several answers, that is to say, you will learn how to use the SI function so that you can obtain more than 2 answers, as is usual in the use of this function.
The common use of the SI function
Normally the SI function in Excel is used to perform a logic test and get one result when the logic test is true and a different result when the logic test is false.
So by using the SI function in a simple way you could only get two possible answers.
To explain the use of the IF function with several answers we will use an example in which we want to get 4 different answers depending on value.
SI function with several answers
Using a table showing the grades of the students in a class, we want to obtain different answers depending on the grade obtained.
You will use the SI function within the SI formula so that when the result of the SI function is false, another SI function will be executed again.
The most important thing is that you carefully plan the SI functions in order to create them properly. If you do not nest the formulas correctly it may only work in some cases and not in others.
In the example, we will introduce the SI functions in the function of the note and we will evaluate them from minor to major so that errors do not occur.
The function will be as follows:
=YES(note<5; "SUSPENSE";YES(note<7; "APPROVED";YES(note<9; "NOTEBLE"; "OVERHIGHLIGHT"))) In the example the note is located in cell F2, so it would look like this: =IF(F2<5; "SUSPENSE";IF(F2<7; "APPROVED";IF(F2<9; "NOTABLE"; "OUTSTANDING")))) How does the nested IF function work? According to the formula used, the first IF function evaluates if the note is lower than 5, if the answer is true it will place the SUSPENSE value in the cell, if the answer is false it will execute again an IF function. The second function YES evaluates if the note is lower than 7, if the answer is true it will place the value APPROVED in the cell, if the answer is false it will execute again a function YES. In the third and last function if you evaluate if the note is lower than 9, if the answer is true it will place the value NOTABLE and if the answer is false it will place the value . Excel allows you to nest up to 64 different IF functions, although we do not recommend doing so. As you can imagine the result can be chaotic and difficult to work properly. Alternative solution for the example For the example used the given solution is not the only one possible. You could also nest the SI function using the values from highest to lowest. In that case the SI function would be this way: =IF(F2>8,99; “OUTSTANDING”; IF(F2>6,99; “REMARKABLE”; IF(F2>4,99; “APPROVED”; “SUSPENDED”)))
Apply colour to a specific rating
In the example, a red text format is applied to the whole row if the student’s grade is SUSPENSE.
It is something very simple to do, just use the conditional format and apply a rule that states that when the value of the cell of the rating is SUSPENSO apply the format established.