Excel: remove #VALUE formula result

If a cell shows #VALUE it is because it cannot evaluate what you asked for.
eg If A1 has a value of 5, and A2 has "No", then =SUM(A1:A5) will give #VALUE because it cannot add a number and text together. 

=IF(ISERROR(SUM(A1:A2)),""No Data",SUM(A1:A2))

So you can get around this by querying if there is an error: =IF(ISERROR(SUM(A1:A2)),""No Data",SUM(A1:A2))
Thus if there is an error, the formula returns "No Data", otherwise you get the sum.

Similarly, for #DIV/0!, you can (should?) test to see if the denominator is 0. If it is then put "", else the result of the division.

Hope this helps