Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please help - formula works in some cells but evaluates to #value in others
Hi,
I was hoping some one could help. I am working with 2 workbooks. The one has 31 worksheets (one for each day of the month) the other uses the inputs from each day and calculates what should be left. Its fairly simple (adding the values of the two cells and subtracting a third cell) The formula works for the first three days however it evalutes to #value there after. I cant understand why the identical formula suddenly causes an error. Could anyone please offer suggestions as to what can be causing this error. Thanks in advance Ullie N.B: this same thing has happend with two different formulas. e.g1: =E163-(E58+E8)..... this works however after that it doesnt one doesnt i.e. =F163-(F58+F8) e.g2: ='MSA 04.06.06'!B16:C16-'[Test.xls]June Daily Sales-Units'!$F$58.... this works but the next one doest and it also returns #value if i want to add another cell to it. i.e: stock ordered |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please help - formula works in some cells but evaluates to #value in others
It might be that you have spaces in the cells which are causing the
errors to appear. These cells will appear blank (and so you think the arithmetic should work), but they are not true blanks, so you get the error messages. You may have formulae in these cells along the lines of: =IF(condition,action," ") or =IF(condition,action,"") and both of these will return errors in later arithmetic operations. You should change these to return a zero instead of " " or "". Hope this helps. Pete Ullie wrote: Hi, I was hoping some one could help. I am working with 2 workbooks. The one has 31 worksheets (one for each day of the month) the other uses the inputs from each day and calculates what should be left. Its fairly simple (adding the values of the two cells and subtracting a third cell) The formula works for the first three days however it evalutes to #value there after. I cant understand why the identical formula suddenly causes an error. Could anyone please offer suggestions as to what can be causing this error. Thanks in advance Ullie N.B: this same thing has happend with two different formulas. e.g1: =E163-(E58+E8)..... this works however after that it doesnt one doesnt i.e. =F163-(F58+F8) e.g2: ='MSA 04.06.06'!B16:C16-'[Test.xls]June Daily Sales-Units'!$F$58.... this works but the next one doest and it also returns #value if i want to add another cell to it. i.e: stock ordered |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Please help - formula works in some cells but evaluates to #value in others
Pete_UK wrote:
It might be that you have spaces in the cells which are causing the errors to appear. These cells will appear blank (and so you think the arithmetic should work), but they are not true blanks, so you get the error messages. You may have formulae in these cells along the lines of: =IF(condition,action," ") or =IF(condition,action,"") and both of these will return errors in later arithmetic operations. You should change these to return a zero instead of " " or "". Hope this helps. Pete Ullie wrote: Hi, I was hoping some one could help. I am working with 2 workbooks. The one has 31 worksheets (one for each day of the month) the other uses the inputs from each day and calculates what should be left. Its fairly simple (adding the values of the two cells and subtracting a third cell) The formula works for the first three days however it evalutes to #value there after. I cant understand why the identical formula suddenly causes an error. Could anyone please offer suggestions as to what can be causing this error. Thanks in advance Ullie N.B: this same thing has happend with two different formulas. e.g1: =E163-(E58+E8)..... this works however after that it doesnt one doesnt i.e. =F163-(F58+F8) e.g2: ='MSA 04.06.06'!B16:C16-'[Test.xls]June Daily Sales-Units'!$F$58.... this works but the next one doest and it also returns #value if i want to add another cell to it. i.e: stock ordered Hi Pete, Thanks for your response. I tried your suggestion but it still retrieves the same result. I'll keep playing around with it and i'll let you know if i'm successfull. Kind regards, Ulrich |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Returning a cell's formula in a different cell | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions | |||
quick way to copy-paste a formula linked to cells in another file | Excel Worksheet Functions | |||
Formula works in some cells, doesn't in other | New Users to Excel | |||
trying to create an (almost) circular formula between cells and data validated cells with lists | Excel Worksheet Functions |