Problem
One of the more frustrating features in Excel is its ability to automatically change a cells format – based on it’s content.
For example – a cell a currency will automatically change to text.
This often breaks formulas with Excel suddenly deciding that a cell contains text and not numbers.
This is even more frustrating when it happens in report templates where you end up having to look for and fix the error every time you run the report.
Solution
Fortunately there’s a simple solution – wrap the cell reference in the VALUE function.
The VALUE function will force the cell contents to be a value regardless of its formatting settings.
For example – instead of =SUM(B2:D2)) you would use
=SUM(VALUE(B2:D2))
