Excel function to solve equation by approximation?

[ivan747]

Hi,

I need to use excel to calculate a link budget. Everything is fine except that I have to solve a rather complex equation for the propagation model.

Is there an Excel function that will let me solve for X by approximation?

Trivial example: 132=log(x/100)+log(y/z)

I have a Casio calculator that will do this. You write the formula and you tell it what’s your best guess for X. It will start by giving X some value and see if both sides of the equation are equal, if not, it then tries another, then another, and step by step it approximates until it gets it to like 8 digits of precision. This is not a sophisticated calculator so I think excel must have something like this built in...


I know I could rearrange the formulas, but they are all very complex. I need to try several different models and I don’t really have much time. I tried to solve some of them but I never quite got them right, even using a CAS the result wasn’t quite right... at this point the easiest way it to let excel approximate stuff.

Any suggestions?

Thank you all.

[Brumby]

Something like this, maybe?

[ivan747]

Something like this, maybe?

Thank you for the quick reply, but sadly I figure out how to apply this tool to my problem.

[ivan747]

I kinda figured that what I need to do is to “find the roots” of an equation. I’m researching...

[JohnnyMalaria]

Solver is definitely the way to do it.

Set up some cells with your roots. Solver will change these to meet your goal. The key is to know what goal to define. If you think about it, most fitting is of the least squares type. So, set up a column where you calculate your function using your roots. Create another column that calculates the square of the difference between your calculated values and the experimental values. In other cell, simply calculate the sum of the column of square differences. Tell Solver to adjust your root cells in order to minimize the sum of square differences.

This approach can work very well as long as you know the general form of your equation.

[ivan747]

Thank you both Brumby and JohnnyMalaria. You both pointed me in the right direction. Take a look at this video:


You were right, solver is the way to go. The guy gave an example using a quadratic equation. The drawback is that solver will only give you one root. Now, I wonder how many roots my models have... I'll try to solve each one, with example constants, on my CAS system and see how many roots I get for each. Hopefully this won't take very long... hopefully. 

Edit: Went ahead and I'm making the model work on excel the way it was designed to work (it was designed so that you give it a distance and it tells you how much path loss you have to budget for). Now it's time to use solver to do it the other way around (tell it how much path loss I can tolerate so that it tells me what's my maximum distance). Wish me luck.

[ivan747]

It works! It's not realtime, you have to manually go and solve it every time you change the inputs to your formula, but it's working!

Thanks guys!

P.S. I managed to symbolically solve the simplest model using my CAS calculator. That means I now have the equation itself solved for "x". The Solver and the new equation get the same results, pretty nice.

[Brumby]

Good to hear you've got a working solution.

I am sometimes amused, if not amazed, at some of the things you can do with Excel.  I have a couple of fun spreadsheets I play with on occasion.  One does anagrams and the other finds all the prime factors of any number up to 1 million.  Thinking outside the square can help find some rather fascinating solutions.

[lwatts666]

Mathematica is my tool of choice. Since its comes free with Raspbian for the Raspberry Pi, and we all have one (or more) sitting unused in the bottom desk drawer, there is no reason not to give it a try.

[ivan747]

Mathematica is my tool of choice. Since its comes free with Raspbian for the Raspberry Pi, and we all have one (or more) sitting unused in the bottom desk drawer, there is no reason not to give it a try.

Nothing like a good CAS system! I use an HP Prime, which in turn uses xcas as its engine. It is more user friendly than xcas, so I use my calculator whenever I can.

Sometimes the Prime gives a very convoluted answer, even if you try to simplify. This was the case with many of the models I tried. However, I managed to solve one of the (mathematically) simpler ones with its help. I’m going to be using the one unless somebody complains.