Regarding R1C1 formula apply for particular cell

Aspose.Cells Product Family
1

Hi Team,

Can u provide the example for Applying R1C1 formula for particular cell. If i use below mentioned formula it is showing invalid formula

r1c1=IF(ISNUMBER(OFFSET(RC,-13,-12)),R[-13]C[-12],0);

If above mentioned formula is wrong please correct and let me know.

Thanks & Regards,
Saravanan Mani

2

Hi,

Thanks for your posting and using Aspose.Cells.

Please apply the same formula manually uing Microsoft Excel and attach it here for our reference. We will look into your sample excel file and analyze it and provide you a sample code to achieve the same thing using Aspose.Cells APIs. Thanks for your cooperation.

3

Hi Shakeel,

Whether Offset will work for Aspose?

Thanks & Regards,
Saravanan Mani

4

Hi,

Thanks for your posting and using Aspose.Cells.

Yes, you can set R1C1 formula using the Cell.R1C1Formula property. Please see the following sample code. I have attached the source excel file used in this code and the output excel file generated by it for your reference. I have also shown the console output of the code for you to check.

C#
Workbook workbook = new Workbook(“Book1.xlsx”);

Worksheet worksheet = workbook.Worksheets[0];

Cell cell = worksheet.Cells[“D4”];

//Set R1C1 formula
cell.R1C1Formula = “=SUM(R[-3]C[-3]:R[-2]C[-1])”;

workbook.CalculateFormula();

Console.WriteLine(cell.StringValue);

workbook.Save(“output.xlsx”);

Console Output
21

5

Hi,

Thanks for using Aspose.Cells.

Besides you can use CellsHelper.ConvertA1FormulaToR1C1() method to convert your A1 formula to R1C1 formula.

Please see the following code and its console output for your reference.

C#
string r1c1 = CellsHelper.ConvertA1FormulaToR1C1("=Sum(A3:D6", 5, 5);

Console.WriteLine(r1c1);

Console Output:
=Sum(R[-3]C[-5]:R[0]C[-2]

6

Hi,

whether is number and offset will accept aspose cells?

Thanks & Regards,
Saravanan Mani

7

Hi,

Thanks for using Aspose.Cells.

If you check this formula, you will see both number and offsets are present.

=Sum(R[-3]C[-5]:R[0]C[-2]

Let me know if I got you wrong. Please elaborate yourself in detail with some sample excel files or screenshots so that we could investigate your issue and help you asap. Thanks for your cooperation.