Test in Formulas

  • Thread starter Thread starter Farsin Derakhshan
  • Start date Start date
F

Farsin Derakhshan

I have a worksheet used for reporting expenses. Sometimes
cells contain numbers which are used to calculate taxes,
other times they contain label (or descriptions of
expenses)

When users enter label into the workbook, the fomulas
used to ignore them, but now we get #VALUE! errors. What
can I do to fix this?

- Farsin
 
I am not sure what you mean by post the formulas, but
this happens to all formulas. If A1=1, A2=2 and A3=A1+A2,
then A3=3. But if I change A1 to "Bob", A3=#VALUE!.

In previous versions of Excel, A1 would be ignored if it
was not a number (that is A3=2), now all I get is this
error message.
 
What previous version? Every time you calculate using an operand
and text you will get a value error, you can overcomes that by using the sum
function

=SUM(A1:A2)

or

=SUM(A1,A2)


If you previously were a Lotus user you could do that and you still can,
tools>options>transition and check transition formula evaluation
will do that but will sometimes cause side effects depending what type
of other formulas you are using. I would use SUM
 
Hi
you probably have use the SUM function and not the '+' operand (as this
would have thrown an error also in previous versions). Try in A3:
=SUM(A1,A2)

Though I'd recommend to design your spreadsheet in such a way that you
don't try to sum text values
 
Thanks for the information. I converted an old Lotus123
file and I guess it kept some of the Lotus functions.

For Excel, I solved the problem using a nested if/then
statement and the IFERROR function to check for problems
with the formulas. Works great now - just not quite
straight forward.

Thanks again.
 
Back
Top