Getting rid of a VALUE error

[Victor Delta]

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

 

[David Biddulph]

ERROR.TYPE
http://office.microsoft.com/en-us/excel/HP052090791033.aspx

 

[T. Valko]

Post the formula with an explanation of what you're trying to do.

 

[Victor Delta]

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

 

[T. Valko]

=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)

 

[Victor Delta]

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

 

[T. Valko]

You're welcome. Thanks for the feedback!