Computations involving cells containing text

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

How do I avoid getting a #VALUE! return when a formula I've standardized for
repetitive rows or columns may involve cells containing text?
 
Not quite with you, but you can use ISERROR or ISERR for this

=IF(ISERROR(Your_Formula),"",Your_formula)

You can replace "" with 0 if you like

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
Nick Hodge said:
Not quite with you, but you can use ISERROR or ISERR for this

=IF(ISERROR(Your_Formula),"",Your_formula)

You can replace "" with 0 if you like

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
Click to expand...
 
Nick Hodge said:
Not quite with you, but you can use ISERROR or ISERR for this

=IF(ISERROR(Your_Formula),"",Your_formula)

You can replace "" with 0 if you like

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)




Nick -- thanxmuch for the response. I'm compiling results from several pages onto a summary page. The consistency of the data is erratic because some of the data goes back 10 years while some only 1 or 2, for example.
Click to expand...

On the summary page I need to compile the available data regardless of how
far back it goes, and the problem is that I can't just copy and paste the
formulas under these circumstances. For instance, a simple formula compiles
the sum of all of the data points found in the same cell on each of the 10
pages.

This works fine if there is data in that cell on each of those 10 pages.
However, if the data in this case only goes back 2 years/pages, the
corresponding cells on the previous 8 pages will contain "---". In this
case, the standardized formula on the summary page returns #VALUE!, in which
case I have to modify each such formula {100s of them} to account for only
those cells with data in them.

Is there any way to have Excel treat "---" cells as equivalent to 0 and
complete the calculation?
 
To be honest, I would replace the --- with a blank cell or a 0. It would
make life much easier.

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
 
Nick Hodge said:
To be honest, I would replace the --- with a blank cell or a 0. It would
make life much easier.
Click to expand...
Found the solution ...
Unlike Quattro Pro, from which I am transferring 10 years worth of work,
Excel apparently can't pull data from separate sheets if some cells contain
text when using a simple formula such as
"=+'1992'!AB11+'1993'!AB11+'1994'!AB11+'1995'!AB11+ {etc thru 2004}" IF the
data looks something like "---"+"---"+2,111.36+6,545.91+ {etc thru 2004}
where "---" is text, i.e., no value for that year. This returns a #VALUE!
message.

Going back and entering "0" won't work, as there is a distinction between a
zero value and no value that has to be maintained.

However, using a SUM formula works, eg
"=sum('1992'!AB11+'1993'!AB11+'1994'!AB11+'1995'!AB11+ {etc thru 2004}".

As I said, I'm converting from Quattro; had to ... it started making up its
own numbers.

Thanks again.
 
Back
Top