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.

office Excel.jpgLet’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

Microsoft office Excel

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.

Edward Lewis is a Microsoft Office expert and has been working in the technical industry since 2002. As a technical expert, Edward has written technical blogs, manuals, white papers, and reviews for many websites such as office.com/setup.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s