Posts Tagged ‘spreadsheet


Creating Mathematical Formulae from sample data using Excel

So you’re in Flash and want to move an item on the stage, rotate a camera or dynamically change a volume depending on another value. You could use the curves available in various Maths libraries, but you can spend a lot of time matching and applying constants.

A quicker – more accurate way can be to come up with your own formulae.

What!!? It is easy – you really don’t have to understand the mathematics, you can get Excel and Flash to do the heavy lifting for you.

Let me explain this by means of an example:
I was building a 3D application. I had a camera and needed it’s X rotation to be close to certain values depending on the camera’s position on the Z axis. I manually moved the camera into position and noted down samples of the values I required and placed them into Excel: One column for the z value and one for the required angle. – I only need a handful of samples to allow me to plot a regression trend line.

I now select the values on the spreadsheet and create an XY scatter chart. Great, my points are on the graph. I can see it makes a curve. In the ‘Chart’ drop down menu I now ‘Add Trendline…’. If I choose ‘Linear’ I get a straight line, but I want a curve so I can choose from a number of different methods. In this case ‘Exponential’ seems good. Select that and a look at the Options tab allows me to select ‘Display Equation on Chart’.

Now as soon as I press OK, I get my equation. Simple.

Regression Curve Formula in Excel from sample data

How do I represent this formula in Actionscript? Basically it is saying 202.62 times the exponential of 0.0014 times the chart’s x value. So in AS:

The final formula in Actionscript

As required, I have calculated the formula using the camera.z value and applied it to camera.rotationX. Now whenever the z value changes I have a smooth curve applied to rotationX.


2nd Oct 2010:
A quick addition to this post. If a more complex curve is needed then a polynomial should be chosen. A polynomial can have multiple ‘orders’. This more orders you have the more compelx the curve can be. (Excel allows up to 6). A great thing about polynomials is speed. It is merely a series of multiplications which are very light on the processor.

So a polynomial trend gives you (for example) the formula:
109.13×4 – 265.48×3 + 125.34×2 + 40.012x + 2

To represent this in Actionscript I’d need to replace x4 (x to the power of 4) with the x value paramter – say delta – so x4 becomes delta * delta * delta * delta (or indeed Math.pow(delta,4)), and so on. The final formula would be:
109.13 * Math.pow(delta,4) – 265.48 * Math.pow(delta,3) + 125.34 * Math.pow(delta,2) + 40.012 * delta + 2


Reasons to be Creative 2012

FITC Amsterdam 2012

Flash on the Beach 2011

Flash on the Beach 2010

Swingpants at Flash on the Beach

Swingpants at Flash on the Beach 2010

Flash on the Beach 2009

Swingpants at FOTB2009

Flash on the Beach Speaker

Twitter Updates