All this is answered in the FAQ paragraph:
ClosedXML doesn't resolve formulas so the next best thing is to allow you to get the
value calculated by Excel (ValueCached). Since the user can turn off automatic calculation of formulas the ValueCached property is on a "use at your own risk" basis. In other words,
if you don't know for sure that the
formulas have been calculated by Excel then don't use ValueCached.
In this case you won't have the formula results.
I'll try to paraphrase a little bit...
ClosedXML doesn't calculate the results from the formulas. It can only give you the results which the Microsoft Excel application saves when the user saves a file. When you add a formula using ClosedXML the ValueCached is not set because this is something
the Excel application does.
