I have this line of code to add a formula on a given range:
using (var range = EnvSheet.Cells[49, 4, 86, 4])
range.Formula = "=AVERAGEIF(E49:P49,\">0\")*(1+C$47)";
range.Style.Fill.PatternType = ExcelFillStyle.Solid;
Now I want to add a value to a target cell as shown:
FillCurrentSheet.Cells[49, 5].Value = "00:00:25";
When I open Excel to view the result, the value 00:00:25 is there, but the formula is still #DIV/0 (Divide Zero Error).
What I've noticed though is when I manually set the value, or just retype "00:00:25" via the Excel UI, the formatting remains the same in the cell, but looking up at the Formula Bar, it changes to 12:00:25 AM and the AVERAGEIF formula now actually
recognizes the cell instead of displaying #DIV/0.
I've already tried setting the Numberformat before and after setting the value using this code:
FillCurrentSheet.Cells[49, 5].Style.Numberformat.Format = "h:mm:ss";
Still unlucky though. Will try ExcelCalcMode.Automatic.Equals(true) and see how it goes.
Just want to make sure if there's any other way to mimic the behavior that Excel does using EPPlus? It's relatively strange as the formula bar does not follow the formatting of the actual cell.