Implement Excel Solver in Labview

[yavs]

Hello all,

I currently have an Excel sheet where I use Solver to solve some equations. The thing is I need to implement those calculations in a .vi since I need to get the results of the calculations in real time.

A summary of what values and equations I use in Excel:

- At the beginning I have a column with time values (X axis) and a column with frequency (Y axis).

- In a new column (lets call it column3), I use equation1, where I know the values "a,r,t", for the summation I only use values from i=1 to i=4.

Equation1

"t" is the time value at that moment.

For equation1 I will have four "a" values and four "r" values, corresponding to "i" values:

i=1 --> a1, r1;

i=2--> a2, r2;

i=3--> a3, r3;

i=4--> a4, r4;

To solve equation1 I will have to "manually guess" "a" and "r" values, but Solver does this work for me (I will explain these later).

- With column3 solved, I then use Least-Square fit equation:

Equation 2

To solve this equation I use Excel equation "SUMXMY2", to do so I use columns 2 and 3. The outcome of the equation will be called "X".

- Then I tell Solver to change "a" and "r" values to get the minimum value of "X". The only restriction I use is "r1" >2. What Solver does is a lot of iterations with different "a" and "r" values until it gets the minimum "X" value possible.

Sorry for this long summary... Now comes my question:

I know how to implement all those equation in Labview and I know how to manage all the columns/raws.

What I don't know, is how to implement the part where I need to change/guess "a" and "r" values. Solver does a lot of iterations of "a" and "r" until it gets the minimum "X" value possible.

In Labview how do I change "a" and "r" values automatically until I get the minimum "X" value?

Thank you for your time and help

[JÞB]

I'm not going to get into the math.  However, I will caution you that you are not very likely to get the exact same results in LabVIEW and Excel.  LabVIEW is IEEE-754 compliant, Excel is notorious for choosing to ignore this advice whenever it wants to (See Here)

[cbutcher]

I'd suggest looking at tools like the following:

Find All Zeros of f(x) VI

Nonlinear Curve Fit VI (doesn't have to be non-linear, if you don't need)

Take a look at Christian Altenbach's 2017 NIWeek presentation. It described the use of the latter in quite some detail. The slides are available here: NiWeek 2017 Presentation: Solving the inverse problem with LabVIEW

[cbutcher]

---

@yavs wrote:

- At the beginning I have a column with time values (X axis) and a column with frequency (Y axis).

- In a new column (lets call it column3), I use equation1, where I know the values "a,r,t", for the summation I only use values from i=1 to i=4.

Equation1

"t" is the time value at that moment.

For equation1 I will have four "a" values and four "r" values, corresponding to "i" values:

i=1 --> a1, r1;

i=2--> a2, r2;

i=3--> a3, r3;

i=4--> a4, r4;

To solve equation1 I will have to "manually guess" "a" and "r" values, but Solver does this work for me (I will explain these later).

---

This is an equation for finite summation, but what's the relationship you're actually trying to solve?

It looks like you're going for something like 

and trying to determine the values of a and tau ("r"?), but this has an analytic solution, so would be easier to just match terms without the summation (so perhaps this isn't what you're trying to solve).

If you can write the slightly more generic form of what you're trying to do, it might be possible to write the Nonlinear Curve Fit VI and upload it here. Then you'd just need the arrays you already have to calculate the result(s).

[Bob_Schor]

Two suggestions to get you started:

• Open LabVIEW.  Go to Help, LabVIEW Help ..., search for Least Squares.
• Open Google.  Search for Least Squares LabVIEW.

Read what you find on either site.  When in doubt, read what you find on both sites.  Try to implement what they suggest.  If you get stuck, post your code and some data for us to "chew" on.  Note that code posting is important -- we will want to "play" with the code you are developing to understand where/how/why you are "stuck" and attempt to teach you how to fix it (unless you want to hire one of us to do your work for you).

Bob Schor