nested find in if problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

gs - 22 Oct 2007 19:27 GMT

for excel 2003, the formula
=IF(FIND("s",A2:A14),B2,IF(FIND("s",A2:A14),C2,0)) will fail ( show #VALUE
as result) when s is not present but "r" is.

what can one do about that?

Reply to this Message

Ron Coderre - 22 Oct 2007 19:36 GMT

Try this:
=IF(COUNTIF(A2:A14,"S"),B2,IF(COUNTIF(A2:A14,"R"),C2,0))

Is that something you can work with?
--------------------------

Regards,

Ron (XL2003, Win XP)
Microsoft MVP (Excel)

> for excel 2003, the formula =IF(FIND("s",A2:A14),B2,IF(FIND("s",A2:A14),C2,0)) will fail ( show #VALUE as result) when s is not
> present but "r" is.
>
> what can one do about that?

Reply to this Message

Bernard Liengme - 22 Oct 2007 19:43 GMT

You have to look for the error first
Something like this IF(ISERROR(FIND("s",A2:A14),C2,B2)
but.....find will not work on a range, only on a cell.
If you want:
If there is a cell with a sinlge "s" in A2:A14
then give me B2
esle give me C2
Then =IF(COUNTIF(A1:A14,"s"),B2,C2) will do

If you want to find a "s" within a string in any cell in the range, the
problem is more diffiuclt.
Which do you want?
best wishes
Bernard

> for excel 2003, the formula
> =IF(FIND("s",A2:A14),B2,IF(FIND("s",A2:A14),C2,0)) will fail ( show #VALUE
> as result) when s is not present but "r" is.
>
> what can one do about that?

Reply to this Message

gs - 22 Oct 2007 20:40 GMT

pardon me, actually I don't need range, I goofed in the OP

=IF(FIND("s",A1),B1,FIND("r",A1)*C1)

should have been what I tried to determine the result value: if a cell has a
letter "s", I want value in B1, "s" in C1, otherwise D1

The original problem was slightly more complex ( different formula
depending on what is found first)

Countif will not work as I am looking if a cell has certain letter

> You have to look for the error first
> Something like this IF(ISERROR(FIND("s",A2:A14),C2,B2)
[quoted text clipped - 16 lines]
>>
>> what can one do about that?

Reply to this Message

gs - 22 Oct 2007 21:55 GMT

great, thank you, got it working

=IF(ISERROR(FIND("s",A1)),IF(ISERROR(FIND("r",A1)),D1,C1),B1)

a bit twisted, not as straight forward in conventional sense but works

would have been nice if find("whatever", a1)> 0 will either be true or
false instead of error

> You have to look for the error first
> Something like this IF(ISERROR(FIND("s",A2:A14),C2,B2)
[quoted text clipped - 16 lines]
>>
>> what can one do about that?

Reply to this Message

Rate this thread: