#value! error in sum function

[Art]

I need to sum a range of cells. Some of the cells may contain text. If so,
the function returns #value!. In Lotus 1-2-3, text is treated as 0 for
function purposes. Is there some way to force Excel to do the same, or
another way to get around this problem?

 

[T. Valko]

Instead of doing something like this:

=A1+A2+A3

Do it like this:

=SUM(A1:A3)

The SUM function ignores text.

 

[Lancelot]

I have a very similar problem to Art.

In my case the problem arises when my 1-2-3 sheet is saved as an Excel sheet
and then I get a heap of #VALUE! results in Excel.

The reason for this is because the 1-2-3 formulas contain a lot of ""s,
which is text and can only be added by Excel using =sum, or so I understood.

Unfortunately, =sum doesn't work when several non-contiguous cells are added
and the "" cell is deducted.

Example: =SUM(A1;B1;-C1) where C1 contains "" (as a result of a condition
not having been met in C1). The =sum formula shows #VALUE! only because the
"" in C1 is deducted instead of added.

What do I have to put in my C1 formula so that, when the condition there is
not met, it shows nothing and does not cause a #VALUE! elsewhere? C1 is not
allowed to show zero because that is an incorrect value.

What is the best solution?

 

[T. Valko]

Try something like this:

=SUM(A1:B1,IF(COUNT(C1),-C1))

 

[Lancelot]

Thank you very much T.

Your COUNT() solution solves a lot of #VALUE! cases in my spreadsheet.
However there are some cases where I have difficulty to know where to put
the COUNT(). They look like this:

=IF(OR(CK56=0;CN56=0);"";IF(CL56=0;ROUND((CK56+CN56)/2;0);ROUND((CK56+CL56+CN56)/3;0)))

All the mentioned cells in row 56 could contain "".

Could you help me with this case? I tried to put in one or two COUNT()s but
I couldn't get it right.

By the way, how would I design the formulas if I wrote the Excel spreadsheet
from scratch. Would I really need so many COUNT()s in the formulas?

 

[Lancelot]

Thank you very much T.

With your COUNT() solution I have eliminated most of the #VALUE!s from my
worksheet. However there is one remaining which I can't get right. It looks
like this:

=IF(OR(CK56=0;CN56=0);"";IF(CL56=0;ROUND((CK56+CN56)/2;0);ROUND((CK56+CL56+CN56)/3;0)))

Any of the cells in row 56 can and do contain "".

I tried to insert some COUNT()s in the formula, but it was not successful.
Could you spare a moment to help me with this one?

By the way, I wonder how I would formulate the Excel worksheet if I was
designing it from scratch. Would I really need to have all these COUNT()s in
the formulas - what do other users do?

 

[T. Valko]

Here's how you end up getting a #VALUE! error...

A1 = a formula that might return either a number or a formula blank "".
A2 = same thing

If either A1 or A2 contain the formula blank then:

=A1+A2 will return #VALUE!

Excel is telling you that you can't add TEXT values to numeric values.

In these cases use the SUM function. SUM will ignore any text entries which
is what a formula blank is.

=SUM(A1:A2)

So, with this formula:

=IF(OR(CK56=0;CN56=0);"";IF(CL56=0;ROUND((CK56+CN56)/2;0);ROUND((CK56+CL56+CN56)/3;0)))

Try it like this:

=IF(OR(CK56=0;CN56=0);"";IF(CL56=0;ROUND(SUM(CK56;CN56)/2;0);ROUND(SUM(CK56:CL56;CN56)/3;0)))

 

[Lancelot]

Thank you T.

Your formula solved my problem.
I only had to adapt it, so that where there is a condition with a value 0, I
had to change the 0 to "" in some cases, because the conditioning cell may
contain "".

P.S. Ignore my duplicated posting of the 10/15/2009 - there was a connection
problem on that day and I don't know how to remove or change my repeat
posting.

Thanks again,
Lancelot

 

[T. Valko]

You're welcome. Thanks for the feedback!