Here, we will learn how to use the Excel logical function: IFS. Later, we will also learn how to assign a grade based on the given score using IFS with the RIGHT and VALUE functions.
IFS function
The Excel IFS function runs multiple logical tests and returns the value corresponding to the first TRUE condition.
IFS takes two primary arguments: logical_test1 and value_if_true1. The first required argument logical_test1 will take the condition to evaluate whether it is TRUE or FALSE. The second required argument value_if_true1 will take the value to return when the condition in logical_test1 is TRUE. Like this, we can enter up to 127 pairs of logical_test and value_if_true arguments.
For example, assume that a code of 1 is "Bad", 2 is "OK" and 3 is "Good". We can make the formula, like so: =IFS(A1=1,"Bad",A1=2,"OK",A1=3,"Good"). When the value of A1 is 1, 2 or 3, IFS will return the values "Bad", "OK" or "Good" respectively. If A1 contains any other value, like 4, IFS will return the #N/A error. To prevent the error, provide TRUE as the final test and a value like "Error" as the default value, like so: =IFS(A1=1,"Bad",A1=2,"OK",A1=3,"Good",TRUE,"Error").
Assigning grade based on score using IFS with RIGHT and VALUE
To assign grade based on score, use IFS and supply the RIGHT and VALUE functions in the logical_test arguments of IFS, only if your dataset contains scores at the right side of the text strings.
The dataset (shown in the image) contains a score scored by a student in the specified subject. The goal is to assign a grade in column C based on the scores in column A. Since the score is at the right side of the name in column A, we need to use RIGHT and VALUE that will extract the score and convert the score into numeric value respectively.
The formula in C3, copied down, is:
=IFS(VALUE(RIGHT(A3,2))<60,"F",
VALUE(RIGHT(A3,2))<70,"D",
VALUE(RIGHT(A3,2))<80,"C",
VALUE(RIGHT(A3,2))<90,"B",
VALUE(RIGHT(A3,2))>=90,"A")
How this formula works
An IFS formula is constructed with 5 tests. Each test contains the RIGHT and VALUE functions. In each test, RIGHT extracts the two characters "92" from the right side of the text string "Daniel -92". In each test, VALUE converts the text string "92" into numeric value 92. Each test is then tested returning TRUE or FALSE. Only the last test returns TRUE and therefore, the following value "A" is returned as the final output.
You have successfully learnt how to use the Excel logical function: IFS, and how to assign a grade based on the given score using IFS with the RIGHT and VALUE functions! I hope this post helped you.
