Multiple Condition Nested Logical Functions

Feb 17, 2014

I am having trouble evaluating two conditions with nested logical operators to produce the output I need.

For example Condition 1 has 5 choices (A,B,C,D,E) and Condition 2 has 3 choices (a,b,c).

Breaking it down,
[Condition 1] option "A" always applies and should give "Yes" (No matter [condition 2], "A" always gives "Yes")
[Condition 2] option "c" always applies and should give "Yes" (No matter [condition 1], "c" always gives "Yes")

[Condition 1] option "D" never applies and should give "No" (No matter [condition 2], "D" always gives "No")

[Condition 1] options "C" and "B" only apply if [condition 2] is "b" (if so, the answer is "Yes")

What I need is the equivalent of:

if ((([condition 1] == A) || ([Condition 2] == c)) || (([condition 1] == B || C) && ([condition 2] == b)), "Yes", "No")

View 4 Replies


ADVERTISEMENT

Unable To Apply Multiple Logical Conditions Using If / AND OR Functions

Feb 19, 2014

I have to decide if the final answer will be yes or no based on a combination of 4-5 cell values from a single row.

I have attached the details in the excel sheet.

View 4 Replies View Related

Multiple Nested IF & SUM Functions/Formulas

May 23, 2008

I am currently having trouble with what I thought was a simple IF statement. As you will see from the formula I want to complete a statement for every month of the year but Excel will not let me go past July. Is there a limit to the number of arguments in an IF statement and how do I overcome this? =IF($A$3="Jan'08", SUM(C7),IF($A$3="Feb'08",SUM(C7:D7),IF($A$3="Mar'08",SUM(C7:E7),IF($A$3="Apr'08",SUM(C7:F7),IF($A$3="May'08",SUM(C7:G7),IF($A$3="Jun'08",SUM(C7:H7),IF($A$3="Jul'08",SUM(C7:I7),"n/a")))))))

View 5 Replies View Related

Use Of Logical Functions

Jun 16, 2006

why the following code within a macro does not work

Cells(x,11).Value = If(Or(Cells(x,136)="", Cells(x,134)="","",Cells(x,136)-Cells(x,134))

View 9 Replies View Related

Logical Functions Query

Nov 28, 2008

i had a query regarding a report i have to provide daily. i want the result to be in the form of a number which i pick up from another file.

now there are three colums in this file. one shows the date (i.e. in the ddmmyyyy format, e.g. 5/11/2008). another shows a region (e.g. SOUTHD) and the third shows a number (e.g. 445) of which i have to count how many instances occur for a particular day.

Hence for any given cell in which i would place this formula, i would need the number of times the number 445 occurred from the third column provided the date from the date column was, say, 5th November 2008 and the region was showing as SOUTHD.

pretty sure the COUNTIF function can be used to make such a formula when combined with some logical functions but am unsure on how to do so.

View 11 Replies View Related

Logical IF Functions To Retrieve Filled Cells

Feb 28, 2009

I am working on a sheet to retrieve some cells which are not empty or equal to 0. Like..

A B
Item A
Item B 5
Item C 10
Item D
Item E 5
Item F

I want to retrieve only the cells which have got some values in column B. Like I want to get the list as follows


Item B 5
Item C 10
Item E 5

View 6 Replies View Related

Multiple "nested" Functions In A Spreadsheet -

May 25, 2009

I am trying to make a multiple nested spreadsheet. I get as far as the first 2 and then can't seem to get the data validation to work. I tried several different formulas (indirect(vlookup)) ones but must be doing something wrong. I have the "lists" on the second sheet to be referenced for the first sheet as drop down menus.

I do building material appraisals and this sheet will be to help me with the inspections if I ever get it done. It is supposed to work like this - Area - Item - then depending on the item - three consecutive dropdown lists with further information.

View 4 Replies View Related

NESTED IF And AND Functions?

Jun 30, 2014

I am trying to write a formula that will satisfy the following:

C1 and G1 are number values.

IF (cell E1 does not contain any text) AND (G1-C1-12.5>=0), then output (G1-C1-12.5), otherwise output nothing (""). BUT, IF (cell E1 DOES contain text) AND (G1-C1-13>=0), then output (G1-C1-13), otherwise output nothing.

View 2 Replies View Related

Nested If/or Functions

May 7, 2009

IF/OR function below is not working

=IF((OR(B14=1,2,3,12,15,16)),"A",IF(OR(B14=4,5,6,7,8,9,10,11,13,14,21,40,41,42,43,44,45,46,47,50,51, 52,53,54,55,56,59,60),"B",IF(OR(B14=20,30,57,58),"C",IF(OR(B14=33,31,32,34,35,22),"E F OR G",IF(OR(B14=36),G,"ERROR")))))

View 4 Replies View Related

IF AND Functions Nested

Aug 20, 2008

I recently came across a spreadsheet an ex-employee wrote with an interesting IF-AND statement in it. The only way I've ever written one is:

=IF(AND(something=1, somethingelse>2), do something)

His statement was written in the following manner:

=IF(something=1*AND(somethingelse>2), do something, if(thing3>1*AND(thing4>1), do something)

View 9 Replies View Related

Nested Functions Limits

Feb 9, 2009

I have the following formula in a cell:

View 2 Replies View Related

Multilple Functions In Nested IF

Nov 9, 2009

I have these 4 functions that i want to be able to "run" in a nested IF where the functions are used in this manner:

=if((funct1 or funct2 or funct3 or funct4);"OK";"")

funct1: =IF(AND(B7=1;D7=1;E7=1);"OK";"")
funct2: =IF(AND(B7=0;C7=1;F7=1;A7<10000);"OK";"")
funct3: =IF(AND(B7=0;C7=1;G7=1;A7<25000);"OK";"")
funct4: =IF(AND(B7=0;C7=1;H7=1;A7<50000);"OK";"")

View 14 Replies View Related

Using Nested RANK Functions

Aug 7, 2006

I have a sales ladder which ranks my people by the % difference between budget and sales to date, but not everyone has made a sale yet and the % difference is zero for 6 people. These 6 people all have a rank of 75. Where this happens, I want to then rank only those 6 people, based on their budget figure.

My current formula reads:
=IF(F83=0,75,RANK($G83,$G$9:$G$83,0))
where F is the sales value and G is the % difference. Budget figures are in column D.

View 11 Replies View Related

Nested Functions On Text

Jun 6, 2009

=If(isError(find("-";a1));mid(a1;3;9);mid(left(a1;search("-";a1)-1);3;9))
where: a1=AN03048-12
and its result I wanted is the following: 03048 (great! )
But... what if I'd have ANN03048-12?
Then the result would be N03048...

How can I obtain only 03048 or whatever (but only numbers) avoiding previous letters? How can I only pick numbers without letters starting from the left beginning of the record? I'd like to get only numbers before the "-" symbol. I think I should add a function before or instead of the "3" number (which I highlighted in red) in the second mid() function

View 10 Replies View Related

Nested Functions Limit

Apr 1, 2008


=SUM(IF(WEEKDAY(calendar,2)=2,IF(RIGHT(C35:C400)="H",
SUBSTITUTE(C35:C400,"H","")/7.5)))))))

I need to nest the above formula within an IF statement. Unfortunately, I am already up to the Excel limit of nesting 7 functions.

Can anybody see, at a glance, how I can achieve the same result with at least one less nested function?

View 9 Replies View Related

Nested IF And COUNTIFS Functions

Feb 23, 2010

I would like Excel to look at column A, and if there is a number0, count it and go to the next row. If it's =0, then go to column B and see if THAT number is 0. If it is, count it; if not, go to C. So my data looks like:

A B C
0 2 3
4 0 0
0 0 0

I only want it to count one time per row, so right now I am using =IF(COUNTIFS(A2:A30,"0",B2:B30,"0")0,COUNTIFS(A2:A30,"0",B2:B30,"0"),IF(COUNTIFS(B2:B30,"0",C2:C30,"0")0,COUNTIFS(B2:B30,"0",C2:C30,"0"),0)))

I also tried it with a SUMPRODUCT to simplify but both formulas are getting me the wrong answer. I'm using Excel 2007.

View 9 Replies View Related

Alternative To Nested IF Functions

Nov 2, 2004

I have 12 teams(rows) and would like a cell(A21) to show the team name after determining the max value of the column(O). Is there another way to do it besides "ifs", I've maxed out the function at 7, but there are 12 teams

If I could get this figured out it would be great, and a seperate question/addition problem I would also like to do is have the cell come up the same color as it is above.

View 5 Replies View Related

Excel 2007 :: Too Many Nested Functions

Jan 31, 2014

Attached is a small spreadsheet, my problem is I am using Excel 2007 and it only allows me 7 nested functions and I need to do 31.

Nested Function.xlsx‎

View 8 Replies View Related

Nested Functions IF, AND, OR, NOT: If A Part Is Discontinued, It Cannot Be Ordered

Nov 27, 2009

using function IF, AND, OR, NOT the following is the description and attached is the photo of the excel file.

1-If a part is discontinued, it cannot be ordered.

2. If a part is not discontinued, is a high-demand part and is currently stocked at less than 75% capacity then it’s time to order more.

3. If a part is not discontinued, it not a high-demand part and is currently stocked at less than 50% capacity then it’s time to order more.

View 5 Replies View Related

7 Nested Functions Alternative - Substitute Function

Nov 4, 2008

I want to substitute the following "special" characters for an underscore. i need to do 9 different characters!!! is there another way?

as you are limited to 7!!!

excel 2003!

=INDIRECT("_animal_"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C2," ","_"),"/","_"),"-","_"),"&","_"),"~","_"), "(", "_"), ")", "_"), "$", "_"), ":", "_"))

View 9 Replies View Related

Nested IF/And Condition Formula Assistance

Aug 4, 2009

Current Formula
{=IF(AND(G144>0,F144:F173

View 9 Replies View Related

Logical Function: Logical Test With Three Possible Results

Apr 8, 2009

I am trying to create a logical test with three possible results:

h2 <10.2 "needs improvement"
(if false) h2=10.2 "meets standard"
if false h2>10.2 "exceeds standard"

Basically, I am referring to a cell to determine if it meets, exceeds or does not meed a specific standard. In this case 10.2.

View 4 Replies View Related

Nested IF Function - Condition Is Based On Two Values Within A Cell

Mar 10, 2014

I am working on creating a simple Excel Database of an Engineering Tool Room Inventory for work. However I can not seem to get one particular 'NESTED IF Function' to do what I want it to do. (In advance warning, I may be going down the wrong route and might have used the wrong Formula in Excel but from my understanding the 'IF' function seemed the most plausible.

Issue: All I would like to do is for a NESTED IF Function to be able to say this:

1. When a user inputs data into one column, for example 'Column A', with the following data: 4" Square
2. Then the neighbouring column, for example 'Column B', would have a NESTED IF Function.
3. Which would output a logical response, "A, B, C, D, E, F etc...", depending on what is written in Column A.
If 'Column A' - 4" Square, Then 'Column B' - A
If 'Column A' - 5" Square, Then 'Column B' - B
& So on...
But the NESTED IF Function should only put a logical response if there is certain values within the cell, for example:
User types a value into 'Column A' which contain - 4 & S, Then 'Column B' - A
User types a value into 'Column B' which contain - 5 & S, Then 'Column B' - B
& So on...

Currently to do the above function, I have utilised the following Excel NESTED IF Function:

[Code] .....

& The following happens:
1. User inputs data into 'Column A'
2. The NESTED IF Function then provides an automatic response.
3. However it relies on the data being inputted without the ' " (Quotation Symbol)'
4. & With me defining the exact phrase that should be typed into the cell, for example, "4" Square"

Other: I have attached an example worksheet of the NESTED IF Function in practice, with two tables. One showing what is happening with the code above and one table showing what I would like to happen but with no function.

Example - IF Function.xlsx

View 3 Replies View Related

Multiple Outcomes From A Logical Formula

Sep 28, 2009

I am trying to get more than one outcome from a logical function in a single cell. I am teaching a class and i want this particular cell to add up all the points from tests and quizzes and then give me an outcome of a grade (A,B,C,D, or F) Right now i can only get the cell to give me an outcome of A or B. I need the cell to be able to give me 5 possible outcomes (A,B,C,D,F).

Right now i have this formula in this particular cell......=IF(Q6>234,"A")......so if Q6 is greater than 234pts they get an A...if not they get a B. How can i also give ranges of total points to have the cell give C,D, or F?

View 5 Replies View Related

If Logical Test Multiple Criteria

Feb 17, 2012

I am trying to build a logical formula and it turns wrong values. I have four criteria in numbers, the codes are: 1 invoices, 3 credits, 7 Debits, 8 Returns, 9 Payments. All of these codes have positive numbers. I am trying to convert "credits", "returns", "payments" in negative . the rest "invoices" "debits" stay positive. In column A i have dollar values all positive, column B "codes" mentioned above, column C "i want to put the correct values.

View 9 Replies View Related

Basic IF Function With Multiple Logical Tests

Jul 10, 2014

Basically, I'm trying to calculate a pooled z-score from z-scores already stored in different cells.

Each individual z-score are calculated like this :
D1=IF(A1=999,999,((A1-10)/3)).

They are derived from standard scores with a mean of 10 and a SD of 3. I use 999 to mark missing values and this work flawlessly for single z-score cells.That way, if A1 is a missing value (999), Excel returns a value of 999.

Now here's my problem. How do I tell Excel to ignore 999 values if I am pooling multiple z-scores together?

Let's say A1, B1 and C1 are single z-scores. I want D1 to be the pooled z-score result, but I want it to ignore A1,B1 and/or C1 if their value is 999. For instance, if A1=+2.0 , B1 = 999 and C1= -1.50, I want my D1 cell to ignore B1 in the calculation.

I've tried : D1=IF(A1,B1,C1=999,999,(LC5+LI5+IK5)/3)) but it sure doesn't work...

View 7 Replies View Related

Return Multiple Values From A List That Fit Logical Test?

Nov 15, 2013

I need a formula that will return a range of values from a list.

Example: I need a formula in cell A2 that looks at the list and will return all numbers that are >99 but 199 but299 but

View 6 Replies View Related

How To Return Multiple Values In Multiple Cells If Argument Is TRUE In IF Functions

Jan 8, 2013

I want my IF function to populate two cells with two values if argument for IF function is found TRUE. Is it possible?

IF(logical_test, [value_if_true], [value_if_false])

value_if_true = return multiple values in mutiple cells (for example put number 8 in cell A2 and number 10 in cell A3 if function is TRUE)

How would I do that? I tried putting IF(logical_test, (A2="8",A3="10"), [value_if_false]) but it is not possbile...

View 3 Replies View Related

Row Nested If With Multiple Cells?

Aug 20, 2013

Is there a way on excel to produce a nested IF that says when 3 cells out of a row of 26 cell exceeds 100, then 'issue' is shown? So far I have the worksheet set up using:

'=IF(OR(C5=$C$2,D5=$D$2,E5=$E$2,F5=$F$2,G5=$G$2, H5=$H$2,I5=$I$2,J5=$J$2,K5=$K$2,L5=$L$2,M5=$M$2,N5=$N$2, O5=$O$2,P5=$P$2,Q5=$Q$2,R5=$R$2,S5=$S$2,T5=$T$2,U5=$U$2, V5=$V$2,W5=$W$2,X5=$X$2,Y5=$Y$2,Z5=$Z$2 ),"Nusiance","No Issue")'

Which is each single cell in the row, but I'd like to go a step further for it to be if any 3 of these calls exceeds 100, then nuisance or no issue is shown and not just if one single cell exceeds it.

View 1 Replies View Related

Multiple Functions

Jan 25, 2009

For the odds range, i have already two cells(the upper(F9) and the lower(E9)) and i have a cell named the current odd(G9). What are the formula together for the attribut cell(H9) for the following data:

1.0 : The current price is outside and below the odds range spread. For example, if the range is between 2,00 and 2,20, the range’s attribute will be 1.0 if the current price is below 2,00
NOTE: Obviously the formula for this one is =IF(G9

1.1 : The current price is on the bottom edge of the “odds
range” figure. For example, if the range is between 2,00
and 2,20, the range’s attribute will be 1.1 if the current
price is 2,00
NOTE: The formula for this one would be =IF(G9=E9,1.1)

1.2 : The current price is inside the “odds range” spread
and is placed one or two clicks from the bottom edge. For
example if the range is between 2,00 and 2,20, the range’s
attribute will be 1.2 if the current price is 2,02 or 2,04
NOTE: I have a hard time creating a formula with this one.

2.0 : The current price is outside and above the “odds
range” spread. For example, if the range is between 2,00
and 2,20, the range’s attribute will be 2.0 if the current
price is above 2,20
NOTE: The formula would be =IF(G9>F9,2.0)

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved