need formula that recognize

[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

 

[Dave Peterson]

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.

 

[Ron Rosenfeld]

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

Perhaps something like:

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

--ron

 

[T. Valko]

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

 

[Shane Devenshire]

Hi,

I think he wants it blank if not PMC or PLB.


Cheers,
Shane Devenshire

 

[Shane Devenshire]

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.

 

[RYNNER]

ron, praise you,,,,,
its workssssss.. wow thanks a lot...

 

[Ron Rosenfeld]

ron, praise you,,,,,
its workssssss.. wow thanks a lot...

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

 

[T. Valko]

Yeah, I guess I wasn't paying attention. Oh well!

 

[RYNNER]

sir dave,
what is the meaning of that "left" what is the function of it? and what
about "3", what is it for?

 

[RYNNER]

hello,

thanks for the help.. it works.. mwah mwah mwah

 

[David Biddulph]

LEFT is a standard Excel function. For any Excel function (except one), if
you want to find out its syntax, examples, & usually a "See also" link to
related functions, look it up in Excel help.

 

[Dave Peterson]

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.