Logical Filtering based on Value

I've been using this formula to sort some values and return results

if(
	or(
		and(
			b9<>"",
			isnumber(
				find(
					Left(b9,1
					,"AWFUY")
				)
			),
			isnumber(
				find("SHORT",g9
				)
			),
			sum(
				countif(c9,{"BOG","BLM","CMO"})
				)
			)
		)
	)
,i9-0.01,j9
)

--I used ALT-ENTER between each formula so as to see thing clearly
Column A values can be "B01"
Column B values can be "BRN"
Columns I & J are values, one being lowest the other being highest
allowed.




On another sheet I use
IF(
     OR(
         AND(
                B15<>"",
                ISNUMBER(
                               FIND(
                                      LEFT(
                                            B15,1),"JCR"
                                     )
                                ),
                 ),
                 SUM(
                         COUNTIF(
                                    C15,{"DST","LVN","DNM"}
                                       )
                         ),
             ),
       I15-0.01,J15
    )

1st:The "JCR" formula is not quite working. With "R08" in column B and
"DNM" in column C, for example things work great. But "B73" in A and
"DST" in C I get the wrong result. (values starting with "B" should be
filtered)

2nd: I would like to combine this into one formula
0
2/13/2009 2:27:22 PM
excel 39879 articles. 2 followers. Follow

3 Replies
547 Views

Similar Articles

[PageSpeed] 30

Seeking clarification. So far I have
=IF(OR(AND( B9<>"", ISNUMBER( FIND(LEFT(B9,1),"AWFUL") )),ISNUMBER( 
FIND("SHORT",G9 ))),"A","B")

Please explain what is to happen if the condition is TRUE (A) and what is to 
happen if FALSE (B)

Also unclear why we are told: Column A values can be "B01" ; Column B values 
can be "BRN

best wishes
-- 
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"caveman.savant" <caveman.savant@gmail.com> wrote in message 
news:177d04d3-e31b-419f-9763-d1dbf89ca868@z6g2000pre.googlegroups.com...
> I've been using this formula to sort some values and return results
>
> if(
> or(
> and(
> b9<>"",
> isnumber(
> find(
> Left(b9,1
> ,"AWFUY")
> )
> ),
> isnumber(
> find("SHORT",g9
> )
> ),
> sum(
> countif(c9,{"BOG","BLM","CMO"})
> )
> )
> )
> )
> ,i9-0.01,j9
> )
>
> --I used ALT-ENTER between each formula so as to see thing clearly
> Column A values can be "B01"
> Column B values can be "BRN"
> Columns I & J are values, one being lowest the other being highest
> allowed.
>
>
>
>
> On another sheet I use
> IF(
>     OR(
>         AND(
>                B15<>"",
>                ISNUMBER(
>                               FIND(
>                                      LEFT(
>                                            B15,1),"JCR"
>                                     )
>                                ),
>                 ),
>                 SUM(
>                         COUNTIF(
>                                    C15,{"DST","LVN","DNM"}
>                                       )
>                         ),
>             ),
>       I15-0.01,J15
>    )
>
> 1st:The "JCR" formula is not quite working. With "R08" in column B and
> "DNM" in column C, for example things work great. But "B73" in A and
> "DST" in C I get the wrong result. (values starting with "B" should be
> filtered)
>
> 2nd: I would like to combine this into one formula 


0
bliengme5824 (3040)
2/13/2009 3:34:37 PM
If the condition is true  value of cell in column "I"-0.01, if false
value of cell in column "J"

On Feb 13, 7:34=A0am, "Bernard Liengme" <blien...@stfx.TRUENORTH.ca>
wrote:
> Seeking clarification. So far I have
> =3DIF(OR(AND( B9<>"", ISNUMBER( FIND(LEFT(B9,1),"AWFUL") )),ISNUMBER(
> FIND("SHORT",G9 ))),"A","B")
>
> Please explain what is to happen if the condition is TRUE (A) and what is=
 to
> happen if FALSE (B)
>
> Also unclear why we are told: Column A values can be "B01" ; Column B val=
ues
> can be "BRN
>
> best wishes
> --
> Bernard V Liengme
> Microsoft Excel MVPhttp://people.stfx.ca/bliengme
> remove caps from email
>
> "caveman.savant" <caveman.sav...@gmail.com> wrote in message
>
> news:177d04d3-e31b-419f-9763-d1dbf89ca868@z6g2000pre.googlegroups.com...
>
>
>
> > I've been using this formula to sort some values and return results
>
> > if(
> > or(
> > and(
> > b9<>"",
> > isnumber(
> > find(
> > Left(b9,1
> > ,"AWFUY")
> > )
> > ),
> > isnumber(
> > find("SHORT",g9
> > )
> > ),
> > sum(
> > countif(c9,{"BOG","BLM","CMO"})
> > )
> > )
> > )
> > )
> > ,i9-0.01,j9
> > )
>
> > --I used ALT-ENTER between each formula so as to see thing clearly
> > Column A values can be "B01"
> > Column B values can be "BRN"
> > Columns I & J are values, one being lowest the other being highest
> > allowed.
>
> > On another sheet I use
> > IF(
> > =A0 =A0 OR(
> > =A0 =A0 =A0 =A0 AND(
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0B15<>"",
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0ISNUMBER(
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 FIND(
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=
 =A0LEFT(
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=
 =A0 =A0 =A0 =A0B15,1),"JCR"
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=
 )
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0),
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ),
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 SUM(
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 COUNTIF(
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=
C15,{"DST","LVN","DNM"}
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0=
 =A0 )
> > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ),
> > =A0 =A0 =A0 =A0 =A0 =A0 ),
> > =A0 =A0 =A0 I15-0.01,J15
> > =A0 =A0)
>
> > 1st:The "JCR" formula is not quite working. With "R08" in column B and
> > "DNM" in column C, for example things work great. But "B73" in A and
> > "DST" in C I get the wrong result. (values starting with "B" should be
> > filtered)
>
> > 2nd: I would like to combine this into one formula

0
2/14/2009 2:30:24 PM
IF(OR(AND( B9<>"", ISNUMBER( FIND(LEFT(B9,1),"AWFUL") )),ISNUMBER(
 FIND("SHORT",G9 ))),I9-0,01,J9)


-- 
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"caveman.savant" <caveman.savant@gmail.com> wrote in message 
news:f972f9b0-a2cf-4d19-ad61-df45755557de@i18g2000prf.googlegroups.com...
If the condition is true  value of cell in column "I"-0.01, if false
value of cell in column "J"

On Feb 13, 7:34 am, "Bernard Liengme" <blien...@stfx.TRUENORTH.ca>
wrote:
> Seeking clarification. So far I have
> =IF(OR(AND( B9<>"", ISNUMBER( FIND(LEFT(B9,1),"AWFUL") )),ISNUMBER(
> FIND("SHORT",G9 ))),"A","B")
>
> Please explain what is to happen if the condition is TRUE (A) and what is 
> to
> happen if FALSE (B)
>
> Also unclear why we are told: Column A values can be "B01" ; Column B 
> values
> can be "BRN
>
> best wishes
> --
> Bernard V Liengme
> Microsoft Excel MVPhttp://people.stfx.ca/bliengme
> remove caps from email
>
> "caveman.savant" <caveman.sav...@gmail.com> wrote in message
>
> news:177d04d3-e31b-419f-9763-d1dbf89ca868@z6g2000pre.googlegroups.com...
>
>
>
> > I've been using this formula to sort some values and return results
>
> > if(
> > or(
> > and(
> > b9<>"",
> > isnumber(
> > find(
> > Left(b9,1
> > ,"AWFUY")
> > )
> > ),
> > isnumber(
> > find("SHORT",g9
> > )
> > ),
> > sum(
> > countif(c9,{"BOG","BLM","CMO"})
> > )
> > )
> > )
> > )
> > ,i9-0.01,j9
> > )
>
> > --I used ALT-ENTER between each formula so as to see thing clearly
> > Column A values can be "B01"
> > Column B values can be "BRN"
> > Columns I & J are values, one being lowest the other being highest
> > allowed.
>
> > On another sheet I use
> > IF(
> > OR(
> > AND(
> > B15<>"",
> > ISNUMBER(
> > FIND(
> > LEFT(
> > B15,1),"JCR"
> > )
> > ),
> > ),
> > SUM(
> > COUNTIF(
> > C15,{"DST","LVN","DNM"}
> > )
> > ),
> > ),
> > I15-0.01,J15
> > )
>
> > 1st:The "JCR" formula is not quite working. With "R08" in column B and
> > "DNM" in column C, for example things work great. But "B73" in A and
> > "DST" in C I get the wrong result. (values starting with "B" should be
> > filtered)
>
> > 2nd: I would like to combine this into one formula


0
bliengme5824 (3040)
2/15/2009 1:41:45 PM
Reply:

Similar Artilces:

SQL report to filter for Current user
I am working on a SQL report, and i need to filter the data for the current user logged in to CRM. Something like where current user id = user id of activity. What I am looking for is how to find the current user id. Much thanks in advance! An easier solution is not to build that into the report; simply use the default filter to do that. No coding needed "ilibhart" <ilibhart@discussions.microsoft.com> wrote in message news:92DE3638-A92A-42C8-BDEA-A8686829D249@microsoft.com... >I am working on a SQL report, and i need to filter the data for the current > user l...

Problem with Series Values
HI - I hope this question isnt too stupid but it ha been a show stopper for me. I am trying to enter a series of cell references into the Series Values field in the Edit Series window but when I click OK the window just ignores the keyclick. This must mean that it is unhappy with something but I cant figure out what that is for the life of me. Any suggestions would be most appreciated. Hi, There is a limit to the length of the string created when selecting cells. If the cells are non-contiguous this limit can be reach with only a few cells being selected. Cheers Andy -- Andy Pope...

Mail getting filtered into Junk Email with no IMF installed
I am running a Exchange 2003 server with mostly Outlook 2003 clients. I have installed Symantec Mail Security for Exchange 4.5, with Heuristic Spam scanning turned on, and any emails with a SCL >1 being tagged with a custom X header. What I am finding is that somewhere on the server or client, anything coming in with an SCL score in the headers is being filtered into the "Junk Email" folder, rather than following any rules I set up on the rules wizard. This is not the functionality I want, as I want the users to be able to chose whether to filter for spam or not. I can only as...

Custom filters
I created a custom filter on a city field, looking for all cities that start with So. The criteria used was begins with SO. THis works fine. I then tried to filter for zipcodes begins with 46. THis filter returns no records, and there at least 50 in the list. I also tried to use wildcards * or ??? Still no returned records. Am I using the feature incorrectly. Thanks Dudley Try "Greater than 45000" AND "Less than 47000" Vaya con Dios, Chuck, CABGx3 "Dudley" <Dudley@discussions.microsoft.com> wrote in message news:F303E...

Spam filtering not applying SCL
Exchange 2000 org with single server migrating to Exchange 2007 single server, One exchange server with CAS,HUB and mailbox roles. Install the Antispam agents and everything appears to be fine, but Nothing gets filtered - If content filtering is configured with specific words, it doesn't catch it. When going through the agent logs from C:\Program Files\Microsoft\Exchange Server\TransportRoles\Logs\AgentLog it shows: ,1,Content Filter Agent,OnEndOfData,AcceptMessage,,SCL,not available: policy is disabled. I am not sure of what could be blocking this or how to enable this policy. Has...

Knowledge Base Articles 09-20-06
Can you add attachments to Knowledge Base Articles? Thanks, Boolean1 KB Articles are system entities that cannot be customized. Since they dont have a notes tab, I dont believe that you can attach a note. You might try exporting the sitemap.xml file and manually editing it to include a notes tab. I doubt that it would work but it may be worth a try. Editing the sitemap.xml is documented in the SDK. Good luck. -- Bullitt Fitzhugh MBS Certified Master- CRM 3.0 "Boolean1" wrote: > Can you add attachments to Knowledge Base Articles? > > Thanks, > > Boo...

FRx Base Year Enhacement
The FRx Report Date field for the base year should be enhanced to allow the flexibility of hard coding the year in the same manner as the default base period can be hard coded and saved. Right now, in order for the base year in FRx to remain as 2005, we need to keep one of the 2005 fiscal periods opened in Great Plains. Our financial folks would like to keep all the 2005 fiscal periods 'closed' until they complete all the financial statements. We cannot do a year end close since some last minute journal entries are required to be entered in Great Plains for 2005. Thanks. Frank ...

"Filter for" context menu item for Access 2007
One of my most commonly used commands in previous versions of Access was the "Filter for" command available in the context menu when right clicking a field in a table or query. Is this feature available in Access 2007? The filter by form is not what I want because it requires many more operations to set up for a quick temporary filter than the "Filter for" menu command. I would frequently type SQL statements such as "A or B or C or D" in the "Filter For" filter command in Access 2003 and earlier. ...

If-Then logics
Hi, I have a work sheet with numbers in 'Column A'. In 'Column B' I wish to get the following output: If column A is < 3000 then give me '1'; If column B is >5000 then give me '3'; if column A is >3000 but <5000 then give me '2'. Kindly help. Thanks Rumneet "Rum" wrote: > I have a work sheet with numbers in 'Column A'. > In 'Column B' I wish to get the following output: > If column A is < 3000 then give me '1'; > If column B is >5000 then give me '3'; > if...

IF THEN LOGIC
Can you have a cell return a value based on another cell's input. Ex: If I enter the year 2004 in a specific cell then I want another cell to display the date 12/01/03 or some other date or value that I would pre-determine. Hi =IF(A1=2004,DATE(2003,12,1),"") -- Regards Frank Kabel Frankfurt, Germany "flotowntiger" <flotowntiger@discussions.microsoft.com> schrieb im Newsbeitrag news:B0DE4A33-E000-4468-AD96-309CE5D16E7D@microsoft.com... > Can you have a cell return a value based on another cell's input. Ex: If I > enter the year 2004 in a specific cel...

Filtering a Combo Box
I am creating a form to run in front of a table from another MDB program. The problem is with a combo box I use to select a record. The table works like a flat based db. So the combo box shows duplicate entries. I need to be able to filter the combo box to show only one instance of a listing. The combo box may show (B2D0D0D) 30 times I need to see it only once Gary Hull wrote: > I am creating a form to run in front of a table from another > MDB program. > > The problem is with a combo box I use to select a record. The > table works like a flat based db. So the combo bo...

OWA 2003 FE Servers and Forms Based Auth
Does OWA 2003 using forms-based authentication require server affinity? We started using a second Front-End Server and have some users calling they are being prompted for username and password when they didn't expect to be. We are using Round-Robin dns method for load balancing. If you could point me to a resource and tell me if you found this documented. Also if you have any feedback on using WNLB - Windows Network Load Balancing method I would appreciate. Thanks, Mark It should not require affinity because it the session is cookie based and unless you have modified the length ...

IF THEN logic
Sample Data: A B C D E F G CID IID Concerns Interests Contact first timers Identifier TestData 1 1 Building Fellowshi NO U65286929 a 2 4 Tips for Meeting sp b 1 3 Building Opportuniti NO L35069718 a What i want to do is: if column G has an "a" then count column A if it has a 1 in it if column G has an "b" then count column A if it has a 2 in it etc. Seprate the data as first choice and second choice. =SUMPRODUCT(((G2:G200="a&quo...

Re: What is an efficient way to determine if a value is in an array?
Right now I have a list of 300 items, and I want to see if they belon to one of three sets of values. I keep the 300 items in column A o page 1, and on pages 2, 3, and 4 I have the other three lists of value in columns A respectively. Right now I am using a VLOOKUP on each of the three sheets, and if i exists I return the value in the adjacent column, which is always "1". I run this vlookup for each page, and put the return value in th three adjacent columns next to my original value. This works, but the problem is that it returns the dreaded #N/A if i doesnt exist in the page. ...

Investing, column "Value on <date>"
Using money 2007 when clicking on investing, you are presented with a list of all your investing accounts with 4 columns: Investing accounts, Current value, Value on <date>, Gain since opening date. Where is the column "Value on <date>" getting its info from? How do you change the date associated with the column? thanks for your help ...

Auto Filter, Wilcards and Numbers
In a spreadsheet I have a colum full of numerical values like this: 0.016666667 0.033333333 0.05 0.066666667 1.016666667 1.233333333 1.25 44.28333333 44.3 44.01666667 These numbers go down for nearly 3000 rows. I want to be able to run an autofilter on anything that ends in ??.016666667 However...it just wont let me do it. When I try to use wild cards or choose 'ends with', it always returns no results. any ideas? Create a helper column with following formula: =MOD(A1,1)=0.016666667 Then autofilter for TRUE HTH -- AP "Karlos" <Karlos@discussions.microsoft.com...

A question of concept/logic
Hello folks, I would really appreciate your help on the programming logic of the following example: Let's say you have an invitees table with Invitees_ID and Invitees_Names Assuming that you have another table this time with Events_ID and _Name. Now I would like to be able to select invitees, some of them, and invite them to q selected event. Which logic should I use: - Make a third table with gathered Invitees_ID and Events_ID? - Make a table for each events' year so that it can last in the future? Any correction/info will be greatly appreciated :) Have a nice day P.S. sorry a...

combo box selection to change values
Hi, I have a userform with a combo box (cb1), a textbox (tx1), a button (bt1), and a checkbox (ck1). If the value of the combo box corresponds to the value in a range (Col1), I would like the values in three other ranges (Col2, Col3 and Col4) associated respectively with the textbox, the button and the checkbox, to change to their current (i.e. altered) values. I have the following code: Dim i as integer Dim va Col1 as variant, Col2 as variant, Col3 as variant, Col4 as variant vaCol1 = [Col1] vaCol2 = [Col2] vaCol3 = [Col3] vaCol4 = [Col4] Redim vaColumns (1to Ubound(Col1), 1 to 4) F...

Custom Subject based on system time?????
Hi, I have created a shift turnover spreadsheet in Excel and with VBA I am allowing the users to generate an e-mail in Outlook which will send the spreadsheet in the body of the e-mail. What I am looking to do is have the Subject line in Outlook be based on the system time. For example if the time is 0715 the the subject would be "3rd Shift Turnover" if the system time is 1515 then the subject line would be "First Shift Turnover" and so on. Using Office 2007 and Word as my editor for Outlook. -- brianharris16 http://forums.slipstick.com And your qu...

Rules with logical AND
Hi there, I am using Outlook 2003 - without Exchange I am trying to resend incomming messages differenciated by words in the subject line. Example: message1 subject line: test message2 subject line: test WARNING message3 subject line: test ALARM now I would like to resend the messages: Subject "test" stays in inbox, no resend Subject "test" and "WARNING" resend to xxx@yyy.com Subject "test" and "ALARM" resend also to xxx@yyy.com I can't figure out how to do that with the rule wizard. Any hints from the experts? regards from sunny Vi...

Weird values on resource useage
I have a proejct that starts on 22nd March and have set Friday as short day throughout the project starting from April only (friday is a 5 hour day instead of 7). I would expect project to schedule an 8 hour day for the first Friday in the project for a task scheduled on that day with one resource assigned but instead it is putting in 5 hours and then the remaining 3 on the Monday even thought that Friday isn't a short day - help! Also later on it is then scheduling weird stuff like 7 hours one day and a remaining 1 hour the next instead of 8 hours on one day - I've look...

Date field to filter combo list box
I have a date field named [JobRequestDate] following that I have a combolist box [SchedDateTime]. The scheddatetime lists times from Mon to Sat. At the moment, when you pick a jobrequestdate (say a Thursday, date shown in short form) you have to scroll down the combo box to the 'Thur' times. (However, the problem is that sometimes a wrong day is being picked and the Job goes to the wrong place (it's doesn't match the jobrequestdate field). I would like to be able to filter the [SchedDateTime] field to show ONLY the times for the day chosen in the [JobRequestDate] field. The J...

logical function
please what is the use of "" in a formula? can you help breathis down for an hypothetical sheet =IF(AND(I25<>"",I25<=$E$1),$L25*$H25/100,0)k what i dont understand is the "" --- Message posted from http://www.ExcelForum.com/ Debo, It is a way of testing for nothing in the cell. So AND(I25<>"",I25<=$E$1) is testing for the I25 to be not empty and less than or equal to the value in E1, If true, it returns the value of L25 multiplied by H25 divided by 100, else it returns 0 The k at the end looks like a mistake -- HTH Bob Ph...

Text to values
I need to convert cells of data to values. The data is the result of the following formula: =LEFT(D2,SEARCH(" ",D2)-1). Example of data results: 1099r, 1073, 10866c, 09R4, etc. I need Excel to see them as values in order for a formula to recognize them. It doesn't matter if the cell is formatted as general, text, or number. This is in Excel 2003. Thanks so much. Some thoughts. You can actually slant everything to be text comparisons instead of numeric comparisons. From the sample data that you posted, that seems to me the better route. LEFT per se will retur...

Logical formulas
I think I got this in the wrong Discussion group. I put it General Discussion before but I think it belongs here. Original Message below: I must admit. I am horrible at logical functions. I am trying to compose a formula that will return a value of "1" if number is greater than 1, less than 4, greater than 949, and less than 991. Here is what I have now but it is not working: =IF(AND(G7>0,G7<4,G7>949,G7<991),1,0) Any help would be awesome! -- -CRM Try this: =IF(OR(AND(G7>0,G7<4),AND(G7>949,G7<991)),1,0) Does that help? ------------------------...