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.

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

Keith

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: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.(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

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

Keith

- Error in Formula(use of nested if, and, or functions)
- I Need help with excel fuctions IF, AND, OR
- HELP!!! How to Nest AND OR functions PART II
- Help: Nested If/And Function formula to VB code
- HELP!!! How to Nest AND OR functions
- Nested IF,AND,OR function not cooperating
- How can I avoid large, nested IF AND OR queries
- Problem with nested IF and OR formula?
- Problem with nested IF and OR formula?
- If and Or Statement
- Nested, IF, AND, and OR function
- Nesting AND and OR function within an IF
- Use of Find with Left, Mid, Right functions in nested IF(and('s
- Nested "if" and "and" functions
- Nested functions IF, AND, OR, NOT
- Sum with nested if, and, or
- Nested IF AND OR with SUMIF
- The AND OR Functions
- Nested IF AND OR function Inexplicable error
- Nested if(and) functions
- IF and OR function combination
- How to use AND/OR function along with IF function..?
- Help with nesting if and large function
- Mixing AND & OR functions

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

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

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

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

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.

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

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

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

Thanks in advance!!!

Thanks Agoin

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!

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

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.

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.

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

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

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,"")

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

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

Thanks in advance

=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