Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting rid of a VALUE error
I have a formula in a spreadsheet with several nested if statements.
Even when some of the statements should be false, a #VALUE! error earlier on is mucking up the logical outputs. Is there a simple way to 'isolate' such an error code so that it does not affect subsequent logical outputs please? Thanks, V |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting rid of a VALUE error
ERROR.TYPE
http://office.microsoft.com/en-us/ex...090791033.aspx -- David Biddulph "Victor Delta" wrote in message ... I have a formula in a spreadsheet with several nested if statements. Even when some of the statements should be false, a #VALUE! error earlier on is mucking up the logical outputs. Is there a simple way to 'isolate' such an error code so that it does not affect subsequent logical outputs please? Thanks, V |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting rid of a VALUE error
Post the formula with an explanation of what you're trying to do.
-- Biff Microsoft Excel MVP "Victor Delta" wrote in message ... I have a formula in a spreadsheet with several nested if statements. Even when some of the statements should be false, a #VALUE! error earlier on is mucking up the logical outputs. Is there a simple way to 'isolate' such an error code so that it does not affect subsequent logical outputs please? Thanks, V |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting rid of a VALUE error
"T. Valko" wrote in message
... Post the formula with an explanation of what you're trying to do. Biff Thanks. This illustrates the problem. One of the nested if statements is the following =IF(FIND("GRE",A22)=1,1,0) - testing, of course, whether the first three letters of A22 are GRE However, for many values of A22 (including blank), the output of the formula is #VALUE! rather than 0. And this 'upsets' the rest of the logic whose output also then comes to #VALUE! Regards, V |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting rid of a VALUE error
=IF(FIND("GRE",A22)=1,1,0) - testing, of course, whether the first three
letters of A22 are GRE Several ways to do this... FIND is case sensitive so gre and GRE will not match. If no match is found then you get the error. You can trap the error like this: =IF(COUNT(FIND("GRE",A22)),1,0) However, this seaches the entire string, not just the 1st 3 characters. If you want to limit the search to just the 1st 3 characters: =IF(LEFT(A22,3)="GRE",1,0) However, this is not case sensitive. GRE will match gre. If you want to test for the exact match of GRE then: =IF(EXACT(LEFT(A22,3),"GRE"),1,0) -- Biff Microsoft Excel MVP "Victor Delta" wrote in message ... "T. Valko" wrote in message ... Post the formula with an explanation of what you're trying to do. Biff Thanks. This illustrates the problem. One of the nested if statements is the following =IF(FIND("GRE",A22)=1,1,0) - testing, of course, whether the first three letters of A22 are GRE However, for many values of A22 (including blank), the output of the formula is #VALUE! rather than 0. And this 'upsets' the rest of the logic whose output also then comes to #VALUE! Regards, V |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting rid of a VALUE error
"T. Valko" wrote in message
... =IF(FIND("GRE",A22)=1,1,0) - testing, of course, whether the first three letters of A22 are GRE Several ways to do this... FIND is case sensitive so gre and GRE will not match. If no match is found then you get the error. You can trap the error like this: =IF(COUNT(FIND("GRE",A22)),1,0) However, this seaches the entire string, not just the 1st 3 characters. If you want to limit the search to just the 1st 3 characters: =IF(LEFT(A22,3)="GRE",1,0) However, this is not case sensitive. GRE will match gre. If you want to test for the exact match of GRE then: =IF(EXACT(LEFT(A22,3),"GRE"),1,0) Biff Many thanks - that's brilliant! V |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Getting rid of a VALUE error
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Victor Delta" wrote in message ... "T. Valko" wrote in message ... =IF(FIND("GRE",A22)=1,1,0) - testing, of course, whether the first three letters of A22 are GRE Several ways to do this... FIND is case sensitive so gre and GRE will not match. If no match is found then you get the error. You can trap the error like this: =IF(COUNT(FIND("GRE",A22)),1,0) However, this seaches the entire string, not just the 1st 3 characters. If you want to limit the search to just the 1st 3 characters: =IF(LEFT(A22,3)="GRE",1,0) However, this is not case sensitive. GRE will match gre. If you want to test for the exact match of GRE then: =IF(EXACT(LEFT(A22,3),"GRE"),1,0) Biff Many thanks - that's brilliant! V |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error of slope taking into account error of the data points | Excel Worksheet Functions | |||
Error: "Excel encountered an error and had to remove some formatti | Excel Discussion (Misc queries) | |||
Counting instances of found text (Excel error? Or user error?) | Excel Worksheet Functions | |||
I have Error 1919 Error Configuring ODBC dataSource Database | Excel Discussion (Misc queries) |