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
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
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
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
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
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
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
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
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
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
Feb 9, 2009
I have the following formula in a cell:
View 2 Replies
View Related
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
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
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
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
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
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
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
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
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
Aug 4, 2009
Current Formula
{=IF(AND(G144>0,F144:F173
View 9 Replies
View Related
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
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
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
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
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
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
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
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
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
