Guide: How to Use and Features (ver.1.03)

6. Transfer ANN to SpreadSheet TOC

• Organisation of the text report
• Formula to type in in your spreadsheet
• Download spreadsheet with ANN for this example
• Download your ANN as a spread sheet in "symbolic link" format

yu=1/(1+exp(-w2*h)), h=tanh(w1*xu),

where "xu" is the input scaled to fit within the range from 0 - 1,
"yu" the output scaled to fit within the range from 0 - 1.

In the "text report" you find two sections which contain information that you need to fill the values.

Quite at the top you find:

--------------------------------------------- Output Variable: a+b --------------------------------------------- # input nodes: 4 # hidden nodes: 5 # output nodes: 1 # R^2: 0.99959 # StDev: 0.082757 fraction of value span: 0.45976% # Estimated confidence intervals (p=0.05, using residuals): # (add to predicted values): -0.17252 0.15169 # number of cases: 200

And further down:

-------------- advanced users --------------- Weights of Neural Network (last column contains biases) First layer (w1): 5.0821 5.0035 0.20477 0.20516 0.20974 -0.81814 -1.0164 -0.13978 -0.10229 0.76539 2.7179 3.1205 0.32192 0.2587 -6.5849 -0.58223 -0.20484 0.2816 0.20965 0.12049 0.3077 0.4859 -0.90346 -1.5403 -1.8878 Second layer (w2): 5.3086 -1.9415 4.5629 -0.99264 -0.15679 -1.4462 Scaling of inputs to [0 1] min: 0 0 0.009 0.002 max: 9 9 1 0.993 Scaling of output from [0 1] min: 0 max: 18

Let's start with your spreadsheet with your input and and output data and nothing else.
In this example, it would look like this:

a	b	random1	random2		a+b	a-b
0	0	0.554	0.511		0	0
0	1	0.802	0.951		1	-1
0	2	0.146	0.631		2	-2
0	3	0.286	0.845		3	-3

For the ANN in Spreadsheet, you should open a new sheet (but this could be in the original book).
There you should add a field which will contain the coefficients. This could look like this:

maxima and minima values of the data
	Input				Output
	i1	i2	i3	i4	y
min:	0	0	0.009	0.002	0
max:	9	9	1	0.993	18

And you should give the cells names for better understanding:
You could name the minimum of the first input "min_i1", the second "min_i2" etc.

Now the weights for the first layer: w1
(i1 corresponds to "a", i2 to "b", i3 to "random1" and i4 to "random2")
(n1 through n5 correspond to the nodes in the hidden layer)

weights w1
	i1	i2	i3	i4	bias
n1	5.0821	5.0035	0.20477	0.20516	0.20974
n2	-0.81814	-1.0164	-0.13978	-0.10229	0.76539
n3	2.7179	3.1205	0.32192	0.2587	-6.5849
n4	-0.58223	-0.20484	0.2816	0.20965	0.12049
n5	0.3077	0.4859	-0.90346	-1.5403	-1.8878

weights w2
n1	n2	n3	n4	n5	bias
5.3086	-1.9415	4.5629	-0.99264	-0.15679	-1.4462

We will use "w1n1i1" to denominate weight w1 for node 1 of the hidden layer and input 1 ("a") and the others respectively. "w1n1b" is the bias of node 1 of the hidden layer. "i1" input 1 ("a").

It's probably best to split the equation somewhat, in order to avoid a long formula worm.

So, for the node 1 of the hidden layer we get a formula like that:
(we name this cell/column "n1")

which runs from 0 - 1.

To get back our output, we have to scale it back:

Summarising:

For this example with 4 inputs, 2 outputs and 5 hidden nodes for the first output, the sum, you would have to setup the following for an ANN for the first output:

ANN for first output: "a+b"
i1	i2	i3	i4		n1	n2	n3	n4	n5		yu	y ("a+b")

And now everything toghether:

maxima and minima values of the data   Input Output   i1 i2 i3 i4 y min: 0 0 0.009 0.002 0 max: 9 9 1 0.993 18 weights w1   i1 i2 i3 i4 bias n1 5.0821 5.0035 0.20477 0.20516 0.20974 n2 -0.81814 -1.0164 -0.13978 -0.10229 0.76539 n3 2.7179 3.1205 0.32192 0.2587 -6.5849 n4 -0.58223 -0.20484 0.2816 0.20965 0.12049 n5 0.3077 0.4859 -0.90346 -1.5403 -1.8878 weights w2 n1 n2 n3 n4 n5 bias 5.3086 -1.9415 4.5629 -0.99264 -0.15679 -1.4462

ANN for first output: "a+b" i1 i2 i3 i4 n1 n2 n3 n4 n5 yu y ("a+b")         =TANH( ( (i1-min_i1)/(max_i1-min_i1)*w1n1i1 +(i2-min_i2)/(max_i2-min_i2)*w1n1i2 +(i3-min_i3)/(max_i3-min_i3)*w1n1i3 +(i4-min_i4)/(max_i4-min_i4)*w1n1i4 )+w1n1b ) =TANH( ( (i1-min_i1)/(max_i1-min_i1)*w1n2i1 +(i2-min_i2)/(max_i2-min_i2)*w1n2i2 +(i3-min_i3)/(max_i3-min_i3)*w1n2i3 +(i4-min_i4)/(max_i4-min_i4)*w1n2i4 )+w1n1b ) ... ... ... = 1/(1+exp( -1*( n1*w2n1 +n2*w2n2 +n3*w2n3 +n4*w2n4 +n5*w2n5 +w2b))) =yu * (max_y-min_y) + min_y

You can download a spreadsheet to play a little with that.
Note: In the spreadsheet the names are preceeded by two underscores, "__", but are otherwise the same.

• MS Excel: ANN1.xls (XLS 5.0)
• SYLK:ANN1.slk (SYLK)
• Lotus 1-2-3:ANN1.wk1 (wk1)

So far the example.

You can download a spreadsheet containing your ANN. On the "ANN Analysis" page where you find the "Trained ANN Statistics", you find at the bottom of the page a link which points to the spreadsheet containing your ANN. The file format is called "symbolic link". Some spreadsheet software will not import the formulas (for the Linux versions: Staroffice 5.*, Openoffice 1.0, Applix 5.*) but MS-Excel does.

There are two types of the layout of the prediction section. This is due to restrictions of the spreadsheet programs.
If you have less than 48 input columns, the layout is like the above described, but with some more columns (again, this is necessary due to restrictions of the spreadsheet programs):
You find the result of the prediction in the Y column, after the last field for input.
The i1, i2, ..., mark the columns for the inputs of your question.
The iu1, iu2, ..., mark the columns of the inputs scaled from 0 to 1.
The node1, node2, ..., mark the columns with the output of the hidden nodes.
The yu column is the output scaled from 0 to 1.

Network for output o1 Prediction results appear in this column. i1 i2 .... y iu1 iu2 .... node1 node2 node3 .... yu Type in the inputs of your question in this row. ->                     ....

Otherwise, the predictions section is organised vertically:

Network for output o1 Type in the inputs of your question in the column where this text starts, starting in the row below. i1   i2   ....   Prediction results appear here y   ....

And there are even more fields which are needed to reduce the length of the formula in one cell (again, this is necessary due to restrictions of the spreadsheet programs).

That big sets can't be handled nicely in a spread sheet. This feature is there, but we consider it impratical. You should be able to find better ways to implement the ANN at your place, if you really need it.

However, it is useful for educational purpose.