Help with #VALUE! error in simple formula using Excel (2010)

[BunnyGal]

Hello everyone. I have little experience using formulas in Excel and have an error in one I just wrote and can't figure out how to correct it. The formula works if the finding is true, but returns an error if the finding is false. I'm Hoping this forum can help me.

The workbook is my checkbook register.

The problematic formula is : =IF(FIND("Digital",C9),(D9),(0))

The cell the formula is in is I9 which is formatted as Currency.

I want the formula in cell I9 to look at cell C9 (which is formatted as text) and find the word "Digital" (without quotes of course) and if it finds the word Digital then I want it to copy the contents of cell D9 (which is formatted as currency and contains only numbers) into cell I9 (where the formula is). If the word Digital is not in C9 the formula should enter $0.00 into cell I9.

The true part of the formula works great. If C9 contains the word Digital anywhere within the text (can be several sentences long), then the results of D9 are properly copied to I9 and the format is correct (currency). However, if cell C9 does not contain the word Digital, I get #Value! in I9 instead of $0.00 or 0. I tried replacing the false value of (0) with ("") a/k/a nothing, but the same error occurs.

When I hover over the error I get the message "A value used in this formula is of the wrong data type".

To summarize:

The formula is in cell I9 and I9 is formatted as currency.
The formula should look at C9 which is formatted as text for the word "Digital" and if it finds the word Digital, It should copy the contents of cell D9 which is formatted as currency into cell I9 also formatted as currency.

The true portion works correctly. If "digital" is in C9 then the contents of D9 are copies to I9.
The false portion does not work. If "digital" is not in C9 I get #VALUE! in I9 instead of the zero I want it to return.

I tried changing the format of column C from text to general and it made no difference. Column C contains words only so it needs to be either text or general. Column C contains no formulas of any kind and is not linked to or from any other cell. The contents of cell C9 are words; a description of the purchase and D9 is the cost of the item described in C9.

What do I need to change to get $0.00 in cell I9 if the findings are false?

Thank you SO much.

Lisa L.

 

[Jonmo1]

Welcome to the board.

#Value! is what FIND returns if it fails to find what it's looking for.
And Most functions will pass along that error once it's encountered.
So the IF see's the #Value! Error, therefor IF passes that along and returns #Value!.

You have to test the FIND for error, and reverse the true/false logic results.

=IF(ISERROR(FIND("Digital",C9)),0,D9)

 

[Aladin Akyurek]

FIND, SEARCH, or MATCH all return a number when successful. Testing for a number therefore would look more 'natural'...

=IF(ISNUMBER(FIND("Digital",C9)),D9,0)

 

[BunnyGal]

Welcome to the board.

#Value! is what FIND returns if it fails to find what it's looking for.
And Most functions will pass along that error once it's encountered.
So the IF see's the #Value! Error, therefor IF passes that along and returns #Value!.

You have to test the FIND for error, and reverse the true/false logic results.

=IF(ISERROR(FIND("Digital",C9)),0,D9)

Thank you Jonmo1, that worked perfectly. I hope to someday know enough about Excel to understand what you wrote and to be able to write my own formulas, LOL, but until then, at least I have your formula which solved the problem.

Question, what is the difference (if any) between your suggestion (which worked) and the suggestion of Aladin Akyurek (which I didn't try because yours worked).
Aladin's suggestion was:

FIND, SEARCH, or MATCH all return a number when successful. Testing for a number therefore would look more 'natural'...

=IF(ISNUMBER(FIND("Digital",C9)),D9,0)

Would that have given the same results as your formula? If not, why? How are they different? If it's too long/complicated to explain, I'm happy that I have a solution, but learning how/why one function works better or differently than another (or not at all) gives me one more piece of knowledge to build on.

Thanks again. Lisa L.

 

[BunnyGal]

FIND, SEARCH, or MATCH all return a number when successful. Testing for a number therefore would look more 'natural'...

=IF(ISNUMBER(FIND("Digital",C9)),D9,0)

Thank you Aladin. I have not tried your suggestion simply because the reply from Jonmo1 came in first and it solved my problem.
I'll ask you the same question as my follow-up to Jonmo1 which is, what is the difference between the formula you gave me, and his?

They are different, but would they yield the same result? Is one better than the other? if so, why? Trying to learn so that someday I know enough to write these formulas on my own. Until then, thank goodness for this forum - it's awesome.

Thanks again to you and Jonmo1 for your quick reply.

 

[BunnyGal]

SOLVED: Help with #VALUE! error in simple formula using Excel (2010)

My question has been answered and my problem is solved.

Thank you to those who took the time to help me. I truly appreciate it.

Lisa L.

 

[Jonmo1]

Re: SOLVED: Help with #VALUE! error in simple formula using Excel (2010)

Both Isnumber and Iserror are being used to 'Trap' errors like #Value!
The basic difference is that the logic is reversed.

IsError asks if the value is an Error (it will be TRUE if it's an error)
IsNumber asks if the value is a Number (errors are NOT numbers, so it will be FALSE if it's an error)

For this particular function you're using it for, I don't see any advantage or disadvantage either way.
Except as Aladin pointed out, it might seem to have a more natural flow using ISNUMBER.
More Natural meaning that we normally want to test for something positive, "If something (the FIND) is successfull Then do this..."
But using ISERROR, it's the reverse, "If something (the FIND) is NOT successfull, then do this..."


For other uses, Isnumber might not be the right choice because by definition it requires a numerical result.
That's fine for the FIND function (and others), that either return a Number OR An error.
But it won't work on other functions that might return TEXT values as well as number or error, like VLOOKUP.


Hope that helps.