Calculate an Excel column that contains numbers and #Values
 

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

Calculate an Excel column that contains numbers and #Values
 

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.

Calculate an Excel column that contains numbers and #Values
 

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


"Dave O" wrote:

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.

Calculate an Excel column that contains numbers and #Values
 

What is the formula in your Total line?



"Kevin" wrote:

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


"Dave O" wrote:

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.