EPPlus returns #VALUE! instead of Value from the cell randomly

[NavisiteNittin]

trafficstars

As per Current requirement I need to read value from the cell using EPPlus. This cell contains the formula and showing value correctly in XL Sheet. but when i am reading that value using EPPlus some cells are giving correct value but some cells are giving error "#VALUE!". I have used ".Calculate()" before read the value but still facing the same problem. Please find the code below in c#.

totalRecycleWorksheet.Cells[row, colval + 5].Style.Numberformat.Format = "#";

totalRecycleWorksheet.Cells[row, colval + 5].Calculate();

var value = totalRecycleWorksheet.Cells[row, colval + 5].Value;

if (!totalRecycleWorksheet.Cells[row, colval + 5].Value.ToString().Equals("#VALUE!")) {} and here is the formula in every cells:

=IF(('Failure Item'!E348+ROUNDUP(('Output'!E348)$B$1,0)-'Purchased Items'!F348)>0,('Failure Item'!E348+ROUNDUP(('Output'!E348)$B$1,0)-'Purchased Items'!F348),0)

In attached XL Sheet. Please try to read the value from "FRU LVL-Total Recycle Req" Worksheet and you will get error randomly.

RAID800 RAID900 HM800 HM850 and HM900 DQC 10-21-21 Template Rev109.xlsx

and values are as per the screenshot:

[swmal]

Thanks for reporting. What EPPlus version are you using? I'll have a look at this and will use EPPlus 6.0.3 for the tests.

[swmal]

This problem because some cells in the workbook contains formulas like this (example from 'HM-RAID NVME Recycle Calc' worksheet, cell H4.):

SUM('Base Data'!F348:'Base Data'!F362)

EPPlus cannot handle this type of range reference (a worksheet reference at the right side of the colon). If you can change the format to 'Base Data'!F348:F362 it will work better.

We are working on a major rewrite of the formula calc where this will be addressed, but there is no short term solution for it.

[NavisiteNittin]

Thanks @swmal for response on this. Here are the answers of your two queries:

1. I have tried with two versions (4.5.3.3 & Latest version) but facing this problem.
2. Regarding the formula. My question if one worksheet using the following formula:

SUM('Base Data'!F348:'Base Data'!F362) Then error should come for all cell not for specific when you check the above screenshot few cells are giving the correct value or you can refer the attached XL sheet (already did).

Regards Nittin

[swmal]

It is most likely that the cells that has calculated successfully never hits the SUM('Base Data'!F348:'Base Data'!F362) formula. That formula is 5-6 dependencies away from these cells and you have IF-formulas on top of it.

[NavisiteNittin]

Hi @swmal , Any idea when this will be include in EPPlus?