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

 

[p45cal]

Dear all,

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

In at least one case there appears to be an error. The situation is a
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, th
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
and B
there is an outcome (the correct outcome).

There is no type in the formula (I've made the formula by 'clicking i
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).

What's the betting that there's either text or an error in one or bot
of the cells Ref13 and Ref33, or that one or both of those ranges is no
just a single cell

 

[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)

 

[p45cal]

post a cut down version of your workbook which still displays the erro
at thecodecage.co

 

[theredspecial]

please find one attached, I've removed all non-necessary data an
replaced confidential information with imaginary examples.

The error occurs in the sheet "Overview", cell I16

post a cut down version of your workbook which still displays the erro
in this thread at thecodecage.co

+-------------------------------------------------------------------
|Filename: problematic (example).xls
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=246
+-------------------------------------------------------------------

 

[p45cal]

Change the Refers to: of the -Name -*IndirectCostRate *from:
*='START HERE'!$F$28:$H$28*
to:
*='START HERE'!$F$28

 

[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.