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


ADVERTISEMENT

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

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

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

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 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

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

Functions Compared With VBA Functions

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

More Than 7 Nested If -Then - Else Through VBA

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

IF And Then Nested. How Much Nested?

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

Nested IF In VBA

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

Nested If-then

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

VLOOKUP With Nested IF

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

Nested IF Or VLOOKUP

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

Going Beyond 7 Nested If Arguments

Aug 5, 2009

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

View 2 Replies View Related

Nested IF/OR With VLOOKUP

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

Nested 'IF' Statements

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

Nested Loops..?

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

Nested If Function

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

Lookup And Nested If

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

Nested IFs And VLOOKUPs

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







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