Derive a formula for a table of data

For the following table, I'm trying to figure out what formula I could use to plug in a number from the Vk column, and out pops RR. I'm sure there are a practically infinite number of formulas or functions that could be used, but I can't think of even one at the moment.

Code ( (Unknown Language)):

1. [B][U]  Vk    RR[/U][/B]
2.  16.0  10.0
3.  17.5   9.0
4.  20.0   8.0
5.  22.0   7.0
6.  26.0   6.0
7.  31.7   5.0
8.  40.0   4.0
9.  54.0   3.0
10.  81.0   2.0
11. 110.0   1.5
12. 137.0   1.2
13. 167.0   1.0

It doesn't even have to be completely accurate, just reasonably accurate.

Any ideas?

 

RR=146.63(Vk)^-0.976 is a really good match for that range.

(Courtesy of Excel)

 

I think this is the first time I've seen sgtWookie ask a question. guess there's a first time for everything

 

Thanks Zazoo, that's plenty close enough.

Strantor,
Odd as it may seem, math isn't one of my better subjects.

 

Zazoo -

How'd you do that?

dj

 

I'd assume Zazoo did that by a power regression in Excel. Both Excel and Open Office can do such tasks.

For those of you that don't know how to derive such equations, the classic engineering technique is to plot the logarithms of both variables on a Cartesian graph. You can get semilog plots by only taking the log of one of the variables. Straight lines on log-log plots lead to relationships of the form and semilog plots lead to relationships of the form where a and b are constants.

In the days before calculators and computers, we'd plot such data on log-log or semilog paper and be able to see these relationships by eye quickly. You can do the same thing with pencil, graph paper, and a calculator. When I was working, I often used to do this in my lab notebook because it was faster than typing the data into a computer, doing a regression, then plotting things on a printer.

 

I suppose I should explain just what I was trying to do...

I'd been looking around for DIAC PSPICE models, but they seemed to be a bit hard to find.

I'd found a couple of websites with some info, and tried them - the first one didn't work for Linear Technology's LTSpice, and I felt it was a bit dicey anyway.

Then I found this model and write-up:
http://www.spectrum-soft.com/news/spring2002/diac.shtm
The odd thing is that while the use of parameter RS (series resistance) in R5 is documented; VK (breakover voltage) was not shown anywhere in the schematic that I could see; and that was really what I was looking to be able to easily modify.

So, I started replacing R3 and R4 with various resistances (RR in the table up top), and documenting the resulting breakover voltage (VK in the table, see DIAC circuit 2.png)

Then I plotted RR vs VK in an Excel spreadsheet using a polynomial curve fit - (see DIAC calculations for Resistance vs Breakover voltage.png); the items plotted are in columns B and C. Columns G and H were just the same data copied over and re-sorted. I didn't get to columns J thru M until after Zazoo's reply.

But then I drew a blank on the function I'd need to apply to VK in order to obtain RR. I tried intercept(), slope(), and a few other things, but I was basically spinning my wheels.

After I got this one working, I stumbled across another DIAC model in the Yahoo! LTSpice Users' Group forum which actually turned out to be very similar to what I'd put together; but the other model was changing the value of R2 and leaving their equivalents of R1 & R3 @ 10k.

[eta]
Thanks, MrChips - but Zazoo's solution seemed to be a better fit overall. I got just the output I was expecting from the simulation.

 

It's not that hard in Excel. You make two columns, fill with the data, click Insert | Chart, pick XY (Scatter), OK everything till a graph pops up.

Pick any data point, right click it and select "Add Trendline." Pick which type of regression you want, and on the options pane X in "Display equation on chart."

Then look at the chart again and you should see the line Excel predicted against your data points. If they seem to match then your equation is useful. If not, go back and pick another trendline type.

For a polynomial regression, if you have N points and you pick a polynomial of order N you should get a polynomial that exactly hits each point. However, I've never seen a version of Excel get this right above 2nd or 3rd order. OO Calc does it correctly.

 

If any of you folks are programmers, you can quite easily do linear regressions using python and numpy. One of the nice things about matrix notation is the equations for multiple linear regression are the same as for simple linear regression. This makes it essentially trivial to do multiple linear regression. Here's the actual code for fitting a model such as

Y = b0 + b1*f1 + b2*f2 + ...

where the f's are functions of the independent variables X1, X2, etc. It's linear regression because the model is linear in the regression parameters (the b's).

Code ( (Unknown Language)):

1.  
2. I = inv(Xp*X)
3. b = I*Xp*Y  # N&W equation 7.21 p 226 (estimates of model parameters)
4. Yhat = X*b  # N&W equation 7.23 p 227 (fitted values)
5.  

That's all it takes. X is a rectangular matrix containing the observations in the column vectors and the first column is all 1's. Thus, the values in the second column are the values of f1, the third column f2, etc. Xp is the transpose of X. Y is the column vector of the dependent variable values. b contains the estimates of the multiple linear regression model and Yhat contains the predicted values. Y - Yhat contains the residuals. inv means matrix inverse and the * means matrix multiplication.

The N&W reference means Neter & Wassermann's "Applied Linear Statistical Models", one of the best books on the topic ever written -- and very accessible, as it keeps the math simple (e.g., very little calculus).

Years ago I wrote a collection of python routines that take columns of data in text files and produce various graphs, transformations, and regressions. If you're familiar with the old X-windows program called xgraph, you'll have an idea of the things it can do. Some day I'll get off my butt and post it all somewhere...

 

It's almost identical to (1/Vk)*160 which transposes to 160/Vk if that matters.

To me that seemed inherently obvious just looking at the numbers?

 

Here are several formulas, in descending order of goodness of fit(GOF).

The maximum GOF is .9994198 and the minimum GOF is .99924799