Blogger
Share this content
0
2
813

#value

Hello Peeps

How can I eliminate this error in excel by replacing it with 0 (zero) using find/replace or whatever you think is best suited.

Thanks

Replies

Please login or register to join the discussion.

avatar
24th Jul 2012 14:14

If you are using Excel 2007 and beyond and have a formula, you can use:

Iferror(0).

something like iferror(if(value,0))

Or,

CTRL plus H will take you to find and replace page, then type on the top row #value and below row replaced with 0 and press replace all.

Thanks (1)
avatar
24th Jul 2012 15:04

Or (? pre 2007 but still works)

=IF(iserror(your formula)="false",your formula,0)

These work for any error but beware because it can hide things that you should be checking!

You CAN use =IF(iserror(your formula)="false",your formula,"ERROR")  which will highlight any errors but still enable the column(s) to add up.

This will highlight any errors but still enable the column(s) to add up.

THIS IS NOT DISPLAYING HOW IT LOOKS IN EDIT!!

Thanks (1)