Ask Your Question

How to normalize a decision matrix with LibreBasic in Calc? [closed]

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

jasonlibreoffice gravatar image

updated 2017-12-10 16:36:51 +0200

erAck gravatar image

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}} image description


image description

I wrote the following Basic code. It seems to have no errors, but does not do anything.

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

Sub Main


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)
        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)



End Sub

What is the problem?
Spreadsheet file:

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-10-09 12:57:29.769889


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

Lupp gravatar imageLupp ( 2017-07-07 11:10:04 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2017-07-07 11:40:00 +0200

librebel gravatar image

Hello @jasonlibreoffice,

just remove or comment-out the 2 lines:

Dim m 'maximum row index 
Dim n 'maximum column index
edit flag offensive delete link more


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

Lupp gravatar imageLupp ( 2017-07-07 12:35:18 +0200 )edit

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

librebel gravatar imagelibrebel ( 2017-07-07 12:55:08 +0200 )edit

@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.

Lupp gravatar imageLupp ( 2017-07-07 20:10:04 +0200 )edit

answered 2017-07-07 20:11:15 +0200

Lupp gravatar image

See my comments to the answer by @librebel .)

This attachment was announced there.

edit flag offensive delete link 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...

librebel gravatar imagelibrebel ( 2017-07-08 00:09:43 +0200 )edit

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!

Lupp gravatar imageLupp ( 2017-07-08 00:27:42 +0200 )edit

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.

Lupp gravatar imageLupp ( 2017-07-08 00:32:08 +0200 )edit

Question Tools

1 follower


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

Seen: 886 times

Last updated: Jul 07 '17