Hi


Is there a Function or can a macro be used that will ignore #VALUE in a sum formula?



Thanks



Nick

If the reference in your formula includes a cell with an error, it will return an error value. But if you mean you would not want error values appearing after executing your formula, use =IFERROR(your formula),"")

Hi Marcia


Thank you for your reply, still having an issue adding the IFERROR function to work with the formula below

=MID(E3,2,10)+0


I have tried =IFERROR((MID(E3,2,10)+0),"")

Any ideas why this is wrong?


Thanks

Nick

Try: =IFERROR(MID(E3,2,10)+0,""), this will return a blank cell, without the IFERROR function you will receive an error message. May I know the string in E3 from which you are trying to extract 10 characters?

Hi Marcia

The value I am extracting is between M1 and M12, so 10 is OTT, M value is selected from a drop down list, so I don't know if that complicates matters. I tried the IFERROR formula and now returning #NAME.


Thanks


Nick

IFERROR is not available in Excel 2003...




try:


=IF(ISERROR(MID(E3,2,10)+0),"",MID(E3,2,10)+0)

That has sorted it.


Many Thanks to you both, better lobby for an Excel update to this decade

[quote=NBVC;135290]IFERROR is not available in Excel 2003.


OMG, it never entered my mind to check the version Nick is using. Thank you.