Combining AND and OR functions

Where would the "OR" go in the following function:

Cell A1 can be Yes or No
Cell A2 can be any number
Cell A3 can be any number

If EITHER Cell A1 is "Yes" OR Cell A2 is equal to or above 50 AND Cell A3 is 
equal to or above 100, then return "OK"; 
however, if cell A1 is "No" AND either A2 is below 50 or A3 is below 100, 
then return "No good"

=IF(AND(A2>=50,A3>=100),"OK","No good")

How would I add the OR term to test whether A1 is Yes or No?
-- 
MZ
0
Utf
12/21/2009 1:34:02 PM
excel.newusers 15348 articles. 2 followers. Follow

1 Replies
802 Views

Similar Articles

[PageSpeed] 4

"MZ" wrote:
> If EITHER Cell A1 is "Yes" OR Cell A2 is equal
> to or above 50 AND Cell A3 is equal to or above
> 100, then return "OK"; 
> however, if cell A1 is "No" AND either A2 is
> below 50 or A3 is below 100, then return "No good"

=IF(OR(A1="yes",AND(A2>=50,A3>=100)), "OK", "No good")

Alternatively:

=IF((A1="yes")+(A2>=50)*(A3>=100), "OK", "No good")

I would use the latter (less clear, IMHO) only if the function nesting limit 
(7 before Excel 2007) becomes a problem.


----- original message -----

"MZ" wrote:

> Where would the "OR" go in the following function:
> 
> Cell A1 can be Yes or No
> Cell A2 can be any number
> Cell A3 can be any number
> 
> If EITHER Cell A1 is "Yes" OR Cell A2 is equal to or above 50 AND Cell A3 is 
> equal to or above 100, then return "OK"; 
> however, if cell A1 is "No" AND either A2 is below 50 or A3 is below 100, 
> then return "No good"
> 
> =IF(AND(A2>=50,A3>=100),"OK","No good")
> 
> How would I add the OR term to test whether A1 is Yes or No?
> -- 
> MZ
0
Utf
12/21/2009 2:36:01 PM
Reply:

Similar Artilces:

Merge records function
Is it possible to reuse the record merge function in CRM. Fx to call the function with a valid Account GUID with a URL-call to mergerecords ? I want this to open the merge windows with the GUID as the master record. ...

Excel functions
Hi everyone, Hope somebody could help me with these problems. Problem #1. row 1 =DSUM(database,field,'$A4:$A5) where A4 = "destin"; A5 = "Consolidator 1" row 2 =DSUM(database,field,'$A6:$A7) where A6 = "destin"; A7 = "Consolidator 2" ..... row n =DSUM(database,field,'$An:$Am) where An = "destin"; Am = "Consolidator n" I waste a lot of rows for this criteria only because I do not know ho to create it another way, I mean (A4, A6 and so on) Problem #2 I have named several range of cells in workbook. I want to change...

combine 2 vbs into one
on.vbs: Dim fsoSet Set fso = CreateObject("Scripting.FileSystemObject") fso.MoveFile "c:\windows\system32\drivers\etc\POSTS", "c:\windows \system32\drivers\etc\HOSTS" off.vbs: Dim fsoSet Set fso = CreateObject("Scripting.FileSystemObject") fso.MoveFile "c:\windows\system32\drivers\etc\HOSTS", "c:\windows \system32\drivers\etc\POSTS" how do i combine this 2 vbs into 1? "xmd" <fwdsd45@gmail.com> wrote in message news:dcba46df-b0ac-475f-b4fc-8c4ef78b56a3@q22g2000yqm.googlegroups.com... > on.vbs: ...

Code to combine workbooks
Hi, I need to combine several workbooks into one workbook. Say, individual workbooks are named A, B and C, the master workbook is Master. They all have the same format and are stored in directory c:\Data. Please can anyone help and send me the code Thanks Andy Sub GrabData() Dim varr As Variant Dim rng As Range Dim i As Long Dim sh As Worksheet Dim wkbk as Workbook varr = Array("A.xls", "B.xls", "c.xls") Set sh = Workbooks("Master.xls").Worksheets(1) For i = LBound(varr) To UBound(varr) Set wkbk = Workbooks.Open("c:\Data\" & var...

Writting Function using VBA
Hi I am trying to write a function to return an address but instead I get #VALUE!. Public Function fnd(a, b) fnd = Range(a).Find(b).Address End Function Please help. Thanks. ..Find won't work in UDF's called from the worksheet until xl2002. Depending on the range (a), you could use application.match() through each column. If the range is small, you could just loop through the values in that range, too. nc wrote: > > Hi > > I am trying to write a function to return an address but > instead I get #VALUE!. > > Public Function fnd(a, b) > >...

How to combine text from 3 cells into 1 cell with space and ", bet
How to combine text from 3 cells into 1 cell with space and ", between. Is it a formala I can use? HI Try this =A1&" "&""""&B1&" "&""""&C1, adjust range to your needs HTH John "JOF" <JOF@discussions.microsoft.com> wrote in message news:3BD2A42E-BCB9-4504-8454-727E366E25DD@microsoft.com... > How to combine text from 3 cells into 1 cell with space and ", between. Is > it > a formala I can use? ...

converting hand drawn sketches into Visio Cross Functional Horizon
Hi , I am new to Visio 2003 . Can anybody tell me how can one convert hand drawn sketches into Visio Cross Functional Horizontal diagrams? Thanks Harsha Are you asking about a way to scan images into Visio and transform them into drawings? Or are you asking how to assemble a Cross Functional diagram? To make a Cross Functional diagram, you first choose how many bands to create. Then you click on the headings for the bands to change the text to the names you want. Switch to the Basic Flowchart shapes in the Shapes Window and drag out the Process shapes to create the steps in you...

Excel super slow on paste function in 2007
When you copy and paste one cell to another I get the circular waiting bar and after 4-5 seconds it pastes. If I'm doing a bunch of cells, it still takes 4-5 seconds per cell and the cells will appear right to left, 1 every 4-5 seconds until it's complete. If I hit ESC, everything copies and pastes right away. This started happening when we converted a 2003 file to 2007. It affects everyone so it's not my PC, it's the document. There are no active add-ons but I recently downloaded an add-in that I got from this discussion board to find hidden links (it's d...

Combining certain matrices
Can anyone think of a way to add certain matrices together, depending on some matrix criteria? Situation: X Y Z Bob A 2 3 1 Bob B 4 1 5 Bob C 3 2 6 X Y Z Sue A 4 3 3 Sue B 4 1 5 Sue C 2 2 5 X Y Z Bob A 2 3 1 Bob B 3 5 5 Bob C 5 2 1 X Y Z Sue A 3 3 1 Sue B 4 1 2 Sue C 2 5 6 I want to combine all the matrices that belong to Bob, etc (ie. add all A-X together, all B-X, e...

creation of combinations
Divisions Old BA Old CC OLD GL No. New BA NEW CC New GL XYZ 123 1000 37000 500B 10000 700000 ABC 235 2000 47500 600C 20008 800000 DEF 370 2500 50000 700D 26007 905250 runs to about 600 lines in each column From the above i need to get all combinations like: Divisions OLD CC & GL NEW CC & GL XYZ 100037000 1000700000 Can anyone advise pls ...

Automatic Functions in Excel
Hi There I am developing a Time Sheet in Excel for Staff to record times worked on it. I am using the 1904 date system as occasionally a negative total will be displayed. I would like it if someone was on annual leave they would be able to record this by typing in "Annual Leave" or "A/L" or something similar then the total for that day to display as 7:00 (this is in hours). At present the "total" column runs a calculation of the time finished minus the time started. Is this possible? Regards Colin Hi Colin, StartTime in column A, end ti...

is there a baby-sitting function in Exchange
SBS2003 Premium. One user is not very diligent on reading and responding to emails. He is on the road quite a bit and when he gets back he will not remember that one important email that we needed to respond to. Is there a way that I can have other users have his mailbox open as well, so that they can monitor it and make sure things get replied to? There appears to be such functionality in OL2003, but it just doesn't open the mailbox. Plus his password changes every 30 days, as per the administrator settings, how do we deal with that? Thanks in advance, Robert Assign Full Mailbox ...

How to Declare Pointer to Member Function?
I have a thread that needs to make data visible to a function inside a separate class. In the past, I was using PostMessage to get my information to the class function, but occasionally a message gets lost this way. I could declare the class function as static, but then the function would not be able to interact with other parts of the class. The data is thread safe. // header: typedef void (*PtrToLpMsg)(LPTSTR lpMsg); PtrToLpMsg g_AddStatusMsgFn; // code: CMain::CMain() { g_AddStatusMsgFn = AddStatusMsgA; } void CMain::AddStatusMsgA(LPTSTR lpMsg) { // other code } On Mon, 16 ...

Combining books?
Hi all, I have a monthly timesheet that has been saved as a bunch of 1-page books. I would like to combine all the 1-pagers into one book, and add year-to-date calculations, and use it on an ongoing basis. No idea where to start, so any help would be appreciated. Thanks! -tg Hi, A good place to start would be combining all sheets into one book. D this by knowing which workbook will be your 'Master' book, then ope each file, right click the sheet and select Move or Copy. Choose you 'Master' book to send to, no need to create a copy - unless it's th only sheet in...

Function to control how data displayed
I have 2 columns that I exported from Access to Excel. In Access th columns were Yes or No. In excel they display as True or False. I wan them to show as Yes or No what is the function to make this happen? Thank you! -- LOgle531 ----------------------------------------------------------------------- LOgle5318's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2659 View this thread: http://www.excelforum.com/showthread.php?threadid=39860 LOgle5318, Assuming your True and False are in columns A & B then In C1 put =IF(A1="True","Yes") and...

combining duplicate row values?
Excel 2007 XP Pro SP3 I have several rows of data as: 1 gl 24 36 cw1 north 1st 1 g2 24 36 cw2 north 1st 1 gl 24 36 cw1 north 1st 1 g2 24 36 cw2 north 1st 1 gl 24 36 cw1 north 1st that I would like to combine (preferably into a separate worksheet) as: 3 gl 24 36 cw1 north 1st 2 g2 24 36 cw2 north 1st based on rows 1, 3, and 5 and rows 2 and 4 having duplicate data. Is there a way to do this? Dave DDP 1) Use the options in advanced filter to copy unique records to a location in the same worksheet. (Advanced filter does not copy to alternate sheet). 2) Move the records to the required sh...

XL2003: INDIRECT() function changes calling cell
Hello all, It appears that the INDIRECT() function changes the calling cell. Let me try to explain: I have a dynamic named range "NR6.PerMonth": - When "=NR6.PerMonth" is in cell C27 on the "Summary" sheet, it will refer to/return cell C982 on the "6" sheet. - When "=NR6.PerMonth" is in cell D27, it will refer to/return cell D982....and so on and so forth, going across. BUT!!! (A26 on the "Summary" sheet is a formula that returns the number 6...A982 on the "6" sheet is a label): - When '=I...

Average Function #2
Hi Everyone, I am using Excel 2003. I understand there is probably a very sophisticated way to get what I want but I need to learn the simple way first. I have a data sheet named "MICU". I inserted another sheet in the workbook called "Stats". I want to break down the data by month on my new Stats sheet. The MICU sheet has many columns of info. The 2 I think I need to use is a column for start date "ThpyStDtTm" (Date and Time field 3/14/09 18:41) and a column called "VentLOSDays" (Number format with 2 decimal places). Column A Column...

combinations of letters
For a problem, I need to list all of the possible combinations of uppercase/lowercase letters. Each combination should have all 26 letters of the alphabet in either uppercase or lowercase form. There are over 67million combinations. Is there a way to get excel to list these combinations? On Thu, 14 Feb 2008 12:25:21 -0800 (PST), hiflyinskiier@gmail.com wrote: >For a problem, I need to list all of the possible combinations of >uppercase/lowercase letters. >Each combination should have all 26 letters of the alphabet in either >uppercase or lowercase form. There are over 67millio...

How to call a function from another workbook
Dear sir, how can i call a function or procedure that contained in another workbook ? thanks Joe Hi When you want to use an UDF or procedure in several workbooks, then save it into a module of Personal Macro Workbook (Personal.xls, it's created automatically, when you select Personal Macro Workbook as destination to save a newly-created macro, and later is loaded automatically whenever you start Excel). -- Arvi Laanemets (When sending e-mail, use address arvil<At>tarkon.ee) "Joe" <Joe@discussions.microsoft.com> wrote in message news:AF3EABAE-6751-4917-88F1-1A5...

Function Call Problem
Could anyone help me with this Dialog App (first visual app). I am writing a test program for using Tab Controls. However, I have found that I am not sure how to call a class member function from the main dialog class to another dialog class. The program compiles normally. But the function call is causing an "Assertion Failed" error message whenever the button is clicked. It is used to print a number in a dialog of a tab control, and it also prints the same number in the main dialog. Both of these numbers are printed in Static Text Controls. This is the code from the main Dial...

count function in pivot table
How to count the number of types under certain group in a pivot table: Such as one sku occurs twice under one customer, I only need 1 in pivot table, instead of count the numbers of occurrences: 2 A pivot table won't calculate a unique count. You could add a column to the list, then add that field to the pivot table. There's an example here: http://www.contextures.com/xlPivot07.html#Unique Holly wrote: > How to count the number of types under certain group in a pivot table: > > Such as one sku occurs twice under one customer, I only need 1 in pivot > table, inst...

Money Won't Function
Greetings, I'm using Money 2006 Standard Edition, and I wanted to try the latest version of Money Essentials. The FAQ said there were no problems installing the trial alongside your existing installation because it installs into a separate directory. Well, my trial just expired, and now that I removed Money Essentials, I now have two problems: 1. Money 2006 no longer plays sounds. 2. When I click the "download" button at my bank's web site, the information no longer gets downloaded into Money. Instead I get a file download box with a filename ending in either *.ofx o...

Sub not Function!
To make a comparison of two tests I want to put the values next to eac other on a sheet. To do that, I have two Combo Boxes with each the same 15 choises...yo have to choose two different tests. Now, I'm having a problem to make my code short: -Dim leftChoise As Integer leftChoise = Sheets("Keuze").Range("E1").Value Dim rightChoise As Integer rightChoise = Sheets("Keuze").Range("K1").Value Select Case leftChoise Case 1 PutLeftOnReport ("B7") Case 2 PutLeftOnReport ("B29") Case 3 PutLeftOnReport ("B51") Don't u...

PivotTable canned functions
I am using Excel 2000 (9.0.2720). In my work I often faced with the task of creating ratios of CurrentListValue / CurrentSaleValue then need to perform various calculations on those ratios to identify and conclude on Central Tendencies (among other things) within categories from a parent array. The list of functions available for PivotTable calculation (I am assuming under the hood these are Array Functions) is very narrow. Only providing for Average, Standard Deviation, Min, Max, etc. Is there a way to add to this list, such as but not limited to, Average Deviation, Median, Geometr...