For reasons far to boring to go into I’m having to reverse engineer an old Excel spreadsheet and make a few changes to how it works, I’ve managed most of it but I’m stuck re-arranging a formula.

In the original model there is a calculation where I know a,b,c,d & f

x= (1-f)^3 a + 3f (1-f)^2 b + 3 f^2 (1-f) c + f^3 d

What I’m trying to do is re-arrange this as in the new version I know a,b,c,d & x but need to work out f

Hope you’re still with me – A Level maths was 20 years ago and my brain aches so how do I change this into something that says f=…..

Any help you can offer greatfully received!

reads pretends he understands scurries off scratching head
Guesses wildly

is it 42?

Ouch. Now you’be made my brain hurt too… 🙁

I’m not sure how to ‘undo’ that equation, hopefully someone else can help. A quick solution however may be to use the ‘solver’ in Excel to find the value if it is available. My worry would be that it looks as though there might possibly be more that one value of ‘f’ that works. You might want to try graphing values of x to see what it looks like if you know the suitable range of values of f. If it looks as though there is only one matching value then solver may do it for you (or goal seek in Excel is similar I think)

You will spend a very long time trying to come up with a simple formulae for f = …

You have a third order polynomial (degree 3) which will have 3 real or imaginary roots … can you remember the formula taught at A level for solving quadratic equations (degree 2)

It was something along the lines of = [-b +/- SQRT (b^2- 4ac) ] / 2a I think???

Well imagine trying to find such an equation where you are dealing with a higher order polynomial.

Now I can’t remember my Galois Theory (and I can’t phone the Guy up cos he died in a duel over a woman I believe a long time ago!). I think Galois theory states there is no such rational solution for polynomials of order higher than N.

Thing is I cant remember if N =2 or 3. I think it was 3 in which case your equation does have a solution but I will be damned if I know what it is.

Really not sure you are re-engineering here. To me this means unravelling and putting back together to solve the same problem only more efficiently etc. In my mind you are now trying to solve and entirely different problem.

It is over 25 years since I did such stuff so I could be well off Kilter.

yeah, you’ll have 3 values of f, though 2 of them may be the same.

Might need to go to a matrix solution

take the blue pill!! no the red one, oh wait, take them both!

Thanks for the suggestions, I thought there may be a couple of potential solutions given the order of the polynominals.

By way of context I’ve got a formula that calculates a Bezier interpolation between 2 points on a graph using those 2 plus the points either side. In the original file the calculation returns both an X and Y coordinate using the f term to represent the proportion of the distance between the two points.

What I need to do is return a y coordinate for a known x, and the thinking was solve f for the known x, then feed this through the calc for the y coordinate using the original equation.

I’ve bored myself here and I’m thinking the best approach is use a macro’d Goal Seek to run through the graph points.

Either that or tell them it’s 42 😀

Ah, I can see what you are up to and I suspect it won’t work :(. As has been already mentioned you could end up with a situation where you could have two possible values of y for a value of x if the original curve wasn’t well behaved.

the stuff that keeps people up late at night….

GJP – Member
You will spend a very long time trying to come up with a simple formulae for f = …

You have a third order polynomial (degree 3) which will have 3 real or imaginary roots … can you remember the formula taught at A level for solving quadratic equations (degree 2)

It was something along the lines of = [-b +/- SQRT (b^2- 4ac) ] / 2a I think???

Well imagine trying to find such an equation where you are dealing with a higher order polynomial.

Now I can’t remember my Galois Theory (and I can’t phone the Guy up cos he died in a duel over a woman I believe a long time ago!). I think Galois theory states there is no such rational solution for polynomials of order higher than N.

Thing is I cant remember if N =2 or 3. I think it was 3 in which case your equation does have a solution but I will be damned if I know what it is.

Really not sure you are re-engineering here. To me this means unravelling and putting back together to solve the same problem only more efficiently etc. In my mind you are now trying to solve and entirely different problem.

It is over 25 years since I did such stuff so I could be well off Kilter.

It’s N=3, there is an explicit formula for N=3. Of course solutions may not be real. I’ll go and try and find it.

Here you go.

http://mathworld.wolfram.com/CubicFormula.html

I did A-level maths, further maths and several maths modules at uni – we never covered solving cubic functions because it was too damn hard!
your best bet might be some sort of iterative method
good luck!

I think in f it is

x=a+ 3(a+b)f+(3a+6b+3c)f^2+(a+3b-c+d)f^3

It’s N=3, there is an explicit formula for N=3. Of course solutions may not be real. I’ll go and try and find it.

TheBrick – thanks for clarifying that I shall sleep a lot better tonight 😆

Of all the courses I studied during my 3 years maths degree Galois Theory was the most difficult thing I ever encountered

Since it’s in Excel can you not just use Goal Seek to find a solution?

There is a closed form solution for a cubic, with one or three real roots – ALL cubics have at least one real root since they start at -infinity and go to +infinity or vice versa. You can get the condition on whether the other two roots are imaginary or not from your parameters. It’s all a pain though and I only needed the real root once in my thesis. To be honest, I’d stick it in solver and minimize (goal seek), it’s not a hard problem and will be fast enough.

Thanks for all the help and suggestions, glad it wasn’t just me being totally dense.

What’s goal seek? Is it some kind of iterative solution finder?

poppa – Member
What’s goal seek? Is it some kind of iterative solution finder?

Yes. Construct a cell that is equal to the formula for x driven by another cell called f.

Then use Goal Seek to vary cell f until it gives the known answers for x.