Derive a formula for a table of data

Thread Starter


Joined Jul 17, 2007
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.

Rich (BB code):
  Vk    RR
 16.0  10.0
 17.5   9.0
 20.0   8.0
 22.0   7.0
 26.0   6.0
 31.7   5.0
 40.0   4.0
 54.0   3.0
 81.0   2.0
110.0   1.5
137.0   1.2
167.0   1.0
It doesn't even have to be completely accurate, just reasonably accurate.

Any ideas?

Thread Starter


Joined Jul 17, 2007
Thanks Zazoo, that's plenty close enough. :)

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


Joined Jul 7, 2009
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 \(y = ax^b\) and semilog plots lead to relationships of the form \(y = ae^{bx}\) 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.


Joined Nov 25, 2009
Excel can offer three or four different regression types, for example, linear, polynomial, exponential, logarithmic and I might be forgetting some. You can easily choose between them and select the one that fits your data best.

Thread Starter


Joined Jul 17, 2007
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:
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. :confused: 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.

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


Last edited:


Joined Apr 24, 2011
Zazoo -
How'd you do that?
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.


Joined Jul 7, 2009
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).

Rich (BB code):
I = inv(Xp*X)
b = I*Xp*Y  # N&W equation 7.21 p 226 (estimates of model parameters)
Yhat = X*b  # N&W equation 7.23 p 227 (fitted values)
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...


Joined Feb 11, 2008
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?

The Electrician

Joined Oct 9, 2007
Here are several formulas, in descending order of goodness of fit(GOF).

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

RR=\frac{1+.0003208 Vk}{.0063639 Vk}
RR=\frac{9787.6674}{61.8421 Vk-.3902071}
Last edited: