Excel: If statement with #N/A
I have over 6000 records and half of them are formulas that are missing a variable so they result in #N/A on the spreadsheet, what i want to do is if the cell is #N/A then leave the cell blank, otherwise print a string like so
=IF(AR6347="#N/A","","string in here")
But this does not work with ="#N/A", is there a way to do this?
microsoft-excel
migrated from stackoverflow.com Sep 17 '12 at 17:23
This question came from our site for professional and enthusiast programmers.
add a comment |
I have over 6000 records and half of them are formulas that are missing a variable so they result in #N/A on the spreadsheet, what i want to do is if the cell is #N/A then leave the cell blank, otherwise print a string like so
=IF(AR6347="#N/A","","string in here")
But this does not work with ="#N/A", is there a way to do this?
microsoft-excel
migrated from stackoverflow.com Sep 17 '12 at 17:23
This question came from our site for professional and enthusiast programmers.
add a comment |
I have over 6000 records and half of them are formulas that are missing a variable so they result in #N/A on the spreadsheet, what i want to do is if the cell is #N/A then leave the cell blank, otherwise print a string like so
=IF(AR6347="#N/A","","string in here")
But this does not work with ="#N/A", is there a way to do this?
microsoft-excel
I have over 6000 records and half of them are formulas that are missing a variable so they result in #N/A on the spreadsheet, what i want to do is if the cell is #N/A then leave the cell blank, otherwise print a string like so
=IF(AR6347="#N/A","","string in here")
But this does not work with ="#N/A", is there a way to do this?
microsoft-excel
microsoft-excel
asked Sep 17 '12 at 13:49
newSpringer
migrated from stackoverflow.com Sep 17 '12 at 17:23
This question came from our site for professional and enthusiast programmers.
migrated from stackoverflow.com Sep 17 '12 at 17:23
This question came from our site for professional and enthusiast programmers.
add a comment |
add a comment |
5 Answers
5
active
oldest
votes
Try using the ISNA() function:
=IF(ISNA(AR6347),"","string in here")
answered Sep 17 '12 at 13:53
RocketDonkeyRocketDonkey
69656
this will do it for individual cells but it will not let me copy this down the 6000 cells, gives out withthis operation requires the merged cells to be identically sized, is there a way to do this so i can copy it for all cells?
– newSpringer
Sep 17 '12 at 13:59
Sounds like some of the cells in your target column are merged. Assuming that you do need them to stay merged, follow the instructions from saruman576 midway down mrexcel.com/forum/excel-questions/74144-find-merged-cells.html (using theFormatoption to find merged cells). You could then copy the formula up until each merged cell and then handle the merged cell separately. However I would suggest looking into Center Across Selection if applicable (thenewpaperclip.com/2008/04/15/…). Hope this helps!
– RocketDonkey
Sep 17 '12 at 14:10
i was looking into this and the cells are not merged (went into format cells) so this not this :/
– newSpringer
Sep 17 '12 at 14:45
Are there no merged cells anywhere in that entire column? What it sounds like it happening is when you are trying to copy the formula down, it is running into a merge cell, which expands the copy range column-wise to include that entire cell (which will also pick up merged cells in the second column and then expand them, etc.). If you don't require any merged cells, try selecting everything and then unmerge the cells (in Excel 2007, Home -> Alignment -> Unmerge All Cells [in the Merge And Center drop-down]).
– RocketDonkey
Sep 17 '12 at 14:51
ya did what you suggested and im still getting the same problem :/
– newSpringer
Sep 17 '12 at 14:57
|
show 5 more comments
In Excel 2007 and later you're able to use:
=IFERROR(A1;"")
to replace ="#N/A" or any other error with empty string.
answered Sep 18 '12 at 8:06
kurpkurp
8521614
add a comment |
Use the iserror() function. For instance, with a vlookup not finding a value in my table, I want to display Not found instead of #N/A, then I type the following:
=if(iserror(vlookup(A1,Sheet2!$A$1:$C$360,3,0)),'Not found',vlookup(A1,Sheet2!$A$1:$C$360,3,0))
So, this formula is just saying: if the vlookup function is retrieving an error, then return the string 'Not found', else return the result of the vlookup function.
edited Sep 26 '12 at 14:39
jonsca
2,993112539
answered Sep 19 '12 at 8:07
Nicolas C.Nicolas C.
634
add a comment |
SIMPLEST METHOD
You can use this directly in the cell with the formula if you want to skip the intermediate cell steps
=IFNA(formula,"text/value if formula result is #N/A")
This will put the result of the formula in the cell (if the result is not #N/A) and will put the text string (or whatever value you put as the second argument) in the cell instead if the formula result is #N/A.
I use it with VLOOKUP and INDEX-MATCH all the time when I don't want the #N/A's to show.
I replace what would be an #N/A result with a blank cell ("") or zero(0) or text ("text string") as needed.
edited May 3 '16 at 0:51
Burgi
3,88792543
answered May 2 '16 at 23:30
Jeffery HallJeffery Hall
113
add a comment |
I used something similar to determine if an item in A matched one in D and not display #N/A. Used for presentation purposes. =IF(IFERROR(MATCH(A4,$D$2:$D$11,0),0)>0,"text for TRUE","text for FALSE")
answered Oct 21 '15 at 23:40
BobBob
11
5
This is a ridiculously convoluted response when kurp's answer already describes the relevant way to use IFERROR.
– fixer1234
Oct 22 '15 at 0:18
add a comment |
5 Answers
5
active
oldest
votes
5 Answers
5
active
oldest
votes
active
oldest
votes
active
oldest
votes
Try using the ISNA() function:
=IF(ISNA(AR6347),"","string in here")
answered Sep 17 '12 at 13:53
RocketDonkeyRocketDonkey
69656
this will do it for individual cells but it will not let me copy this down the 6000 cells, gives out withthis operation requires the merged cells to be identically sized, is there a way to do this so i can copy it for all cells?
– newSpringer
Sep 17 '12 at 13:59
Sounds like some of the cells in your target column are merged. Assuming that you do need them to stay merged, follow the instructions from saruman576 midway down mrexcel.com/forum/excel-questions/74144-find-merged-cells.html (using theFormatoption to find merged cells). You could then copy the formula up until each merged cell and then handle the merged cell separately. However I would suggest looking into Center Across Selection if applicable (thenewpaperclip.com/2008/04/15/…). Hope this helps!
– RocketDonkey
Sep 17 '12 at 14:10
i was looking into this and the cells are not merged (went into format cells) so this not this :/
– newSpringer
Sep 17 '12 at 14:45
Are there no merged cells anywhere in that entire column? What it sounds like it happening is when you are trying to copy the formula down, it is running into a merge cell, which expands the copy range column-wise to include that entire cell (which will also pick up merged cells in the second column and then expand them, etc.). If you don't require any merged cells, try selecting everything and then unmerge the cells (in Excel 2007, Home -> Alignment -> Unmerge All Cells [in the Merge And Center drop-down]).
– RocketDonkey
Sep 17 '12 at 14:51
ya did what you suggested and im still getting the same problem :/
– newSpringer
Sep 17 '12 at 14:57
|
show 5 more comments
Try using the ISNA() function:
=IF(ISNA(AR6347),"","string in here")
answered Sep 17 '12 at 13:53
RocketDonkeyRocketDonkey
69656
this will do it for individual cells but it will not let me copy this down the 6000 cells, gives out withthis operation requires the merged cells to be identically sized, is there a way to do this so i can copy it for all cells?
– newSpringer
Sep 17 '12 at 13:59
Sounds like some of the cells in your target column are merged. Assuming that you do need them to stay merged, follow the instructions from saruman576 midway down mrexcel.com/forum/excel-questions/74144-find-merged-cells.html (using theFormatoption to find merged cells). You could then copy the formula up until each merged cell and then handle the merged cell separately. However I would suggest looking into Center Across Selection if applicable (thenewpaperclip.com/2008/04/15/…). Hope this helps!
– RocketDonkey
Sep 17 '12 at 14:10
i was looking into this and the cells are not merged (went into format cells) so this not this :/
– newSpringer
Sep 17 '12 at 14:45
Are there no merged cells anywhere in that entire column? What it sounds like it happening is when you are trying to copy the formula down, it is running into a merge cell, which expands the copy range column-wise to include that entire cell (which will also pick up merged cells in the second column and then expand them, etc.). If you don't require any merged cells, try selecting everything and then unmerge the cells (in Excel 2007, Home -> Alignment -> Unmerge All Cells [in the Merge And Center drop-down]).
– RocketDonkey
Sep 17 '12 at 14:51
ya did what you suggested and im still getting the same problem :/
– newSpringer
Sep 17 '12 at 14:57
|
show 5 more comments
Try using the ISNA() function:
=IF(ISNA(AR6347),"","string in here")
answered Sep 17 '12 at 13:53
RocketDonkeyRocketDonkey
69656
Try using the ISNA() function:
=IF(ISNA(AR6347),"","string in here")
answered Sep 17 '12 at 13:53
RocketDonkeyRocketDonkey
69656
answered Sep 17 '12 at 13:53
RocketDonkeyRocketDonkey
69656
answered Sep 17 '12 at 13:53
RocketDonkeyRocketDonkey
69656
answered Sep 17 '12 at 13:53
RocketDonkeyRocketDonkey
69656
69656
this will do it for individual cells but it will not let me copy this down the 6000 cells, gives out withthis operation requires the merged cells to be identically sized, is there a way to do this so i can copy it for all cells?
– newSpringer
Sep 17 '12 at 13:59
Sounds like some of the cells in your target column are merged. Assuming that you do need them to stay merged, follow the instructions from saruman576 midway down mrexcel.com/forum/excel-questions/74144-find-merged-cells.html (using theFormatoption to find merged cells). You could then copy the formula up until each merged cell and then handle the merged cell separately. However I would suggest looking into Center Across Selection if applicable (thenewpaperclip.com/2008/04/15/…). Hope this helps!
– RocketDonkey
Sep 17 '12 at 14:10
i was looking into this and the cells are not merged (went into format cells) so this not this :/
– newSpringer
Sep 17 '12 at 14:45
Are there no merged cells anywhere in that entire column? What it sounds like it happening is when you are trying to copy the formula down, it is running into a merge cell, which expands the copy range column-wise to include that entire cell (which will also pick up merged cells in the second column and then expand them, etc.). If you don't require any merged cells, try selecting everything and then unmerge the cells (in Excel 2007, Home -> Alignment -> Unmerge All Cells [in the Merge And Center drop-down]).
– RocketDonkey
Sep 17 '12 at 14:51
ya did what you suggested and im still getting the same problem :/
– newSpringer
Sep 17 '12 at 14:57
|
show 5 more comments
this will do it for individual cells but it will not let me copy this down the 6000 cells, gives out withthis operation requires the merged cells to be identically sized, is there a way to do this so i can copy it for all cells?
– newSpringer
Sep 17 '12 at 13:59
Sounds like some of the cells in your target column are merged. Assuming that you do need them to stay merged, follow the instructions from saruman576 midway down mrexcel.com/forum/excel-questions/74144-find-merged-cells.html (using theFormatoption to find merged cells). You could then copy the formula up until each merged cell and then handle the merged cell separately. However I would suggest looking into Center Across Selection if applicable (thenewpaperclip.com/2008/04/15/…). Hope this helps!
– RocketDonkey
Sep 17 '12 at 14:10
i was looking into this and the cells are not merged (went into format cells) so this not this :/
– newSpringer
Sep 17 '12 at 14:45
Are there no merged cells anywhere in that entire column? What it sounds like it happening is when you are trying to copy the formula down, it is running into a merge cell, which expands the copy range column-wise to include that entire cell (which will also pick up merged cells in the second column and then expand them, etc.). If you don't require any merged cells, try selecting everything and then unmerge the cells (in Excel 2007, Home -> Alignment -> Unmerge All Cells [in the Merge And Center drop-down]).
– RocketDonkey
Sep 17 '12 at 14:51
ya did what you suggested and im still getting the same problem :/
– newSpringer
Sep 17 '12 at 14:57
this will do it for individual cells but it will not let me copy this down the 6000 cells, gives out with
this operation requires the merged cells to be identically sized, is there a way to do this so i can copy it for all cells?– newSpringer
Sep 17 '12 at 13:59
this will do it for individual cells but it will not let me copy this down the 6000 cells, gives out with
this operation requires the merged cells to be identically sized, is there a way to do this so i can copy it for all cells?– newSpringer
Sep 17 '12 at 13:59
Sounds like some of the cells in your target column are merged. Assuming that you do need them to stay merged, follow the instructions from saruman576 midway down mrexcel.com/forum/excel-questions/74144-find-merged-cells.html (using the
Format option to find merged cells). You could then copy the formula up until each merged cell and then handle the merged cell separately. However I would suggest looking into Center Across Selection if applicable (thenewpaperclip.com/2008/04/15/…). Hope this helps!– RocketDonkey
Sep 17 '12 at 14:10
Sounds like some of the cells in your target column are merged. Assuming that you do need them to stay merged, follow the instructions from saruman576 midway down mrexcel.com/forum/excel-questions/74144-find-merged-cells.html (using the
Format option to find merged cells). You could then copy the formula up until each merged cell and then handle the merged cell separately. However I would suggest looking into Center Across Selection if applicable (thenewpaperclip.com/2008/04/15/…). Hope this helps!– RocketDonkey
Sep 17 '12 at 14:10
i was looking into this and the cells are not merged (went into format cells) so this not this :/
– newSpringer
Sep 17 '12 at 14:45
i was looking into this and the cells are not merged (went into format cells) so this not this :/
– newSpringer
Sep 17 '12 at 14:45
Are there no merged cells anywhere in that entire column? What it sounds like it happening is when you are trying to copy the formula down, it is running into a merge cell, which expands the copy range column-wise to include that entire cell (which will also pick up merged cells in the second column and then expand them, etc.). If you don't require any merged cells, try selecting everything and then unmerge the cells (in Excel 2007, Home -> Alignment -> Unmerge All Cells [in the Merge And Center drop-down]).
– RocketDonkey
Sep 17 '12 at 14:51
Are there no merged cells anywhere in that entire column? What it sounds like it happening is when you are trying to copy the formula down, it is running into a merge cell, which expands the copy range column-wise to include that entire cell (which will also pick up merged cells in the second column and then expand them, etc.). If you don't require any merged cells, try selecting everything and then unmerge the cells (in Excel 2007, Home -> Alignment -> Unmerge All Cells [in the Merge And Center drop-down]).
– RocketDonkey
Sep 17 '12 at 14:51
ya did what you suggested and im still getting the same problem :/
– newSpringer
Sep 17 '12 at 14:57
ya did what you suggested and im still getting the same problem :/
– newSpringer
Sep 17 '12 at 14:57
|
show 5 more comments
In Excel 2007 and later you're able to use:
=IFERROR(A1;"")
to replace ="#N/A" or any other error with empty string.
answered Sep 18 '12 at 8:06
kurpkurp
8521614
add a comment |
In Excel 2007 and later you're able to use:
=IFERROR(A1;"")
to replace ="#N/A" or any other error with empty string.
answered Sep 18 '12 at 8:06
kurpkurp
8521614
add a comment |
In Excel 2007 and later you're able to use:
=IFERROR(A1;"")
to replace ="#N/A" or any other error with empty string.
answered Sep 18 '12 at 8:06
kurpkurp
8521614
In Excel 2007 and later you're able to use:
=IFERROR(A1;"")
to replace ="#N/A" or any other error with empty string.
answered Sep 18 '12 at 8:06
kurpkurp
8521614
answered Sep 18 '12 at 8:06
kurpkurp
8521614
answered Sep 18 '12 at 8:06
kurpkurp
8521614
answered Sep 18 '12 at 8:06
kurpkurp
8521614
8521614
add a comment |
add a comment |
Use the iserror() function. For instance, with a vlookup not finding a value in my table, I want to display Not found instead of #N/A, then I type the following:
=if(iserror(vlookup(A1,Sheet2!$A$1:$C$360,3,0)),'Not found',vlookup(A1,Sheet2!$A$1:$C$360,3,0))
So, this formula is just saying: if the vlookup function is retrieving an error, then return the string 'Not found', else return the result of the vlookup function.
edited Sep 26 '12 at 14:39
jonsca
2,993112539
answered Sep 19 '12 at 8:07
Nicolas C.Nicolas C.
634
add a comment |
Use the iserror() function. For instance, with a vlookup not finding a value in my table, I want to display Not found instead of #N/A, then I type the following:
=if(iserror(vlookup(A1,Sheet2!$A$1:$C$360,3,0)),'Not found',vlookup(A1,Sheet2!$A$1:$C$360,3,0))
So, this formula is just saying: if the vlookup function is retrieving an error, then return the string 'Not found', else return the result of the vlookup function.
edited Sep 26 '12 at 14:39
jonsca
2,993112539
answered Sep 19 '12 at 8:07
Nicolas C.Nicolas C.
634
add a comment |
Use the iserror() function. For instance, with a vlookup not finding a value in my table, I want to display Not found instead of #N/A, then I type the following:
=if(iserror(vlookup(A1,Sheet2!$A$1:$C$360,3,0)),'Not found',vlookup(A1,Sheet2!$A$1:$C$360,3,0))
So, this formula is just saying: if the vlookup function is retrieving an error, then return the string 'Not found', else return the result of the vlookup function.
edited Sep 26 '12 at 14:39
jonsca
2,993112539
answered Sep 19 '12 at 8:07
Nicolas C.Nicolas C.
634
Use the iserror() function. For instance, with a vlookup not finding a value in my table, I want to display Not found instead of #N/A, then I type the following:
=if(iserror(vlookup(A1,Sheet2!$A$1:$C$360,3,0)),'Not found',vlookup(A1,Sheet2!$A$1:$C$360,3,0))
So, this formula is just saying: if the vlookup function is retrieving an error, then return the string 'Not found', else return the result of the vlookup function.
edited Sep 26 '12 at 14:39
jonsca
2,993112539
answered Sep 19 '12 at 8:07
Nicolas C.Nicolas C.
634
edited Sep 26 '12 at 14:39
jonsca
2,993112539
edited Sep 26 '12 at 14:39
jonsca
2,993112539
edited Sep 26 '12 at 14:39
jonsca
2,993112539
2,993112539
answered Sep 19 '12 at 8:07
Nicolas C.Nicolas C.
634
answered Sep 19 '12 at 8:07
Nicolas C.Nicolas C.
634
answered Sep 19 '12 at 8:07
Nicolas C.Nicolas C.
634
634
add a comment |
add a comment |
SIMPLEST METHOD
You can use this directly in the cell with the formula if you want to skip the intermediate cell steps
=IFNA(formula,"text/value if formula result is #N/A")
This will put the result of the formula in the cell (if the result is not #N/A) and will put the text string (or whatever value you put as the second argument) in the cell instead if the formula result is #N/A.
I use it with VLOOKUP and INDEX-MATCH all the time when I don't want the #N/A's to show.
I replace what would be an #N/A result with a blank cell ("") or zero(0) or text ("text string") as needed.
edited May 3 '16 at 0:51
Burgi
3,88792543
answered May 2 '16 at 23:30
Jeffery HallJeffery Hall
113
add a comment |
SIMPLEST METHOD
You can use this directly in the cell with the formula if you want to skip the intermediate cell steps
=IFNA(formula,"text/value if formula result is #N/A")
This will put the result of the formula in the cell (if the result is not #N/A) and will put the text string (or whatever value you put as the second argument) in the cell instead if the formula result is #N/A.
I use it with VLOOKUP and INDEX-MATCH all the time when I don't want the #N/A's to show.
I replace what would be an #N/A result with a blank cell ("") or zero(0) or text ("text string") as needed.
edited May 3 '16 at 0:51
Burgi
3,88792543
answered May 2 '16 at 23:30
Jeffery HallJeffery Hall
113
add a comment |
SIMPLEST METHOD
You can use this directly in the cell with the formula if you want to skip the intermediate cell steps
=IFNA(formula,"text/value if formula result is #N/A")
This will put the result of the formula in the cell (if the result is not #N/A) and will put the text string (or whatever value you put as the second argument) in the cell instead if the formula result is #N/A.
I use it with VLOOKUP and INDEX-MATCH all the time when I don't want the #N/A's to show.
I replace what would be an #N/A result with a blank cell ("") or zero(0) or text ("text string") as needed.
edited May 3 '16 at 0:51
Burgi
3,88792543
answered May 2 '16 at 23:30
Jeffery HallJeffery Hall
113
SIMPLEST METHOD
You can use this directly in the cell with the formula if you want to skip the intermediate cell steps
=IFNA(formula,"text/value if formula result is #N/A")
This will put the result of the formula in the cell (if the result is not #N/A) and will put the text string (or whatever value you put as the second argument) in the cell instead if the formula result is #N/A.
I use it with VLOOKUP and INDEX-MATCH all the time when I don't want the #N/A's to show.
I replace what would be an #N/A result with a blank cell ("") or zero(0) or text ("text string") as needed.
edited May 3 '16 at 0:51
Burgi
3,88792543
answered May 2 '16 at 23:30
Jeffery HallJeffery Hall
113
edited May 3 '16 at 0:51
Burgi
3,88792543
edited May 3 '16 at 0:51
Burgi
3,88792543
edited May 3 '16 at 0:51
Burgi
3,88792543
3,88792543
answered May 2 '16 at 23:30
Jeffery HallJeffery Hall
113
answered May 2 '16 at 23:30
Jeffery HallJeffery Hall
113
answered May 2 '16 at 23:30
Jeffery HallJeffery Hall
113
113
add a comment |
add a comment |
I used something similar to determine if an item in A matched one in D and not display #N/A. Used for presentation purposes. =IF(IFERROR(MATCH(A4,$D$2:$D$11,0),0)>0,"text for TRUE","text for FALSE")
answered Oct 21 '15 at 23:40
BobBob
11
5
This is a ridiculously convoluted response when kurp's answer already describes the relevant way to use IFERROR.
– fixer1234
Oct 22 '15 at 0:18
add a comment |
I used something similar to determine if an item in A matched one in D and not display #N/A. Used for presentation purposes. =IF(IFERROR(MATCH(A4,$D$2:$D$11,0),0)>0,"text for TRUE","text for FALSE")
answered Oct 21 '15 at 23:40
BobBob
11
5
This is a ridiculously convoluted response when kurp's answer already describes the relevant way to use IFERROR.
– fixer1234
Oct 22 '15 at 0:18
add a comment |
I used something similar to determine if an item in A matched one in D and not display #N/A. Used for presentation purposes. =IF(IFERROR(MATCH(A4,$D$2:$D$11,0),0)>0,"text for TRUE","text for FALSE")
answered Oct 21 '15 at 23:40
BobBob
11
I used something similar to determine if an item in A matched one in D and not display #N/A. Used for presentation purposes. =IF(IFERROR(MATCH(A4,$D$2:$D$11,0),0)>0,"text for TRUE","text for FALSE")
answered Oct 21 '15 at 23:40
BobBob
11
answered Oct 21 '15 at 23:40
BobBob
11
answered Oct 21 '15 at 23:40
BobBob
11
answered Oct 21 '15 at 23:40
BobBob
11
11
5
This is a ridiculously convoluted response when kurp's answer already describes the relevant way to use IFERROR.
– fixer1234
Oct 22 '15 at 0:18
add a comment |
5
This is a ridiculously convoluted response when kurp's answer already describes the relevant way to use IFERROR.
– fixer1234
Oct 22 '15 at 0:18
5
5
This is a ridiculously convoluted response when kurp's answer already describes the relevant way to use IFERROR.
– fixer1234
Oct 22 '15 at 0:18
This is a ridiculously convoluted response when kurp's answer already describes the relevant way to use IFERROR.
– fixer1234
Oct 22 '15 at 0:18
add a comment |
