Unleash the Power of Excel
Most people don’t know that there is a very powerful computer language sitting in the background of their Excel spreadsheets. It is called VB (Visual Basic), and it can be used to write custom macros that can do, well, just about anything. A well-written VB macro can automate any manual process that you can do in excel, but it can do far more also. LTS has made countless programs that look and act like stand alone, commercial software that serves a variety of our client’s needs. Anywhere from database manipulation, advanced engineering design programs, test data number crunching, add-in functional utilities. We have never come across an Excel challenge that VB could not offer a clean and efficient solution.
The experts at LTS are masters of the VB environment, and we pride ourselves on using Excel’s functionality to its maximum potential. If you have a time-wasting process involving number crunching, data manipulation, financial reporting, you NEED to talk to LTS and find out how we can increase your productivity, and let the software work for you.
Corporate and Individual Excel Training
LTS will train individuals or corporate groups. Every training session is individualized to meet the trainee’s experience level and goals. The general categories include:
- Beginner Excel
- Intermediate Excel
- Advanced Excel
- Intro to Visual Basic
- Intermediate Visual Basic
- Advanced Visual Basic
Technical Matters
There are many folks out there that dabble with VB, but LTS offers unmatched technical prowess with the fine points of VB and Excel. We know the capabilities and limitations, which is important when you roll your sleeves up and start writing code. Here is an example of what we can do with Excel and VB that others can’t:
Polynomial Regression
If you’ve ever tried to perform a polynomial curve fit to plotted data, you’ve probably found that Excel is not very cooperative in providing the trendline constants for use in the sheet. The novice solution is to display the equation on the chart, and manually type the constants into cells that you can then use to predict other results based on the data. This is a poor technique, because if your source data changes, the constants need to be manually updated.
The intermediate technique is to use the LINEST() function in excel. Some power uses know that LINEST can be manipulated to return regression statistics and coefficients of not only a linear regression, but higher-order polynomial regressions as well, and it returns them in an array-formula format. This is a pretty slick technique, and it was perfect in Excel 2002/2003, but in Excel 2007/2010, the functionality got severely diminished. Before, you could reliably perform up to a 6th order or higher regression. But in the newest version of Excel, LINEST is for lack of a better term, broke. If you try to do higher than a 2nd order regression, it outputs garbage data.
An advanced technique is to do a manual regression calculation in the sheet itself…taking the sum of power terms and product terms of your X & Y data, constructing matrices in the sheet, and performing matrix inversions and multiplications using the MINVERSE() and MMULT() functions. This technique works ok, except that it takes up alot of space on the sheet, and is difficult to modify the regressed order. And there is one more pretty important flaw, and that is the limitations of the embedded matrix math functions of excel…the only calculate to 15 significant digits. This means, that any regression higher than a 4th or 5th order fit starts to produce fairly large error, especially if your data itself is very large or very small.
So what do we do?…well the LTS solution (or better called the super-advanced technique) is to make a custom function that does all the math and avoids any of the embedded Excel function limitations. LTS has a very good custom function that can be added to your Personal Macro Workbook that has virtually unlimited polynomial regression capability. The way it works is basically a VB automated process of finding the sum-of-squares terms, creating matrices in array forms, and performing a manual matrix inversion and matrix multiplication, taking advantage of the double-precision data type in VB.
There are only a handful of power-users that will really even grasp what these differences are; but hopefully it proves a point that, when it comes to VB and Excel, at LTS…we know what we are doing.