There have been many posts here looking at alternative ways of working with functions entered as text on a spreadsheet, and working with units, most recently here.

One drawback with this approach is that text in an Excel cell must be ASCII or Unicode, which has limited capabilities for presenting maths text in a readable form. Excel does allow elaborate maths equations to be generated and displayed, but as far as I know there is no way to interact with these equations from VBA, so they can neither be generated by code, or evaluated by code.

Python on the other hand has libraries that will convert plain text equations to Latex (and other formats), and display Latex code. I have now written a Python function “plot_math” using a combination of Sympy, Pint, and Matplotlib to:

• Read any maths function from the spreadsheet
• Convert the text to Latex format
• Return a graphic image of the function
• Optionally evaluate the function using a table of values for each parameter
• Optionally adjust for the units of the input and output values.

The new code can be downloaded from:

EvalU.zip

The spreadsheet requires the following software:

• Python
• pyxll
• Numpy
• Scipy
• Pint (which includes sympy and mpmath)
• Matplotlib

The download file includes two Python files:

• startup_min.py
• py_Units4Excel.py

Startup_min.py includes the plot_math function, and should be added to the list of files to open at start-up in pyxll.cfg.

Note that this is a work in progress. If you have any problems with installation or running the programs, please let me know.

Some examples of the new function in action are shown below:

The text to be displayed (and optionally evaluated) should be entered as plain text at any chosen location. In the example below the plot_math function is then entered in the cell immediately above (C12).

When the plot_math function is entered the image will display immediately below:

The image can then be dragged to the desired location; in this case so that the original text and the function return value (0) are hidden. In this example the numerical value of the function for selected input values is displayed using the py_Evalu function.

Alternatively the plot_math function will return the value of the function if suitable input data is selected:

In this case a three column data range has been selected, and also an output unit, so the calculation will be unit aware:

As before, the image is displayed immediately below the plot_math input cell, but can be dragged to any desired location:

Some other features to note:

• If the data range has only two columns (variable name and value) the calculation will have no units.
• If the output unit is omitted, and the data range has three columns including units, the output value will be in base SI units.
• Names of Greek letters (e.g. alpha) will generate the Greek character
• In some case Greek letter names will be the name of a standard maths function (e.g. Gamma), in which case the text should be followed by an underscore (gamma_)
• Some English letters also represent standard maths values (e.g. e and i). If an upper-case E or I is required, these should be entered followed by an underscore, and will then be displayed in upper-case, without the underscore.
• The exponentiation symbol may be entered in either Excel (^) or Python (**) format. In either case the exponent will display as a superscript, and the calculation will be correct