3 Similar formulas, one returns #VALUE!

[theredspecial]

Dear all,

I've built a fairly complicated sheet with many references to other cells.
Many of these references are named (e.g. cell A76 is called IndirectCosts).
The references are used throughout the file.

In at least one case there appears to be an error. The situation is as below:
(column A) (column B) (column C)
=Ref11 =Ref12 =Ref13 (row 1)
=Ref21 =Ref22 =Ref23 (row 2)
=Ref31 =Ref32 =Ref33 (row 3)
=(A1+A3)*IndirectCosts >>> see below

The formula (A1+A3)*IndirectCosts is repeated in columns B and C, the only
change to the formula is that A1 becomes B1 and A3 becomes B3, etc.

However, in column C the formula returns #VALUE!, whereas in columns A and B
there is an outcome (the correct outcome).

There is no type in the formula (I've made the formula by 'clicking in the
cell referred to).

Any ideas on how to solve this? I can not rename the cell (well I can, but
then I will end up with loads of errors).

thx
Robert


(The actual names are different, but the situation is clear I assume).

 

[Pete_UK]

Either C1 or C3 (or both) contains a text value - you get that error
when you try to do arithmetic on text values, i.e. values which are
text but which may look like numbers. You need to check out the cells
given by references Ref13 and Ref33. You might find that this works
for you:

=(C1*1+C3*1)*IndirectCosts

Multiplying the terms by 1 will convert them into numbers (if they are
"convertible").

Hope this helps.

Pete

 

[theredspecial]

Still can't make it work, some additional info

- When I evaluate the formula "IndirectCosts" produces the error in column
C, not in A or B
- C1 and C3 are indeed numbers, as are their sources (well, accounting
actually, but so are the sources of e.g. B1 and B3)
- IndirectCosts is not a single cell, but three merged cells (in a row)

 

[David Biddulph]

Are you sure that the addition in =(C1+C3)*IndirectCosts wouldn't do it?

 

[theredspecial]

Thanks, that did it - just to be sure I will check all definitions.