| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
need formula that recognize
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need formula that recognize
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. RYNNER wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need formula that recognize
On Fri, 20 Feb 2009 18:54:01 -0800, RYNNER
wrote: 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(FI ND("PLB",A1)),C1+60,"")) --ron |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need formula that recognize
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 -- Biff Microsoft Excel MVP "RYNNER" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need formula that recognize
Hi,
I think he wants it blank if not PMC or PLB. Cheers, Shane Devenshire "T. Valko" wrote: 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 -- Biff Microsoft Excel MVP "RYNNER" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need formula that recognize
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. -- If this helps, please click the Yes button Cheers, Shane Devenshire "RYNNER" wrote: 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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need formula that recognize
ron, praise you,,,,,
its workssssss.. wow thanks a lot... "Ron Rosenfeld" wrote: On Fri, 20 Feb 2009 18:54:01 -0800, RYNNER wrote: 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(FI ND("PLB",A1)),C1+60,"")) --ron |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need formula that recognize
On Sat, 21 Feb 2009 01:32:00 -0800, RYNNER
wrote: 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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need formula that recognize
Yeah, I guess I wasn't paying attention. Oh well!
-- Biff Microsoft Excel MVP "Shane Devenshire" wrote in message ... Hi, I think he wants it blank if not PMC or PLB. Cheers, Shane Devenshire "T. Valko" wrote: 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 -- Biff Microsoft Excel MVP "RYNNER" wrote in message ... 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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need formula that recognize
sir dave,
what is the meaning of that "left" what is the function of it? and what about "3", what is it for? "Dave Peterson" wrote: 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. RYNNER wrote: 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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need formula that recognize
hello,
thanks for the help.. it works.. mwah mwah mwah "Shane Devenshire" wrote: 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. -- If this helps, please click the Yes button Cheers, Shane Devenshire "RYNNER" wrote: 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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need formula that recognize
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. -- David Biddulph RYNNER wrote: sir dave, what is the meaning of that "left" what is the function of it? and what about "3", what is it for? "Dave Peterson" wrote: 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. RYNNER wrote: 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 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
need formula that recognize
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. RYNNER wrote: sir dave, what is the meaning of that "left" what is the function of it? and what about "3", what is it for? "Dave Peterson" wrote: 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. RYNNER wrote: 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 -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumif Formula - Won't Recognize Dates | Excel Discussion (Misc queries) | |||
| cells don't recognize formula | Excel Worksheet Functions | |||
| recognize formula | Excel Worksheet Functions | |||
| Formula to recognize text only in a cell | Excel Worksheet Functions | |||
| Formula to recognize Duplicates | Excel Worksheet Functions | |||
Linear Mode
