Click here to Skip to main content
15,120,739 members
Articles / Web Development / ASP.NET
Tip/Trick
Posted 22 Apr 2018

Stats

9.3K views
97 downloads
4 bookmarked

Easy dynamic formula evaluation

Rate me:
Please Sign up or sign in to vote.
1.73/5 (4 votes)
22 Apr 2018CPOL3 min read
Use .Net function to evaluate your custom formulae

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:  

C#
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:

C#
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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

DerekT-P
Chief Technology Officer SOS
United Kingdom United Kingdom
First introduced to computing in 1969 while at school. Learned Elliot 903 machine code, punched cards by hand and sent off to a university for processing. Punching and syntax errors came back about a fortnight later. Subsequently the school got a teletype and access to a BASIC system. Really got the bug then and taught myself COBOL and wrote a COBOL interpreter using BASIC.
At Swansea Uni learnt FORTRAN and spent two summers using that to customise train simulation software at BR's Railway Technical Centre.
After graduating, spent 3 years with London Transport, working up from Trainee Programmer to Senior Programmer, using COBOL on both ICL and IBM mainframes, together with CICS.
Later went on to work for companies such as Unilever Computer Services (later became EDS), Cullinet Software (as International Programming Consultant), Synapse Computer Services (Applications Consultant), Prudential Insurance (Design Centre Manager), Dun+Bradstreet International (Business Systems Support Group Manager).
In 1995 I went freelance and formed Small Office Solutions. Initially also providing hardware support to small businesses, I also took on longer-term contracts using VisualBasic with Sainsbury, Tesco, BHs, Mothercare and many others. Later moved into providing full life-cycle consultancy and development to SMEs globally, working remotely most of the time. Applications have ranged from educational, personal finance, logistics, blogging platforms and many more.
Now partially retired and sharing some of my portfolio of code online via CodeProject.com

Comments and Discussions

 
GeneralMy vote of 5 Pin
DidiKunz11-Mar-20 1:38
MemberDidiKunz11-Mar-20 1:38 
GeneralMy vote of 1 Pin
peterboulton23-Apr-18 0:53
professionalpeterboulton23-Apr-18 0:53 
GeneralRe: My vote of 1 Pin
DerekT-P23-Apr-18 23:43
professionalDerekT-P23-Apr-18 23:43 
GeneralRe: My vote of 1 Pin
peterboulton24-Apr-18 1:17
professionalpeterboulton24-Apr-18 1:17 
GeneralRe: My vote of 1 Pin
DerekT-P24-Apr-18 7:42
professionalDerekT-P24-Apr-18 7:42 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.