openoffice calc sumproduct with a twist

Question

my first attempt in VBA apart from using simple functions; asking for a kick start here:

assume this (part of a) sheet

factor      b-count c-count d-count
A2          b2      c2      d2      ...
A3          b3      c3      d3      ...

Assume that these are the first columns and rows A1 to D3, holding numeric values each.

If factor is 1, I want A(N) (column 'A', row N >= 2) to hold the sumproduct of row 1 and row N. The twist comes when factor is not 1. In that case I want a sumproduct of count*round(value * factor).

Example:

1.5         2       1       0       4
=myfunc(2)  4       8       11      15
=myfunc(3)  11      20      28      36
=myfunc(4)  29      53      74      94

where myfunc(2) should result in
round(4*1,5)*2+round(8*1,5)*1+round(15*1,5)*4 = 6*2+12*1+23*4 = 12+12+92 = 116, myfunc(3) = 17*2+30+54*4 = 34+30+216 = 280, myfunc(4) = 44*2+80+141*4 = 88+80+564 = 732 etc.

I could just insert a row below each one, multiplying every value with the factor; but I would love something fancier.

basically thought (pun not intended):

col='B'
sum=0
do while (col)(N)>0
    sum=sum+(col)(1)*round((col)(N)*A1;0)
    col=col+1
loop
A(n)=sum

where (col)(N) refers to the cell in column col and row N.

Not important enough to study the manual; but it would be great if someone can do this off the cuff.

Another point: I have read that custom functions must be stored in the "Standard Library";
but I could not find any mention on HOW to do that. Who will point me to the right manual page?

The entire row, or just parts of a row? Where is your example data stored, and which `count` is to be used? In addition, where is `value` coming from? Is it only the `sumproduct` of row1 and rowN, or is it the `sumproduct` from row1 to rowN? — AJD, Feb 06 '18 at 05:17
This question is well formatted, but not very clear. What does "A2" mean -- is that a string, or does it mean column A, row 2? If the latter, then what might it contain? An actual example would help, rather than only variables. — Jim K, Feb 06 '18 at 18:05
Please remove the `vba` tag, as that is normally only used for MS Office. Probably you want to add `libreoffice-basic` tag instead. The languages are not the same. — Jim K, Feb 06 '18 at 18:12
@AJD The data is in columns B and further right. And it´s just parts of a row; actually it´s columns Z instead of A, AA instead of B and so on. The list is pretty open to the right; but you can assume that it ends when there is an empty cell in row `1` (or actually, currently row 76 in one sheet and 65 in another) — Titus, Feb 07 '18 at 22:00
OK, the question is reasonably clear now. It took me a long time to figure out that `1,5` is a decimal number because in my locale the decimal separator is `.`. Part of my confusion was that`x69` should be `A1` in this example. — Jim K, Feb 08 '18 at 17:31

score 1 · Answer 1 · answered Feb 08 '18 at 17:21

Go to Tools -> Macros -> Organize Macros -> OpenOffice Basic. Select My Macros -> Standard -> Module 1 (that is what is meant by the Standard library), and press Edit.

Paste the following code.

Function SumProductOfTwoRows(firstColumn As Long, row As Long, firstRow As Long)
    'For example: =SUMPRODUCTOFTWOROWS(COLUMN(); ROW(); ROW($A$1))
    firstColumn = firstColumn - 1  'column A is index 0
    row = row - 1  'row 1 is index 0
    firstRow = firstRow - 1  'row 1 is index 0
    oSheet = ThisComponent.CurrentController.ActiveSheet
    sum = 0
    column = firstColumn + 1
    factor = oSheet.getCellByPosition(firstColumn, firstRow).getValue()
    Do
        value = oSheet.getCellByPosition(column, row).getValue()
        count = oSheet.getCellByPosition(column, firstRow).getValue()
        If value = 0 Then Exit Do
        sum = sum + count * CLng(value * factor)
        column = column + 1
    Loop
    SumProductOfTwoRows = sum
End Function

Enter this formula in A2 and drag to fill down to A4.

=SUMPRODUCTOFTWOROWS(COLUMN(); ROW(); ROW($A$1))

The result:

This kind of user-defined function produces an error when re-opening the file. To avoid the error, see my answer at https://stackoverflow.com/a/39254907/5100564.

openoffice calc sumproduct with a twist

1 Answers1