replace #N/A value in EXCEL by 0 ????

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DAVID - 14 Dec 2007 15:16 GMT

I'm using very large EXCEL Sheet for import of external data. After I make
new calculations, which will be shown in the master-TAB.
I receive many "#N/A" values of course, but I don't care about. I just like
to make total calculation of the colums. But result of calculation for
certain range (column) which contains the "#N/A" values, gives me back a
"#N/A" value.
Is there a way to REPLACE the "#N/A" values by a "0" ????
Just like "REPLACE" ....

Thanks for your help, which would be appreciated

Reply to this Message

Jerry W. Lewis - 16 Dec 2007 06:18 GMT

=IF(ISNA(x),0,x)

As long as you are only totalling results, this should work fine. If you
want averages, etc, then you should probably use "" instead of 0.

Jerry

> I'm using very large EXCEL Sheet for import of external data. After I make
> new calculations, which will be shown in the master-TAB.
[quoted text clipped - 6 lines]
>
> Thanks for your help, which would be appreciated

Reply to this Message