Ask Your Question
0

How to normalize a decision matrix with LibreBasic in Calc?

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

Sheet:

image description

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?
Spreadsheet file:
C:\fakepath\file.ods

edit retag flag offensive close merge delete

Comments

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
2

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

Comments

@librebel:
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
1

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

Comments

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
Login/Signup to Answer

Question Tools

1 follower

Stats

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

Seen: 513 times

Last updated: Jul 07 '17