How To Use Nested If Statements In Microsoft office Excel And An Alternative VBA Solution

A common task in Excel is to define category names based on values; for example, grouping test scores into grades. A standard formula in Excel can do the job for a simple if then else scenario, but for multiple selections, the syntax can become complicated.

Let’s look at a simple set of test scores and the formula in the adjacent cell to define the result as passed or not achieved.
45 =if(a1>=50,”Passed”,”Not achieved”)

Excel And Nested If Formulas
The problem occurs when we need to assess values based on multiple ranges. In our example, if we wanted to assess a score of between 50 and 65 as “average” the following formula applies:
=if(and(a1>=50,a1

But this formula doesn’t allow for scores outside the “average” range such as the criteria for assessing scores below:
Below 50 = “Not achieved” 50-65= “Average” Over 65=”Above average”
The formula for applying the above conditions is complex:
=IF(A1=50,A165,”Above average”)))

Clearly, the nested formula can become cumbersome and difficult to debug so let’s look at an elegant VBA alternative.
A VBA Solution For Assessing Multiple Ranges

There are several different methods for assessing multiple values in VBA, but the one we’ll use is the select case method.

One of the best things you can do when writing VBA code is to reflect on the code and consider how easy it is to follow. First, we’ll select a column to assess and to make the code easier to read, assign the grade column and the number of scores to their own variables.

Range(“a1”).CurrentRegion.Columns(1).Select
gradeCol = Selection.Column + 1
items = Selection.Count
Now, we’ll loop through the selection and assign a grade to each score.
For x = 1 To items
test = (x)
‘ we don’t really need to reset the grade variable but it’s good practice to do so.
grade=””Select Case test
Case 0 To 49
grade = “Not achieved”Case 50 to 65
grade = “Average”Case 66 To 100
grade = “Above average”
End SelectCells(x, gradeCol).Value = grade

Next
Points To Note
Using a VBA solution rather than a direct Excel formula reduces the chance of error but a clear disadvantage is that the grade is not updated if the scores are adjusted in the future. One option to address this issue could be a VBA worksheet_change event so that whenever the scores were changed, the code would be repeated.
A more challenging solution might be to develop code which would create the formula as text and insert it into the applicable cells.

Summary

This scenario involved balancing the option of the correct solution – an Excel formula – with the use of easily written VBA code. It’s an example of where Excel users need to make decisions based on their knowledge level and the issue being addressed.

For more Detail :  www.office.com/setup

cantent sources  URl : office.com/setup

You can see Related Blog on  :  office setup