# 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
ADVERTISEMENT
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
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
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
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
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
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
Mar 14, 2008

I am aware of the following topic in the VBA Help file:

"Using Microsoft Excel Worksheet Functions in Visual Basic

You can use most Microsoft Excel worksheet functions in your Visual Basic statements. To see a list of the worksheet functions you can use, see List of Worksheet Functions Available to Visual Basic.

Note Some worksheet functions aren’t useful in Visual Basic. For example, the Concatenate function isn’t needed because in Visual Basic you can use the & operator to join multiple text values."

And I'm aware of how to call Excel funcitons from within VBA; e.g., answer = Application.WorksheetFunction.Min(myRange)

However, not only are some Excel functions not useful; the fact is they cannot be used because VBA has a native function that does exactly the same thing and you have to use that native VBA function to achieve your goal. It is these overlapping functions that I am especially interested in. I want to know what I should use directly in VBA and what I need to go to Excel for.

View 9 Replies
View Related
Dec 11, 2009

My objective is to replace a text string with a numeric variable. The string has text values such as US, Canada, Germany, France etc, There are about 15 such words in total. Each row will have a different one of the 15 text strings.

Each text string has a corresponding number, which I want to use in various formulas throughout the spreadsheet. The numbers are located on a separate worksheet (“Match Rate”), within the same workbook. I obtained the numbers from a formatted report, which I simply pasted into the second worksheet. Due to the complexity of the report, I can’t reformat the numbers to allow use of the VLOOKUP function.

I created the following function, but unfortunately it doesn’t work. Can you help, please?

I’d like to stay with a VBA solution, since a simple if/then sequence such as this is an excellent introduction to the powerful world of VBA... (it just doesn’t work, yet). I suspect the problem is in the calls to the Match Rate Worksheet.

View 11 Replies
View Related
Jan 17, 2008

Ok. Here's what I'm trying to do. I have a drop down menu of certain providers located on cell F5. Each one charges different rates per day located on another sheet. Here's the kicker two of those providers charge different rates depending on the amount of days. Those two are UASC and CMA. For the First 5 days they charge one rate and after the 5 days they charge an increased rate.

I'm trying to have one cell located on the first sheet display this information with all the proper calculations. The below formula gives me a false value. I'm not even sure if it works. I know a little bit of excel through some intro courses in college but I'm not sure if a formula like this would work. I know laugh it up.... : ) but I'm not too saavy with formulas....

View 9 Replies
View Related
Jul 8, 2008

I have a total of 30 nested formulas that (ofcourse) dont work in excel.

Here is an example of the first 7 nested IFS

(1) IF(MID(A7,8,9)="GIR",INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0)),

(2) IF(MID(A7,8,9)="NL",INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0)),

(3) IF(MID(A7,8,9)="BE",INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0)),

(4) IF(MID(A7,8,9)="AT",INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))

(5) IF(MID(A7,8,9)="DE",INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))

(6) IF(MID(A7,8,9)="PH",INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))

(7) IF(MID(A7,8,9)="AUBE",INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))

I tried this code but it is not working.

Function LookupRetrieve(pVal As String) As Long

If pVal = "(MID(A7,8,9)="GIR" Then

LookupRetrieve = INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!

C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))

ElseIf pVal = "(MID(A7,8,9)="NL" Then

LookupRetrieve = INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!

C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))

ElseIf pVal = "(MID(A7,8,9)="BE" Then

LookupRetrieve = INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!

C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))

ElseIf pVal = "(MID(A7,8,9)="AT" Then

LookupRetrieve = INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!

C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))

ElseIf pVal = "(MID(A7,8,9)="DE" Then

LookupRetrieve = INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!

C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))

ElseIf pVal = "(MID(A7,8,9)="PH" Then

LookupRetrieve = INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!

C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))

ElseIf pVal = "(MID(A7,8,9)="AU" Then

LookupRetrieve = INDEX('ERIS INPUT'!$C$2:$C$65536,MATCH(A7,IF(Reconciliatie!

C$3:C$65536=C7,Reconciliatie!A$3:A$65536),0))

Else

LookupRetrieve = "isingcode missing"

End If

End Function

The formula is a vlookup formula (Index/Match but with multiple criterias such as the use of Mid).

I tried Concatenate but the formula is too long and has a lot of characters and VBA seems better.

View 9 Replies
View Related
Dec 7, 2009

I'm building a macro and need to know how to add a nested if-then statement. I need to create a formula in a new column whose value extends to the last row of the database. My base formula is sumproduct(--((A2:A:last row which I need to calculate&B1:B same thing)=A2&A2))

View 9 Replies
View Related
Mar 20, 2014

I am trying to make this work. VLOOKUP (B2,"lookup tables'!$A$4:$A$11, if(D2'="ST" 'lookup tables!$C$4:$E$11.

I am attaching the file : MDCC Reservations.xlsx

I am trying to get the Daily room charge.

View 5 Replies
View Related
Nov 7, 2007

Level RatingA RatingB RatingC Low Medium High

2 x x x x x x

3 x x x x x x

4 x x x x x x

5 x x x x x x

I need to be able to do nested loops. I want to have a criteria that if a person is in LEVEL 2 and RATINGC and HIGH, then take the value under that.

View 9 Replies
View Related
Aug 5, 2009

Im needing to achieve what an 11 argument nested if would manage, ie: ...

View 2 Replies
View Related
Sep 24, 2009

I'm trying to build this formula and I'm only half way through it and it already isn't working.

Column D - can be 1, 2, 3, or 4

Column E - can be any number

If column D is 1 or 3, then the VLOOKUP should look up column E and point to the answer in column 4 on another worksheet (Companies in this case). If column D is 2 or 5, then the VLOOKUP should look up column E and point to the answer in column 5 on another worksheet (Companies in this case). This is what I have so far, just trying to get it to recognize if it is a 1 or 2:

=IF(OR(D2=1,VLOOKUP(E2,Companies!A:G,4)),IF(D2=2,VLOOKUP(E2,Companies!A:G,5)))

What is making this not work? I'm getting a false. But before I added the OR, it worked for the D2=1.

View 3 Replies
View Related
Oct 30, 2009

I'm hoping someone can help me sort this out. I have a formula I need te determine if a TAT was 'met' or 'missed' and it has multiple criteria. Here is the formula I am trying to get to work:

=IF(AND(I2="Amendments",F2<=3,IF(AND(I2="Renewals",F2<=1,OR(D2<=G2)))),"Met",IF(F2="n/a","n/a","Missed"))

I've been able to get separate pieces of the formula to work correctly, example:

=IF(AND(F2<>"n/a",I2="Amendments",F2<=3,OR(D2<=G2)),"Met",IF(F2="n/a","n/a","Missed"))

but when I try to combine everything into one string I can not get it to work.

View 10 Replies
View Related
Aug 11, 2009

I seem to be having trouble creating a nested loop. It seemed simple enough in my brain holder, however, in real life...Well I am here aren't I. Here is my

View 2 Replies
View Related
Dec 1, 2005

I am trying to create a nested IF function (has 5 IF statements), however,

every time I put the brackets at the end of the function, it puts a little .

next to the false answer, removes the brackets and comes up with an error

message?

This is only happening on one particular PC, when I create the same formula

on other pC's, it works fine so I know my formula is correct.

is there a setting on the PC that must be wrong?

View 14 Replies
View Related
Dec 22, 2005

I am trying to write a function such that the data returned to a given cell

will vary depending upon the data entered into another cell. For example:

If A1= 105, then G1=20; if A1=106, then G1=15

I have more variables for A1 than 7, so I don't think that nested if...then

statements would work. Can I do this using VLOOKUP?

View 10 Replies
View Related
Feb 6, 2009

I have a simple Staff Rote which is fed by data on Time Sheet. Currently it is working ok with two definitions;

T : Time

B: Break

If I introduce a 3rd and 4th definition like;

A: Leave

X:non working day

formule will get even more complicated.

Is there a way of simpifying this with a code and introducing the new definitions.

View 11 Replies
View Related