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:
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:
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.
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
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.
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.
Robert Morris a Microsoft Office expert has been working in the technology industry from the last 5 year. As a technical expert, he has written technical blogs, manuals, white papers, and reviews for many websites such as www.office.com/setup