need formula that recognize

  • Thread starter Thread starter RYNNER
  • Start date Start date
R

RYNNER

here is the sample of my formula, this is what im tryin to say =IF(A1=PMC
1234,C1+120,"",IF(A1=PLB 1234,C1+60,""). this is the prob, PMC or PLB is not
constant, it is always followed by different numbers, if i use PMC, it is
plus 120, if PLB, it is plus 60.. im tryin to replace it with asterisk,
therefore =IF(A1=PMC*,C1+120,"",IF(A1=PLB*,C1+60,""). but it doesnt work... i
hope someone understand my problem...
thanks

rynner
 
Maybe you can look at the first 3 characters:

=if(left(a1,3)="pmc",c1+120,if(left(a1,3)="plb",c1+60,""))

I wasn't sure what the rest of the formula really did.
 
here is the sample of my formula, this is what im tryin to say =IF(A1=PMC
1234,C1+120,"",IF(A1=PLB 1234,C1+60,""). this is the prob, PMC or PLB is not
constant, it is always followed by different numbers, if i use PMC, it is
plus 120, if PLB, it is plus 60.. im tryin to replace it with asterisk,
therefore =IF(A1=PMC*,C1+120,"",IF(A1=PLB*,C1+60,""). but it doesnt work... i
hope someone understand my problem...
thanks

rynner
Click to expand...

Perhaps something like:

=IF(ISNUMBER(FIND("PMC",A1)),C1+120,IF(ISNUMBER(FIND("PLB",A1)),C1+60,""))

--ron
 
Try one of these:

=C1+IF(LEFT(A1,3)="PMC",120,IF(LEFT(A1,3)="PLB",60,0))

=C1+(LEFT(A1,3)="PMC")*120+(LEFT(A1,3)="PLB")*60
 
Hi,

If you are willing to show 0 rather than nothing, as in Valko suggestion,
you can use the:

=MAX(IF(LEFT(A1,3)={"PMC","PLB"},C1+60*{2,1}))

You can also use this formula if your turn off the display of zeros under
Tools, Options, View, Zero Values. Or if you are willing to conditionally
format the cells to hide the 0's.
 
ron, praise you,,,,,
its workssssss.. wow thanks a lot...
Click to expand...

You're welcome. Glad to help.

Mine will find the substring anywhere in your entry. Some of the other
approaches find it only if it is at the beginning of the entry -- so look at
those, too.
--ron
 
sir dave,
what is the meaning of that "left" what is the function of it? and what
about "3", what is it for?
 
Just to add to David's response.

=left(a1,###)
will give the first (leftmost) ### characters in A1.

There are other string functions like this. You may want to learn about
=right(), =mid(), too.
 
Back
Top