Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I eliminate "false" from showing in a cell?
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I eliminate "false" from showing in a cell?
Show us your formula, and tell us what you want in its stead.
-- HTH Bob Phillips (replace somewhere in email address with googlemail if mailing direct) "thistooshallpass" wrote in message ... |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I eliminate "false" from showing in a cell?
How is FALSE getting there?
Assuming it's due to an IF() statement, put what you want to appear in the third argument: =IF(A1<B1,"It's true","It's not true") If you want the cell to *appear* blank: =IF(A1<B1,"It's true","") In article , thistooshallpass wrote: |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I eliminate "false" from showing in a cell?
Thanks for the response, however, this is the case.
I am using the vlookup formula, =vlookup(B9,Q12:R41,2,"") which produces "#N/A" when the lookup value cell is blank. So I decided to use an if formula, =if(B9<0,"0,if(B90,vlookup(B9,Q12:r41,2,0))) thinking that I could get rid of the #N/A, which I did but inherited 'false' in the cell. "JE McGimpsey" wrote: How is FALSE getting there? Assuming it's due to an IF() statement, put what you want to appear in the third argument: =IF(A1<B1,"It's true","It's not true") If you want the cell to *appear* blank: =IF(A1<B1,"It's true","") In article , thistooshallpass wrote: |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I eliminate "false" from showing in a cell?
Thanks for the response, however, this is the case.
I am using the vlookup formula, =vlookup(B9,Q12:R41,2,"") which produces "#N/A" when the lookup value cell is blank. So I decided to use an if formula, =if(B9<0,"0,if(B90,vlookup(B9,Q12:r41,2,0))) thinking that I could get rid of the #N/A, which I did but inherited 'false' in the cell. "Bob Phillips" wrote: Show us your formula, and tell us what you want in its stead. -- HTH Bob Phillips (replace somewhere in email address with googlemail if mailing direct) "thistooshallpass" wrote in message ... |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I eliminate "false" from showing in a cell?
A better way of avoiding the #NA error is to use ISNA, as follows:
=IF(ISNA(VLOOKUP(B9,Q12:R41,2,0),"",VLOOKUP(B9,Q12 :R41,2,0)) This will return a blank (i.e. "" in the middle of the formula) instead of #NA - you might like this to be zero (i.e. 0 without quotes in the middle of the formula). Hope this helps. Pete |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I eliminate "false" from showing in a cell?
Two ways,
=IF(B9="","",vlookup(B9,Q12:R41,2,False)) or more stable as it will also handle any invalid value =IF(ISNA(vlookup(B9,Q12:R41,2,False) ,"",vlookup(B9,Q12:R41,2,False) ) -- HTH Bob Phillips (replace somewhere in email address with googlemail if mailing direct) "thistooshallpass" wrote in message ... Thanks for the response, however, this is the case. I am using the vlookup formula, =vlookup(B9,Q12:R41,2,"") which produces "#N/A" when the lookup value cell is blank. So I decided to use an if formula, =if(B9<0,"0,if(B90,vlookup(B9,Q12:r41,2,0))) thinking that I could get rid of the #N/A, which I did but inherited 'false' in the cell. "Bob Phillips" wrote: Show us your formula, and tell us what you want in its stead. -- HTH Bob Phillips (replace somewhere in email address with googlemail if mailing direct) "thistooshallpass" wrote in message ... |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I eliminate "false" from showing in a cell?
Thanks for the response, however, i get an error message when i attempt to
input this formula. The "" are hightlighted from the error message indicating that the error is within the portion of the formula. I have also attempted to replace the "" with simply an 0 and other inputs but I still receive the error message. Am I missing something with the input? "Pete_UK" wrote: A better way of avoiding the #NA error is to use ISNA, as follows: =IF(ISNA(VLOOKUP(B9,Q12:R41,2,0),"",VLOOKUP(B9,Q12 :R41,2,0)) This will return a blank (i.e. "" in the middle of the formula) instead of #NA - you might like this to be zero (i.e. 0 without quotes in the middle of the formula). Hope this helps. Pete |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I eliminate "false" from showing in a cell?
Thanks. The first way worked, however, I still am not able to get pass the
error message which highlights the "" portion of the formula. "Bob Phillips" wrote: Two ways, =IF(B9="","",vlookup(B9,Q12:R41,2,False)) or more stable as it will also handle any invalid value =IF(ISNA(vlookup(B9,Q12:R41,2,False) ,"",vlookup(B9,Q12:R41,2,False) ) -- HTH Bob Phillips (replace somewhere in email address with googlemail if mailing direct) "thistooshallpass" wrote in message ... Thanks for the response, however, this is the case. I am using the vlookup formula, =vlookup(B9,Q12:R41,2,"") which produces "#N/A" when the lookup value cell is blank. So I decided to use an if formula, =if(B9<0,"0,if(B90,vlookup(B9,Q12:r41,2,0))) thinking that I could get rid of the #N/A, which I did but inherited 'false' in the cell. "Bob Phillips" wrote: Show us your formula, and tell us what you want in its stead. -- HTH Bob Phillips (replace somewhere in email address with googlemail if mailing direct) "thistooshallpass" wrote in message ... |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I eliminate "false" from showing in a cell?
One way:
=IF(ISNA(MATCH(B9,Q12:Q41,FALSE)),"",VLOOKUP(B9,Q1 2:R41,2,FALSE)) In article , thistooshallpass wrote: Thanks for the response, however, this is the case. I am using the vlookup formula, =vlookup(B9,Q12:R41,2,"") which produces "#N/A" when the lookup value cell is blank. So I decided to use an if formula, =if(B9<0,"0,if(B90,vlookup(B9,Q12:r41,2,0))) thinking that I could get rid of the #N/A, which I did but inherited 'false' in the cell. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I eliminate "false" from showing in a cell?
Just check what you have typed in more thoroughly - have you missed out
any brackets (there are 2 at the end of the formula), have you included all of the commas, have you included the colons etc? If you can't spot anything wrong, then copy/paste your formula directly to your reply (put an apostrophe in front of the = sign in Excel. Pete |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I eliminate "false" from showing in a cell?
My bad
=IF(ISNA(VLOOKUP(B9,Q12:R41,2,FALSE)),"",VLOOKUP(B 9,Q12:R41,2,FALSE)) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "thistooshallpass" wrote in message ... Thanks. The first way worked, however, I still am not able to get pass the error message which highlights the "" portion of the formula. "Bob Phillips" wrote: Two ways, =IF(B9="","",vlookup(B9,Q12:R41,2,False)) or more stable as it will also handle any invalid value =IF(ISNA(vlookup(B9,Q12:R41,2,False) ,"",vlookup(B9,Q12:R41,2,False) ) -- HTH Bob Phillips (replace somewhere in email address with googlemail if mailing direct) "thistooshallpass" wrote in message ... Thanks for the response, however, this is the case. I am using the vlookup formula, =vlookup(B9,Q12:R41,2,"") which produces "#N/A" when the lookup value cell is blank. So I decided to use an if formula, =if(B9<0,"0,if(B90,vlookup(B9,Q12:r41,2,0))) thinking that I could get rid of the #N/A, which I did but inherited 'false' in the cell. "Bob Phillips" wrote: Show us your formula, and tell us what you want in its stead. -- HTH Bob Phillips (replace somewhere in email address with googlemail if mailing direct) "thistooshallpass" wrote in message ... |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I eliminate "false" from showing in a cell?
Sorry, there is a missing bracket from my formula - it should be:
=IF(ISNA(VLOOKUP(B9,Q12:R41,2,0)),"",VLOOKUP(B9,Q1 2:R41,2,0)) This should correct it now (thanks to Bob's earlier response!) Pete |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I eliminate "false" from showing in a cell?
Thanks.
"JE McGimpsey" wrote: One way: =IF(ISNA(MATCH(B9,Q12:Q41,FALSE)),"",VLOOKUP(B9,Q1 2:R41,2,FALSE)) In article , thistooshallpass wrote: Thanks for the response, however, this is the case. I am using the vlookup formula, =vlookup(B9,Q12:R41,2,"") which produces "#N/A" when the lookup value cell is blank. So I decided to use an if formula, =if(B9<0,"0,if(B90,vlookup(B9,Q12:r41,2,0))) thinking that I could get rid of the #N/A, which I did but inherited 'false' in the cell. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with this conditional IF statement | Excel Discussion (Misc queries) | |||
Formula Showing In A Cell Instead of Proper Result | New Users to Excel | |||
Adding numbers in one cell and showing total in seperate cell | Excel Discussion (Misc queries) | |||
Showing the contents of a cell based on another cell | Excel Worksheet Functions | |||
Possible Lookup Table | Excel Worksheet Functions |