Average Column in Excel - Lotus Script

HCL Domino Domino Forum
1

Hi There,

I have an agent that creates an excel worksheet using lotus script. I need to Calculate the average of one of the columns. Is there a way to calculate the average of the column in lotus script?

Any response is greatly appreciated :slight_smile:

2

Subject: Average Column in Excel - Lotus Script

Create a macro in excel that does what you need to do. Then look at the code of this macro and port it to Lotusscript.

3

Subject: RE: Average Column in Excel - Lotus Script

Thanks for the response! I’ll try :slight_smile:

4

Subject: RE: Average Column in Excel - Lotus Script

Some examples – thare are a TON of ways to do this, including the FormulaR1C1 method…

ActiveCell.Value = “=AVERAGEA(E1,E4)” ’ AverageA (arithmetic mean) cells E1 through E4 – put the result in the currently active cell

xlApp.Cells( RowPointer, ColumnPointer) = “=AVERAGEA(E1,E4)” ’ Average (arithmetic mean) cells E1 through E4 – put the result in the cell pointed to by RowPointer and ColumnPointer

Hope you get the idea…

Gary

5

Subject: RE: Average Column in Excel - Lotus Script

I tried, but Im not understanding. It wouldnt be an active cell. It would be whatever was generated through the script. This is what the Macro is giving me below:

ActiveCell.Offset(-26, 3).Range(“A1:A5”).Select

ActiveCell.Offset(-22, 3).Range(“A1”).Activate

ActiveCell.FormulaR1C1 = “=AVERAGE(R[-4]C:R[-1]C)”

ActiveCell.Offset(-4, 0).Range(“A1:A5”).Select

6

Subject: RE: Average Column in Excel - Lotus Script

See if this helps…

REM  The kind of referencing we are going to do uses numbers instead of letters

Dim RowPointer As Integer

Dim ColumnPointer As Integer

Dim StartingRowNumber As Integer

Dim EndingRowNumber As Integer

Dim FormulaRowNumber As Integer



ColumnPointer = 1  ' For this type of referencing we will use column NUMBER instead of column LETTER!!!!!



StartingRowNumber = 1

For RowPointer = StartingRowNumber To 5  ' these will be the Excel Row Numbers

	xlApp.Cells( RowPointer, ColumnPointer ) = RowPointer  ' Just to put a value in the cell

Next

EndingRowNumber = RowPointer



REM  Now add one to RowPointer so we know where to put the formula for the "Average"

FormulaRowNumber = EndingRowNumber  + 1

xlApp.Cells( FormulaRowNumber, ColumnPointer ) = "=AVERAGEA(" & Cstr( StartingRowNumber ) & "," & Cstr( EndingRowNumber ) & ")"
7

Subject: RE: Average Column in Excel - Lotus Script

Thanks for responding and for your help Gary! :slight_smile:

8

Subject: RE: Average Column in Excel - Lotus Script

Doesn’t HAVE to be an offset – your LotusScript hopefully is keeping track of where it is, so your code should KNOW where to put the formula – you hopefully have a Row counter (pointer) and a Column pointer…

Gary