Easy Dynamic Formula Evaluation

• VB.NET demo page - 1.4 KB

Introduction

Sometimes, there is a need to evaluate the result of a user-defined or dynamic arithmetic formula. This tip shows a very simple way to do that, without the need to build custom classes or lengthy code. It uses an (apparently) little known method of one of the .NET Framework's assemblies to do all the hard work for you.

Background

I had to develop a web application that, as one small part of its functionality, had to calculate a service cost based on just two variables (Mileage, and number of packages). The application supports multiple pricing options that are defined by the user, originally as Excel formulae. There are several standard formulae but we needed the option to support custom formulae entered by the user for specific customers, too. I'd not created this functionality before and searching threw up many custom-developed solutions, some very complex, and some offering far more functionality than needed. I also stumbled across references to an existing .NET method that appeared to do everything I needed, and on investigation, so it did. In fact, for all the formulae in use by the client, it was possible to pass the original Excel formulae in with very basic changes, allowing the users to continue using their familiar expressions.

Using the Code

The .NET Framework's System.Data assembly includes the DataTable object, which exposes a method called Compute. This takes in a formula, evaluates it, and returns the result. All we need to do is to first substitute actual values for any variables, create a DataTable object, and call the Compute method passing in our substituted expression. Say we have a formula like:

IF([miles] < 51, 40, (([miles] - 50) * 0.24) + 40) + [miles] * 0.17

This is used for single packages only, and is expressing the following rules:

If mileage is 50 or less, charge a minimum of £40 + 17p per actual mile.

If mileage is 51 or more, charge £40 for the first 50 miles, plus 24p for mileage over 50 miles, plus 17p per actual mile (for fuel).

Note that the formula uses a named placeholder [miles] for our variable, and uses the Excel IF function.

We'll create a method called Evaluate:

using System.Data;
//
// Scope is public if called from multiple locations; 
// otherwise, you can make this a private function
//
        public static double Evaluate(string expression)
        {
            expression = expression.ToUpper().Replace
                         ("IIF(", "IF(").Replace("IF(","IIF(");
            using (DataTable dt = new DataTable()) {
                return (double)dt.Compute(expression, null);
            }
        }
//

Then we can call it as simply as:

double mileage = 60;  // mileage will normally be user entered or calculated
double result = Evaluate("IIF([miles]<51,40,(([miles]-50)*0.24)+40)+
                          [miles]*0.17".Replace("[miles]",mileage));

Note that our Evaluate method converts the formula to upper case, and replaces any instance of Excel's IF with the DataTable's equivalent IIF. You can omit this replacement if your users know to use the IIF function. When we call the function, we need to replace any variable placeholders with the actual value. The example above just uses a single placeholder [miles] but of course, it's trivial to replace additional placeholders if necessary.

Points of Interest

DataTable.Compute takes a second string parameter, Filter. When called on an actual datatable this filters the rows used, but in our case, we can just use an empty datatable and ignore the filter, passing Null for this parameter. You'll want to enclose your call to Evaluate in a try... catch block in case the formula is invalid.

For more information on the Compute method, see https://msdn.microsoft.com/en-us/library/system.data.datatable.compute(v=vs.110).aspx.

History

• 22^nd April, 2018: Initial version