Your Privacy Matters: We use our own and third-party cookies to improve your experience on our website. By continuing to use the website we understand that you accept their use. Cookie Policy
2700
Formulas do not seem to match on row and columns
posted

I think I may be miss-understanding the way in which formulas work...  I have put together a very simple example of what I am trying to do

int columnIndex = 1;
int rowIndex = 1;
decimal value1 = 10.2M;
decimal value2 = 5.1M;

WorksheetRow row = _worksheet.Rows[rowIndex];
row.Cells[1].Value = value1;
row.Cells[2].Value = value2;

string col1Label = ExcelColumnFromNumber(1);
string col2Label = ExcelColumnFromNumber(2);

Formula formulae = Formula.Parse($"=SUM(${col1Label}${rowIndex}:${col2Label}${rowIndex})", CellReferenceMode.A1);
row.Cells[4].CellFormat.SetFormatting(CurrencyCellFormat);
formulae.ApplyTo(row.Cells[4]);

What I find when I look in my generated .xlsx file is that my value1 is in B2, value2 is in C2, my formula is in E2 as expected however the formula is =SUM($A$1:$B$1) whereas it should be =SUM($B$2:$C$2).  What am I doing wron as this seems so simple.  I have tried various ways of setting the formula for the cell but the row and cell are always 1 out