1

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?

Dmitry Pavliv
  • 35,333
  • 13
  • 79
  • 80
BeardedMan
  • 384
  • 2
  • 12

2 Answers2

1

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));
Vincent Tan
  • 3,058
  • 22
  • 21
  • I wanted to insert the following formula, but it doesn't work. Excel reports an error. =SUMIF(D2:D13;"=EUR";E2:E13) I also tried Vincent Tan's suggestion like so: =SUMIF(D2:D13,"=EUR",E2:E13) – Joel Hansen Nov 20 '18 at 15:20
0

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)");
BlazK
  • 43
  • 7