Hello,

Is there a way to perform numerical functions in Excel (2013) when text values are included

*and* avoid the "#VALUE!" alert? I generally try to write formulas so that a backtick "`" appears when no result is obtained. It's small and unobtrusive and perfect, imo, as a reminder that a formula is in that cell. However, in many cases I end up with a bunch of big "#VALUE!" alerts which I wish could be replaced by something more subtle.

As an example, I have attached a spreadsheet from which the below screenshot is taken.

*By the way, ***my question has nothing whatsoever to do with the displayed formula** (which is for breaking down a number into its single digits regardless of how many digits the number contains) **but rather, how to avoid the "#VALUE!" alerts.**
In the first two examples, the block of cells with borders contains the same formula. In each case, the formula is shown for the cell shaded orange. In Example #3, the block was filled manually because a formula which will provide those results is what I'm after.

In all three examples, Column J contains a =Sum() formula which is shown in blue.

The =SUM() function works fine if the defined range includes text values

*and* I can always change text numbers to numerical numbers by enclosing the result in a =VALUE() function. However, trying to combine the two processes is like mixing oil & water —

*they do not cohabitate serenely.* :P

Thanks

2014-01-29-EE.xlsx
pls try

Open in new window

Regards