Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
3 Similar formulas, one returns #VALUE!
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). |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
3 Similar formulas, one returns #VALUE!
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 On Sep 2, 8:04*am, theredspecial wrote: 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). |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
3 Similar formulas, one returns #VALUE!
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) "Pete_UK" wrote: 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 On Sep 2, 8:04 am, theredspecial wrote: 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). |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
3 Similar formulas, one returns #VALUE!
Are you sure that the addition in =(C1+C3)*IndirectCosts wouldn't do it?
-- David Biddulph "Pete_UK" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF and similar formulas | Excel Worksheet Functions | |||
Index with mulitple value returns and muliple column returns | Excel Worksheet Functions | |||
Same formulas, but one returns a #DIV/0! error | Excel Discussion (Misc queries) | |||
SUMPRODUCT is OK for a while and later a similar formula returns 0 | Excel Worksheet Functions | |||
Need similar formulas to do the job | Excel Worksheet Functions |