Help please with an inverse of a formula

yugi · Newbie Joined: 23 Jul 2006 Posts: 1

Hello.

The formula to start with is A = L + (2*S) + Floor(S/10;1)^2.

What I'm trying to figure out is how to make a formula that solves for S when given values for L and A.

This is actually my first time using Calc or any spreadsheet... I'm more of a programmer so my line of thinking is something like, in psuedocode:

S=0
while(L + (2*S) + Floor(S/10;1)^2 != A)
S+1

Any suggestions on how I can do this in Calc or on other ways of tackling this problem? Thank you very much!

Sliderule · Posted: Sun Jul 23, 2006 12:27 pm Post subject:

yugi:

Within Calc ( and most other Spreadsheets too ) . . . there is a tool called: Goal Seek. You could check out the help to see an example.

To quote from the help file:

RickRandom · Super User Joined: 27 Jan 2006 Posts: 1082 Location: UK

Can you solve it on paper, e.g. by rearranging the formula?

If you didn't have the FLOOR() function, then it would be a quadratic, and thus easy to solve, and with 2 possible answers:

= (-2 +sqrt(2^2-4*1/100*(L-A))/(2*1/100)

and

= (-2 -sqrt(2^2-4*1/100*(L-A))/(2*1/100)

or something like that, putting the cell references for A and L in place of A and L in the above equations.

If the FLOOR() is criticla, that prevents that idea....I'm not sure how you'd solve it on paper, so perhaps the goal seek is the best solution. Remember that if you change A or L, you need to re-run the goal seek. If you've got lots of values for A and L then that's a lot of goal seeking.

If the FLOOR() function critical, or just some means to always use the next lowest integer?

Is S an integer?

What is the context?

David