CODEWITHSUNDEEP
spreadsheetformulaspreadsheetlight
BeardedMan
BeardedMan

Reputation: 394

Spreadsheetlight Formula not working

i'm using Spreadsheetlight for creating excel document. I need to use a formula on a specific cell, but it's not working. The code:

report.SetCellValue(string.Format("{0}{1}", Report.CELL_MAP.ACTIVITY_CRU_COL, row), string.Format("=IF({0}{1}=0; 0; ROUND(({2}{1}/{0}{1})*100; 0))", Report.CELL_MAP.ACTIVITY_REAL_MD_VAL_COL, row, Report.CELL_MAP.ACTIVITY_INVOICED_MD_VAL_COL));

Is there something I'm missing? Setting formulas like '=E9' is stored in cell as formula and works in final document. Any ideas why it doesn't work?

Upvotes: 1

Views: 1659

Answers (2)

BlazK
BlazK

Reputation: 43

I have just tested Vincent's answer on SUMPRODUCT and IF formulas and I can confirm that it works.

SUMPRODUCT example:

Instead of

sl.SetCellValue(cellRef, "=SUMPRODUCT(A2:B2;--A1:B1=\"Something\")");

should be

sl.SetCellValue(cellRef, "=SUMPRODUCT(A2:B2,--A1:B1=\"Something\")");

IF example:

Instead of

sl.SetCellValue(cellRef, "=IF(A1=\"Something\";1;0)");

should be

sl.SetCellValue(cellRef, "=IF(A1=\"Something\",1,0)");

Upvotes: 0

Vincent Tan
Vincent Tan

Reputation: 3166

Should it be commas instead of semicolons in the formula? Like so:

report.SetCellValue(string.Format("{0}{1}", Report.CELL_MAP.ACTIVITY_CRU_COL, row), string.Format("=IF({0}{1}=0, 0, ROUND(({2}{1}/{0}{1})*100, 0))", Report.CELL_MAP.ACTIVITY_REAL_MD_VAL_COL, row, Report.CELL_MAP.ACTIVITY_INVOICED_MD_VAL_COL));

Upvotes: 1

Related Questions