[Webinar] Streamline your web hosting managementRegister Today

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 205
  • Last Modified:

Combine text values and numerical values but avoid the "#VALUE!" alert


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.

Fig. 1In 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


3 Solutions

pls try


Open in new window

I fixed by example 2

Since you have Excel 2010 or later, you can use the AGGREGATE function instead of SUM. AGGREGATE has the ability to ignore error values. For example:
=SUM($E14:$H14)               returns #VALUE! error value if one of the cells contains #VALUE!
=AGGREGATE(9,6,$E14:$H14)         returns the sum while ignoring any error values

Also, for returning the sum of the digits, you might consider a formula like:
This formula works for numbers up to six digits in length.
RyanProject Engineer, ElectricalCommented:
You can also format a cell so the forecolor and backcolor match, hiding the text when there's an error.

Steve_BradyAuthor Commented:

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now