# How to normalize a decision matrix with LibreBasic in Calc? I want to run a normalization on a decision matrix of 4 times 6. I have entered my numbers in a sheet in Calc. The formula I am using is \pi_{ij}= \frac{x_{ij}} {\sqrt{\Sigma_{i=1}^m x_{ij}^2}} Sheet: I wrote the following Basic code. It seems to have no errors, but does not do anything.

REM  *****  BASIC  *****

Sub Main

Normalize(5,3)

End Sub

Sub Normalize (ByVal n As Integer,ByVal m As Integer)

Dim Doc As Object
Dim Sheet As Object
Dim SrcCell 'Cell in the source matrix
Dim TargetCell 'Cell in the target matrix where normalized values are saved
Dim TempCell As Object

Dim I 'index
Dim J 'index
Dim JJ 'inner index
Dim Sum 'Sigma x_ij^2 (j=0 to m)
Dim m 'maximum row index
Dim n 'maximum column index

Doc = ThisComponent
Sheet = Doc.Sheets(0)

For I = 0 to n  'traverse columns
For J=0 to m 'traverse rows
SrcCell = Sheet.getCellByPosition(I,J)
'Now apply the normalization formula for this cell
'Run a new loop to run formula on this cell
Sum = 0 'Reset Sum to 0
For JJ=0 to m
TempCell = Sheet.getCellByPosition(I,JJ)
Sum = Sum + (TempCell.Value^2)
Next
TargetCell = Sheet.getCellByPosition(I+n+1,J) 'Place the normalized cells in a new matrix cell, n+1 cells away

'Put the sum in the formula
TargetCell.Value = SrcCell.Value/Sqr(Sum)

Next

Next

End Sub


What is the problem?
C:\fakepath\file.ods

edit retag close merge delete

Attach fikles (.ods in this case) instead of images, please!

Sort by » oldest newest most voted Hello @jasonlibreoffice,

just remove or comment-out the 2 lines:

Dim m 'maximum row index
Dim n 'maximum column index

more

@librebel:
I would love to get informed in whatt way this should help.

n and m are passed as the method arguments , the dim resets them.

@librebel: Yes I saw that. But in addition I thought the repeated calculation of the sum of squares would spoil the thing. It didn't, but only wasted time. Sorry for my rash comment. I doubt if the OQ will be interested, but you might be. Therefore I will post a second answer with a demonstartion of possible solutions and "something" attached. This is not intended to be a demotion of your answer.

See my comments to the answer by @librebel .)

This attachment was announced there.

more

Hallo @Lupp, thank you for this interesting demonstration, plus additional macros for normalizing the matrix per column.

As for the "Test" Sheet name, it would be clearer if Sheet names would not begin with a "\$" character, but i don't know if it could lead to any operative troubles...

Any idle "enhancement" concerning reasonable syntactical limitations will cause trouble. Well, the grave errors related to the misleading single apostrophes for sheetnames beyond classical syntax for names seem to be worked off meanwhile. The fact that the enclosing apostrophes never become a part of the actual name, but get part of the formulae referencing into the respective sheet causes a pita as soon as you try to extract sheetnames from formulae...
No Calc function for sheetnames!

The function returnRangeForAddress from the above attached example may serve as an example for the useless complications caused by the admission of dollar signs (in specific leading ones) in sheetnames.

@librebel: You may try to suggest a clearer solution.

## Stats

Asked: 2017-07-07 11:02:49 +0200

Seen: 513 times

Last updated: Jul 07 '17