Including destination cell value in a formula

**Alko_Holik** · 03-28-2009, 06:53 PM

Hello,

Hope someone could help. I have the following problem:

There is a big range of cells with normal numbers (ex. 100, 150 .. etc), but I need to convert them in the following formulas that give the same numbers as a result: for example if the cell value is 100, I need to convert it in =if(iserror(100);0;100) and so on with all other values. Is it possible this to be done automatically for all cells?
Thank you in advance!

**shg** · 03-28-2009, 07:02 PM

Welcome to the forum.

Literal numbers are not errors, so doing that wouldn't accomplish anything.

**Alko_Holik** · 03-28-2009, 07:15 PM

I know this is absolute non sense and don't know why I am asked to do this. Someone before me has filled the table like this and I am supposed to continue, but can't really find a way to do it except manual typing, which would take weeks.

**shg** · 03-28-2009, 07:23 PM

1. In B1 and copy down, ="=if(iserror(" & A1 & "), 0, " & A1 & ")". Change commas to semicolons if appropriate.

2. Select column B, copy, Edit > Paste Special, Values

3. With column B still selected, Data > Text to columns, Finish.

**Alko_Holik** · 03-28-2009, 07:39 PM

It works. Thank you very much!!!

You saved me a great headache

!

**shg** · 03-28-2009, 07:40 PM

You’re welcome. Would you please mark the thread as Solved?

Click the Edit button on your first post in the thread

Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes