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

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

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