Use IFS function to calculate letter grades in Excel

By Wendy Tietz, CPA, Ph.D.; Jennifer Cainas, CPA, DBA; and Tracie Miller-Nobles, CPA

To calculate letter grades based on a percentage score, you can use multiple nested IF statements in Excel, which can get rather complicated quickly. However, there is an easier way.

Added by Microsoft in 2016, the IFS function replaces the need for multiple nested IF statements. You can put all your conditions (percentage grade cutoffs in this example) in one function, making it easier to follow.

To start, click on the cell where you want to enter the letter grade. Then click on the Insert Function icon (to the immediate left of the formula bar), opening the Insert Function dialog box. Type IFS in the Search for a function: field, click go, and then double-click on IFS under Select a function:. This will open the IFS dialog box (shown in the screenshot below). Fill in each of your letter grade cutoffs, starting with the cutoff for the bottom of the A grades. The screenshot shows how the cutoffs for the A grade and the B grade have been entered. Continue with grade cutoffs until you reach the bottom letter grade category. For an F in this example (not shown in the screenshot), the Logical_test5 was A3>0 (the cutoff value for the lowest letter grade should be the lowest possible percentage grade.)


Another option is to simply type out the formula using the IFS function syntax, =IFS(Logical_test1,Value_if_true,[logical_test2,value_if_true2]…). In the example above, the final formula comes out to IFS(A3>89.99,”A”,A3>79.99,”B”,A3>69.99,”C”,A3>59.99,”D”,A3>0,”F”).

See this short tutorial video for Windows and Mac users for a step-by-step overview of how to use the IFS function to calculate letter grades.

In case you were wondering, the IFS function has a limit of 127 different conditions, so it can easily handle letter grade calculations and many other different scenarios.

Wendy Tietz, CPA, CMA, Ph.D., is a professor of accounting at Kent State University in Kent. Ohio; Jennifer Cainas, CPA, DBA, is an instructor of accountancy at the University of South Florida in Tampa; and Tracie Miller-Nobles, CPA, is an associate professor of accounting at Austin Community College in Austin, Texas. See their site AccountingIsAnalytics.com for resources they have developed for teaching data analytics in introductory accounting. To comment on this article or to suggest an idea for another article, contact senior editor Courtney Vien at Courtney.Vien@aicpa-cima.com.


Get your clients ready for tax season

Upon its enactment in March, the American Rescue Plan Act (ARPA) introduced many new tax changes, some of which retroactively affected 2020 returns. Making the right moves now can help you mitigate any surprises heading into 2022.


Black CPA Centennial, 1921–2021

With 2021 marking the 100th anniversary of the first Black licensed CPA in the United States, a yearlong campaign kicked off to recognize the nation’s Black CPAs and encourage greater progress in diversity, inclusion, and equity in the CPA profession.