Free Microsoft Excel 2013 Quick Reference

Nested IF(AND(OR Function

Ok this is my first post. I hope I am doing this right.

I think I need to use If And OR functions to do what I want but there is to many combinations and I am not to good at this. So here it goes.

Cell B7 could be SD CC or WO PBM. Cell K3 could be 4 or 5. Now depending on what the two cells contain cell K7 should get its value from one of 4 other cells F2 or H2 or F3 or H3.

Example: If B7 is SD CC and K3 is 4 then K7 should be F2 (15.00) But if B7 is WO PBM then K7 should be F3 (8.40). Now it would change if K3 is 5 that would make K7 choose from the other 2 cells H2 and H3.

I hope I explained this OK. I have tried to do this and I can get it to pick only one set of cells F2 and H2 I think. By now I don't know what I have. This is just some code I have tried. I have had I don't know how many different versions.


	VB:
	
(B7="SD CC",K3=4),F2,H2) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Here is a my sheet. And it is 11:00 here and I got to get up at 2:00 so I won't be able to post back tonight.

Thank you for your help in advance. And thank you for baring with me.

Keith


Would appreciate help with the following formula in excel. Not sure if it's a formatting issue or if i've misused the 'if' 'and' 'or' functions as i've only just started to implement them in my work. The following gives me a parse error.

=IF((AND(D2<=25%)),A1,(IF((OR(D2>25%,D2<=35%)),A2,(IF((OR(D2>35%,D2<=45%)),A3,(IF((OR(D2>45%,D2<=55% )),A4,))(IF((OR(D2>55%,D2<=65%),A5,(IF((OR(D2>65%,D2<=75%),A6,(IF((OR(D2>75%,D2<=85%)),A7,A8)))))))) )))))

any help appreciated.

Can anyone help, I need to come up with a formula using the nested IF and OR function for column D labeled family leave. Column B is status(Fulltime or Parttime), Column C is the number of years employed. If the employee is FT and has worked for 1 year or more they get 5 days. If they are FT and worked for less than 1 year or If they work PT but for more than 2 years they get 3 days. Anyone else gets O days. I need help figuring out a formula for this case problem. If you could explain how you came up with the formula. I’m having trouble getting the whole functions part of excel. Thank you.

Ok, so here was my original problem:

I am attempting to nest the and or functions.
I would like the argument to state:

IF C47 and D47 are negative... take the absolute value of g47/d47
IF C47 or D47 are negative.... take g47/c47+1
IF C47 and D47 are not negative... take g47/d47

Here is what i have written.... can someone please help?????? IT IS
GIVING ME ERRORS

=IF(AND(C47,0,d47<0),abs(g47/d47),OR(C47<0,D47<0),(G47/C47+1),(G47/D47))

---------------------------------------------------------------------------------------------------------------------------------------
We were able to fix that with:
=IF(AND(C47,0,d47<0),abs(g47/d47),IF(OR(C47<0,D47<0),(G47/C47+1),(G47/D47))

I have one more question.
What do I do to this argument if when d47 is negative but c47 is
positive, how can I add it so that if this is the case........ take
g47/d47

Here is what i wrote with error again

=IF(AND(C47<0,D47<0),ABS(G47/D47),IF((C47<0),G47/C47+1,G47/D47),IF((,D47<0),g47/d47+1,g47/d47))

I have an inefficient, but working, nested if-and worksheet function formula
that I need to put into a macro. I almost got "do-looped" to death today.
Any help or direction would be most appreiciated. - Randy

Range("H11").Select (Activecell)
ActiveCell.Formula =
"=IF(AND(B11=24,B18=24,B25=24,B32=24,B39=24,B46=24,B53=24,B60=24),TRUE,FALSE)"

If ActiveCell = True Then

Else:

I am attempting to nest the and or functions.

I would like the argument to state:

IF C47 and D47 are negative... take the absolute value of g47/d47
IF C47 or D47 are negative.... take g47/c47+1
IF C47 and D47 are not negative... take g47/d47

Here is what i have written.... can someone please help?????? IT IS
GIVING ME ERRORS

=IF(AND(C47,0,d47<0),abs(g47/d47),OR(C47<0,D47<0),(G47/C47+1),(G47/D47))

i have this problem and cant seem to get it going. if an employee is FT and has worked =>1, then 5 is true. if he is FT and has worked less <1 year or if employee is PT and has worked >1.5, then 3 is true. all other is 0. here is what i have so far but it doesnt seem to work right.

=IF(AND(Leave[[#This Row],[Status]]="FT",Leave[[#This Row],[Years Employed]]>=1),5,IF(AND(Leave[[#This Row],[Status]]="FT",Leave[[#This Row],[Years Employed]]<1),3,IF(OR(Leave[[#This Row],[Status]]="PT", Leave[[#This Row],[Years Employed]]>1.5),3,0)))

Hello,

here's [what should be] a simple formula I ofen struggle with; nested IF statements.

In the attached spreadsheet I need to carry out 15 numerical checks based on what the current value of another field is. Is this possible in a shorter formula to avoid nesting 15 IF AND OR formulas.

As explained in the spreadsheet:

B4 to B18 is static
C4 to C18 is variable
E4 to E18 is variable within a range

Each cell in the range G4 to G18 needs to carry out 15 TRUE/FALSE tests (examples below), but within a single formula

If any one of the tests are TRUE then a return of TRUE is required

Hope someone can help.

Regards,

sherlock99.

Hi,

I'm having a problem with an Excel formula containing several IFs and
ORs.

The formula looks like this (the cell references are not important
here, you can simplify it if you like, it is about the structure of
this whole expression in which there must be an error that causes it
not to yield the results that I want):

In one line:
=IF(C21

Hi,

I'm having a problem with an Excel formula containing several IFs and
ORs.

The formula looks like this (the cell references are not important
here, you can simplify it if you like, it is about the structure of
this whole expression in which there must be an error that causes it
not to yield the results that I want):

In one line:
=IF(C21<$C$62,"Absent",IF(OR(B23="Absent",C21<$C$62),IF(OR(C21/B21<2,ABS(-1/(C21/B21))<2),"*",IF(OR(C21/B21<3,ABS(-1/(C21/B21))<3),"**",IF(OR(C21/B21<4,ABS(-1/(C21/B21))<4),"***",IF(OR(C21/B21>=4,ABS(-1/(C21/B21))>=4),"****","X"))))))

In a somewhat more structured format:
=IF(C21<$C$62,
"Absent",
IF(OR(B23="Absent",C21<$C$62),
IF(OR(C21/B21<2,ABS(-1/(C21/B21))<2),
"*",
IF(OR(C21/B21<3,ABS(-1/(C21/B21))<3),
"**",
IF(OR(C21/B21<4,ABS(-1/(C21/B21))<4),
"***",
IF(OR(C21/B21>=4,ABS(-1/(C21/B21))>=4),
"****",
"X"))))))

What I would like this formula to do is this:

If the first condition (here: C21<$C$62) is true, then I want "Absent"
to be displayed. This works.

If the subsequent OR condition (here: OR(B23="Absent",C21<$C$62) ) is
true, then I want 1, 2, 3, or 4 asterisks to be displayed depending on
the other four OR conditions. This also works.

If the condition OR(B23="Absent",C21<$C$62) is false, I want X to be
displayed (actually, X is another expression but for simplicity's sake
I just call it X here). However, this does not work. Instead, I get
FALSE instead of X.

I can't see the mistake. Could anyone help me make the formula do want
I want it to?

Regards,
Peter

I am trying to get my if and or function to work but I am having problem

If Col1 and Col2 are #N/A I want a 0 to show up

If (Col1>=Col2,Col1,Col2)

But this is the part that I can't get working

If Col1 has a value but Col2 is #N/A I get a value of 0 with my function but I need the Col 1 value

Also if Col2 has a value but Col1 is #N/A I need the Col2 value

here is the function I am using

=IF(OR(ISNA(A2),ISNA(B2)),0,IF(A2>=B2,A2,B2))

Thanks for your help

I have an assignment in which I have to use nested, IF, AND and OR functions. Does anyone know the syntax for this kind of function? Basically I have to determine leave time based on years of service and whether the employee is full time or part time. Part-time with less than 1.5 years of service = 0 days of leave Part-time over 1.5 and full-time under 1year = 3 days of leave and full time over 1 year = 5 days of leave. I know how to do a nested function, but I have not worked with both an AND and OR together.

Thanks in advance!!!

Thanks Agoin

Hello,

Is it possible to nest both AND and OR within and IF statement.? For example:

=IF(OR(D$79="OCTOBER B",D$79="NOVEMBER",D$79="DECEMBER",D$79="JANUARY",D$79="FEBRUARY",D$79="MARCH",D$79="APRIL"), "--> hold", IF(L82="Affected Hold", "--> hold", IF(L82="Affected Move", "-->move", IF(AND(O82=B82,K82="Combine"), "--> hold", IF(AND(O82<>B82, K82="Combine"),"-->move", IF(A82=P82,"--> hold",IF(AND(OR(R81="--> hold",R83="--> hold"),P81<>"System E"),"--> hold", "-->move")))))))

A lot I know, but Excel pinpoints my problem as the OR statement in bold and I'm not sure why? Can I not nest AND and OR statements together? Or is it simply in combination with the numerous IF's I have that prevent me? Because when I go through the Edit Function method, it tells me that it's calculating the statement correctly; just when I hit enter does it tell me it's wrong.

Help please. Excel version from Office 2000 Professional. Cheers!

I have a rather large (11k+ rows) sheet with, well, too many columns, to
which I've added an auxiliary column. One column describes the type of
service (support) the row is related to, and is unfortunately text and fairly
inconsistent in certain areas. While I have some experience using the left,
mid and right functions, as well as find, I'm having some difficulty coming
across the syntax required to locate the first " ", and gather the
alphanumeric character that proceeds it. For instance, if a field reads
"Premium 3yr" or "Premium 3 yr", what would be the most efficient formula to
extract only the "3"? Please keep in mind that I need to be able to use the
formula in an existing one with many existing nested IF(and( statements.
Appreciate any help as always.

MJW

=IF(AND(R2="yes",S2="1"),(W2*0.65)-95,IF(AND(R2="yes",S2="2"),(W2*0.8)-95,IF(AND(R2="no",S2="1"),(W2*0.65)-70,IF(AND(R2="no",S2="2"),(W2*0.8)-70))))

The above nested "if" and "and" functions are used to calculate commission totals based on R2 (which tells whether we are capped or not), and S2 (which indicates what type of transaction occurred). These formulas used to work, returning dollar amounts. However, now all they return is "FALSE." I can't figure out why. R2 and S2 both have acceptable entries, as does W2. I can't figure it out, and am getting pretty frustrated, so I wanted to post the formula to see if anyone saw a mistake anywhere or had any advice/answers for me.

Thanks so much! I will very much appreciate it.

Hi,
need help
using function IF, AND, OR, NOT
the following is the description and attached is the photo of the excel file
please
thank you,

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.

I have a spreadsheet with the following headers:

Loan number (column A)
Grade (column B)
Days Past Due (column C)
Borrower (Column D)
Valuation Pool (column E)
Balance (Column F)

I need to sum the balance for each pool (A1, A2, B1, B2, C1, C2, D1, D2, E1, E2, F1, F2, G1, G2, H1, & H2) for a grade lower than five and where days past due is less than 60, and then again for grades five and above and days past due 60 and greater. However, the loans could be more than 60 days past due and still have a grade lower than five (meaning they shouldn't be included in the sum), so using a simple SUMIFS function won't work.

I'm think I need to nest an if, and, and or in a sum function to accomplish this. The best I can come up with at this point is:

{=SUM(IF(AND($E$2:$E$8312="A1",OR($B$2:$B$8312<5,$C$2:$C$8312<60)),$F$2:$F$8312))}

Which obviously isn't working because it's summing all of the balances and ignoring my criteria.

Thanks for your help.

I'm basically looking to total amounts by invoice number in order to take a percentage. However, some invoice numbers are divided between needing 3 & 5% taken. I need to adjust my formulas to total by invoice number but also to take into account percentage criteria. I'm using a SUMIF function nested within an IF statement. I've tried using some AND/OR's in there but nothing seems to be returning the correct figures.

I have attached a mock spreadsheet showing what the spreadsheet should ultimately look like and the formulas I am currently using that need tweaking.

Please help

Posts: 3

Re: Nested IF Functions Help
I hate to bother anyone again but I'm just not getting these functions. There is a part 2 of this problem, I'm supposed to enter a fourmula using a IF function to determine total family leave time using the AND OR functions

the totals for family leave are
5 days for full-time employees who have worked 1 or more years
3 days for full-time employees who have worked less than 1 year or for part-time employees who have worked more than 2 years
0 days for everyone else

Thanks-Joanna

I have the following formula which I believe does not breach any rules, yet
it returns an error message. Can anyone see where my error is? I would much
appreciate any help, and thank you in advance.
I don't believe I am breaching the max 7 arguments rule.

=if(OR
(AND(J44="GreenAbove",J43="GreenAbove",J42="RedBel ow"),
AND(J44="GreenAbove",J43="GreenBelow",J42="RedBelo w"),
AND(J44="GreenBelow",J43="GreenBelow",J42="GreenBe low",J41="RedBelow")),
C44,"")

Hi,

I need to create a function that can cope with more than 7 if ands, where it
can read what's in cell A1 and what's in cell B1 then spit out a defined
value in C1. For example,

If A1="a" and B1="a", then 1, but if A1="a" and B1="b", then 2 and so on for
combinations aa, ab, ac, ad, ae, ba, bb, bc..........ea, eb, ec, ed and ee.
My formula below works but only gets me up to bc.

=IF(AND(A1="a",B1="a"),"1",IF(AND(A1="a",B1="b")," 2",IF(AND(A1="a",B1="c"),"3",IF(AND(A1="a",B1="d") ,"3",IF(AND(A1="b",B1="a"),"2",IF(AND(A1="b",B1="b "),"2",IF(AND(A1="b",B1="c"),"3",)))))))

I've found some tricks to get around the 7-limit IF functions but can't seem
to make them work for my nest IF(AND) function.

I hope this makes sense. Any help would be most appreciated.

thanks.

Rohan.

I need help on the following problem:

I have 2 cells with numbers.
In a 3rd cell I want to create a formula which looks at the 2 data cells and shows a value. The rules are the following:
If C1 or C2 are bigger than Xthen C3=value1 else C3=value2
I have some basic excel knowledge but im not very familiar with functions, so please help me out on this one.
I'm using Excel 2007.

Thanks in advance!

Hi guys...
I want to use AND/OR functions along with IF. For example,
=IF(A2="JANUARY" AND B2="SUNDAY",1,0)
ie, if value of A2 is JANUARY and value of B2 is SUNDAY then some other cell(say C2) should populate value 1 else value 0.
Please help me on this.

Thanx in advance...

I am trying to find the largest value in a data set, once it is found I need "max" to appear beside the largest value in the adjacent column and min to appear beside the smallest value in the data set. I thought I could nest large and small with if function but it does not seem to be working. Any suggestions?
Thanks in advance

Can I mix the AND & OR functions like this.....
=IF(F5="","",IF(AND($D5<10,OR(F5>(F11+1),F5=0),F11+2,F5)) ???

I can't seem to get it to work. Is there a better way? I tried the another
way using all AND functions but I ended up with more than 7 nested IF
statements. I had 10 nested if and it wouldn't work. In that case there
were more arguements than you see above.

Thanks in advance.

Barney