Calculate an Excel column that contains numbers and #Values

[Guest]

Dear All,

I am trying to calculate a column in Excel but some of the cells within the
column have #Value in them because of other calculations within the
worksheet. Is there anyway to calculate the numbers in a column (in this case
the number are set as a currency) and ignore the #Value that is in some of
the cells.

Thanks

 

[Dave O]

One strategy is to change the formulas that are returning #VALUE to an
IF statement, maybe something like this:
=IF(ISERROR(your formula here),"",your formula here)

This evaluates the formula and returns nothing if the formula returns
an error, but returns the formula's result if it is not an error.

 

[Guest]

Hi Dave O

Thanks, but I can not get this to work, I am a bit of a beginer in Excel so
maybe I have not explained what I am trying to do properly.

€18.00 #VALUE! #VALUE!
0 €425.00 €0.00 £0.00
€18.00 #VALUE! #VALUE!

1 €2,170.00 €2,170.00 £1,446.67
Y 8 €63.00 €504.00 £336.00

1 €460.00 €460.00 £306.67
Y 8 €40.00 €320.00 £213.33

€0.00 £0.00
€0.00 £0.00
€0.00 £0.00
€0.00 £0.00
Total #VALUE! #VALUE!

As you can see above because the formula coming across is delivering a
#value (which is correct) I can not total the column. I need some way
ignoring the #Value so that I can total the column

 

[Guest]

What is the formula in your Total line?

Hi Dave O

Thanks, but I can not get this to work, I am a bit of a beginer in Excel so
maybe I have not explained what I am trying to do properly.

€18.00 #VALUE! #VALUE!
0 €425.00 €0.00 £0.00
€18.00 #VALUE! #VALUE!

1 €2,170.00 €2,170.00 £1,446.67
Y 8 €63.00 €504.00 £336.00

1 €460.00 €460.00 £306.67
Y 8 €40.00 €320.00 £213.33

€0.00 £0.00
€0.00 £0.00
€0.00 £0.00
€0.00 £0.00
Total #VALUE! #VALUE!

As you can see above because the formula coming across is delivering a
#value (which is correct) I can not total the column. I need some way
ignoring the #Value so that I can total the column