If formula with mark "-" combination

[Jhon Mustofa]

hi all,

i am looking for some formula to figure out this problem;

--data1-------expected result---------data2-----
john---------------mark "-" ----------- mark "-"
mark "-" -----------ok------------------mark "-"
mark "-"------------ok-----------------sisca43

with using if formula, how to make it in column expected result (col. B)

assuming mark"-" as -

thanks...

[Bernie Deitrick]

It depends. Is the - a custom format for zero values, or is it a string value? Assuming Data1 is in A and Data2 is in C, try

Zeroes version:
=IF(A2=C2,"ok",IF(A2=0,"ok",0))

String version:
=IF(A2=C2,"ok",IF(A2="-","ok","-"))

[Jhon Mustofa]

Bernie, thanks but with new criteria, how about this ;

data1-----------expected result------data2
john-------------ok----------------- mark "-" (new criteria)
john---------------mark "-" ----------- mark "-"
mark "-" -----------ok------------------mark "-"
mark "-"------------ok-----------------sisca43

[Jhon Mustofa]

here. workbook attached

please, see it..

[Jhon Mustofa]

sorry have a correction, here new file update

[Jhon Mustofa]

bump-bump!!!!

[AlKey]

Hi John,

Could you please explain the logic (if any) behind the desired results?
Why Mike is "OK" and David "-"?

[Jonmo1]

Agree with AlKey, there doesn't appear to be any sensible logic to why mike and john are ok, but david is -
Also there are 2 other cells in column A that are already -, and they are ok.

The only pattern I see that is common with the ok's that doesn't exist with the -
Is they are less than 5 characters in length.

So this will produce your desired results in B2 and filled down to B6
=IF(LEN(A2)<5,"ok","-")

[AlKey]

@Jonmo1

I like it! I guess, when there is an absence of logic, one should invent one

[Jhon Mustofa]

ok guys,

here 3 criteria not logic characters in length ;
1) if col. a contains value/text and col. c mark "-" so result (in col. b) returning "-"
2) if col. a contains "-" and col. c contains "-", returning "ok" in col.b
3) if col. a contains "-" and col. c contains text/value, returning "ok" in col.b

[Jonmo1]

But Your example book doesn't follow that pattern.

You have ok in B2, but A2 has text and C2 has -

[Jhon Mustofa]

hi, Jonmo, i am sorry..

but here in file attached..

[Jonmo1]

That new book 'still' doesn't follow the pattern of rules you posted.

[Jhon Mustofa]

maybe...
1) if col. a contains value/text and col. c mark "-" so result (in col. b) returning "-"
2) if col. a contains "-" and col. c contains "-", returning "ok" in col.b
3) if col. a contains "-" and col. c contains "text, returning "ok" in col.b

[Jonmo1]

Maybe...

=IF(A2="-","ok","-")

Note, this produces the results you describe in your posts, not the results shown in the books you've posted.

Also, there's a 4th possiblity,
If BOTH A and C contain a text value..

[Jhon Mustofa]

hi Jonmo, for 3 rd criteria your formula not work, the result should be "-" not "ok"

[AlKey]

For your last example try this one

=IF(OR(LEN(A3)>1,LEN(C3)>1),"-","Ok")

[Jonmo1]

As I said, the formula I suggested follows the rules you posted in the thread, not what you posted in the books.

maybe...
1) if col. a contains value/text and col. c mark "-" so result (in col. b) returning "-"
2) if col. a contains "-" and col. c contains "-", returning "ok" in col.b
3) if col. a contains "-" and col. c contains "text, returning "ok" in col.b

Let's do this.

Please replace the ??'s below with the results you want for the described situation.

ColA = - and ColC = - : I want ??
ColA = - and ColC = text : I want ??
ColA = text and Col C = - : I want ??
ColA = text and Col C = text : I want ??

[Jhon Mustofa]

ColA = - and Col.c = - : col. b -
ColA = - and Col.c = text : col.b -
ColA = text and Col c = - : col. b -
ColA = text and Col c = text : col.b -

[Jonmo1]

Sorry, I used wrong column refs in my previous post.
Please refresh browser and look at it again.

[Jhon Mustofa]

hi Alkey, for 2 rd (OP #19) : for Col a = - and Col.c = text : col.b should be "-" not "ok"

[Jonmo1]

OK, given your latest description of your desired results in post #19

It looks like the formula in column B should be
="-"

[Jhon Mustofa]

hi jonmo1, if Col a2 = text and Col c2 = - : in col. b2, return "-"

[Jhon Mustofa]

hi alkey, i missing it..

your code :

Please Login or Register  to view this content.

that worked!!! many thanks...