Excel question, I keep getting a #Value!!

June 3, 2009 5:06 PM

Why does the following formula provide a #Value! instead of False and how do I fix it?

The formula below is not working for me...

=IF(ISNUMBER(SEARCH("rejected",A10)),"true",IF(SEARCH("paper",A10),"true","false"))

Also, if anyone has any reason as to why this formula requires ‘ISNUMBER’ to work…that would be great.

posted by penguingrl to Education (10 answers total)

What's the purpose of the formula--to display "true" when a cell says rejected or paper and false if it says neither?
posted by arm426 at 5:10 PM on June 3, 2009

I get "true" not #Value! when I try to formula.

I would look at the formatting of the contents of cell A10. Is the text that you see actually formatted as text?
posted by dfriedman at 5:12 PM on June 3, 2009

I had dfriedman's experience; if the formula is supposed to do what arm426 says, then it works for me.

I don't think you need ISNUMBER (or maybe you need it because you're using SEARCH), but I got the same result by using:

=IF(A10="rejected","true",IF(A10="paper","true","false"))
posted by emumimic at 5:27 PM on June 3, 2009

Response by poster: When it should say False it says #Value! instead. So, when rejected or paper are not present in the cell it says #Value! and not False.
posted by penguingrl at 5:32 PM on June 3, 2009

You need ISNUMBER if there is a text string in A10. If the only text in A10 is either "rejected" OR "paper" with no other accompanying text then you do not need the ISNUMBER(SEARCH(...)) syntax.
posted by dfriedman at 5:32 PM on June 3, 2009

Try:

=IF(OR(ISNUMBER(SEARCH("rejected",A10)),(ISNUMBER(SEARCH("paper",A10)))),"true","false")
posted by dfriedman at 5:35 PM on June 3, 2009

Oh, got it. Try this:
=IF(ISNUMBER(SEARCH("paper",A10)),"true",IF(ISNUMBER(SEARCH("rejected",A10)),"true","false"))
posted by emumimic at 5:39 PM on June 3, 2009

Or dfriedman's more elegant version :)
posted by emumimic at 5:41 PM on June 3, 2009

When I am trying to do these complex logic statements in Excel, I find it helps me to articulate the logic: "IF "rejected" OR "paper" are in cell A10, THEN return "true", ELSE return "false."

This tells me that I need to use the OR(...) syntax with something nested into it.
posted by dfriedman at 5:44 PM on June 3, 2009

Response by poster: Thank you, guys, it worked! I also didn't know Excel is not a fan of copying and pasting equations, but when I wrote it manually, it worked!
posted by penguingrl at 6:21 PM on June 3, 2009