Formulas in spread

Posted by: stephc on 8 September 2017, 3:15 pm EST

  • Posted 8 September 2017, 3:15 pm EST

    Hello,

    In the first cell of a column, I would like to put a formula that calculates the SUM of this column from line 2 (Row 1) to the final row of the sheet (. ROWCOUNT)

    But this value changes because the end user can insert rows.

    I can do this programmatically
    Dim MyFormula = "SUM(R1C: R" & FP.ActiveSheet.RowCount & "C"
    FP.ActiveSheet.SetFormula (0, 2, MyFormula)

    But this is not what I want

    I want to register directly in the Spread designer or in the relevant cell (AllowUserformula is true) the correct formula and, after, save the spread.

    The Rows function "Rows(C: C)" which gives the value of RowCount does not help me much in this case.

    Is there a way to do this?

    Thank you

    Stephane


    (Translated by Google)





  • Replied 8 September 2017, 3:15 pm EST

    Hi Stephane,

    If you use the formula "R1C:R500" (assuming RowCount is 500, the default value), then the formula will reference the first row in the spreadsheet through the last row, and the current column, which would create a circular reference in any cell where you set that formula (since the cell would be included in that range). While circular references are supported, that's clearly not what you intend here.

    To reference all the cells except the first cell in the row, you want to use "R2C:R500C" instead.

    Please note that the row and column indexing for formulas starts at 1, and the indexing for the Spread API starts at 0, so the first row (index zero in the API) is "R1" for formulas.

    If rows are inserted or deleted in run time somewhere inside the range from row 2 to row 500, after the formula "SUM(R2C:R500C)" is set to some cell in the first row, then the formula will automatically update to reference the appropriate new range of cells, so that it continues to reference all cells in the column except the first.

    However, if you implement code to add new rows to the spreadsheet by setting RowCount in the sheet (or in the data model), then those new rows are NOT included in the formula. In that case, you would need to update the formula to include those cells.

    If rows are inserted before the first row, the formula will update to exclude those cells from the formula (since the first row is excluded).

    Hope that helps!

    Regards,
    -Sean
  • Marked as Answer

    Replied 8 September 2017, 3:15 pm EST

    Yes, I saw that for formulas, the lines start at 1 (It was a transcription error)

    But never mind the fact that there is no pre-established function for this.

    Thank you for your help

    Stephane
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels