Home > Software engineering >  Why does this report #Value error in Excel
Why does this report #Value error in Excel

Time:03-04

Just trying to report binary test...

=IF(FIND("RHS",B14),"RHS","LHS")

The function works for RHS, but reports #value for LHS. Why?

CodePudding user response:

The FIND function returns the index of a string within another string, however, if the string you are looking for is not there it results in an error and displays "#value".

Likewise the if function is expecting an expression that results in a true or false value like: if ( this expression =true, then do this , otherwise do this)

Since the find function returns an error #value, it causes the if statement to also error out, when trying to calculate what to do.

To mitigate this you can wrap the function with either an ISNUMBER OR ISERROR function to validate if the find function is indeed returning an expected result

=IF(ISNUMBER(FIND("RHS",B14)),"RHS","LHS")

=IF(ISERROR(FIND("RHS",B14)),"LHS","RHS")

CodePudding user response:

If expects a boolean result.

Find returns i9f it finds sowethig a number if Not showws an error that is why RDS works

A ISNUMBER returns suich a boolean result, that is needed

=IF(ISNUMBER(FIND("RHS",B14)),"RHS","LHS")
  • Related