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.