Hi.

This may be simple, (and hope you understand what I am trying to do)

but I can't get it to work.

Basically, I am trying to determine if a person is eligible based on

hire or rehire dt but also may or may not be based on basic or nonbasic

dt. I have HIRE (A2), REHIRE (B2), BASIC (C2) and NONBASIC (D2) dt.

How do I set up nested IF statements to determine if HIRE or REHIRE dt

is < = than 1/4/05? If eligible, Yes. And if BASIC OR NONBASIC dt is

= >1/4/05, Check. Thanks.

This may be simple, (and hope you understand what I am trying to do)

but I can't get it to work.

Basically, I am trying to determine if a person is eligible based on

hire or rehire dt but also may or may not be based on basic or nonbasic

dt. I have HIRE (A2), REHIRE (B2), BASIC (C2) and NONBASIC (D2) dt.

How do I set up nested IF statements to determine if HIRE or REHIRE dt

is < = than 1/4/05? If eligible, Yes. And if BASIC OR NONBASIC dt is

= >1/4/05, Check. Thanks.

- Multiple nested if statements not workiing
- Alternative to multiple nested if statements
- Multiple nested IF statements
- HELP! Nesting IF Statements/Dependent Dropdown List
- Alternative to using multiple nested IF statements
- HELP! Nesting IF Statements/Dependent Dropdown List
- Nested IF statement to return a value based on multiple criteria
- Nested IF Statements
- Nested If statements ... best practices?
- Multiple conditions in nested IF statements
- Multiple Condition IF Statements
- Need an alternative to lots of nested IF statements
- Nested if statement with #N/A
- Problem using four AND rules in a Nested IF Statement
- Nested IF statements to calculate # of weeks based on various dates
- Nested IF statement limit
- Nested IF statement
- Nested If Statement Malfunctions
- Named Formula For Mega Nested If Statements
- Nested if Statements
- Numerical range inside nested IF statement
- How do I get around the limit of 7 nested IF statements ?
- Alternative to nested IF statements
- Error handling in nested IF statements

Because the results of that vlookup will be charted, I want the cell to display #N/A if the vlookup returns a 0 or if it returns an error message.

A nested IF statement with ISERROR returned the #N/A that I wanted for the cells where the formula returned Div!/0.

A nested IF statement checking to see if the VLOOKUP=0 returned the #N/A where the formula returned 0.

However, I have not been able to combine the two into one statement. I've tried both an OR statement and a double nested IF statement.

IF(ISERROR(VLOOKUP),#N/A,IF(VLOOKUP=0,#N/A,VLOOKUP)

and IF(OR(ISERROR(VLOOKUP),VLOOKUP=0),#N/A,VLOOKUP)

In each case, I get Div!/0 instead of #N/A.

This is not my 1st, 2nd, or even 50th effort at making a multiple nested IF statement, so it's driving me a bit nuts as to why I can make the individual statements work but not the combined statement. Any troubleshooting advice would be appreciated.

All though it works fine, i was wondering if there was an easier alternative to this method.

Kindly help.

Thanks,

booo

This may be simple, (and hope you understand what I am trying to do)

but I can't get it to work.

Basically, I am trying to determine if a person is eligible based on

hire or rehire dt but also may or may not be based on basic or nonbasic

dt. I have HIRE (A2), REHIRE (B2), BASIC (C2) and NONBASIC (D2) dt.

How do I set up nested IF statements to determine if HIRE or REHIRE dt

is < = than 1/4/05? If eligible, Yes. And if BASIC OR NONBASIC dt is

= >1/4/05, Check. Thanks.

statements in order to produce the precise result based on the conditions. My

first stumbling block is knowing where to place the parentheses to establish

the precedence of the conditions to be evaluated i.e. if the first

logical_test is FALSE, the second IF statement is evaluated, and so on.

..:Rationale:.

The purpose of this function is to display a value (i.e. string of text,

date/time, or NULL) in the adjacent cell when the user selects one of the

items in the list. In this case, the drop-down list resides in cell D4 and

generates a value in cell E4.

..:Version 1.0:.

In cell D4 there is a drop-down list containing 3 elements:

-Select Status (â€śSelect Statusâ€ť is default text that the use can see)

-Submitted

-Not Returned

..:Conditional Results Anticipated:.

If D4=â€śSelect Statusâ€ť, then E4=â€ťâ€ť

If D4="Submitted", then E4=display the date & time

If D4="Not Returnedâ€ť, Then E4=â€ť-â€ś

The IF statement below resides within the cell E4 and simply displays the

results based upon which item in the drop-down list in cell D4 was selected.

However, the problem with using the â€śNOW() or TODAY()â€ť function is that they

both continue to update and as found out is not static.

=IF(D4="Submitted",NOW(), IF(D4="Not Returned", "-", IF(D4="Select

Status","")))

..:Version 2.0:.

In version 2 I tried a work around by inserting a static date & time

manually by selecting F4 and hitting Ctrl+:, Ctrl+Shift+: and this made the

time stamp static, but to me, it contradicted the point of the function:

=IF(D4="Submitted",F4, IF(D4="Not Returned", "-", IF(D4="Select Status","")))

..:Version 3.0:.

I was able to convert the NOW() function result into text and only update if

E4=â€ťâ€ť (TRUE)

=IF(D4="Submitted",E4="",TEXT(NOW(),"mm/dd/yy h:mm AM/PM"))

I have tried to nest the IF statements with the remaining 2 items in the

drop-down list and can not get it to work. Here are a few examples I have

tried and donâ€™t work.

1. =IF(D4="Submitted",E4="",TEXT(NOW(),"mm/dd/yy hh:mm"), IF(D4="Not

Returned", "-", IF(D4="Select Status",""))))

2. =IF(AND(F4="", E4=""),E4='',TEXT(NOW(),"mm/dd/yy hh:mm")))

3. =IF(AND(D13="Submitted",D13"Select Status"),E13="",TEXT(NOW(),"mm/dd/yy

h:mm AM/PM"))

Please explain how to nest IF statements or provide any URLâ€™s etc, as I have

searched the web high & low and had no luck in finding any meaningful

information. Ohâ€¦donâ€™t forget to show me the right way to compile this

formula. Thanks.

Col A........Col B......Col C........Col D.....Col E.........Col F......Col G........Col H

Student....Grade....Student.....Grade....Student.....Grade....Student.....Grade....

Mike..........Pass......Tim..........Pass.......Joe............Fail.........Sue.........Pass

Mike..........Pass......Tim..........Pass

Mike..........Pass......Tim..........Fail

Mike..........Pass......Tim..........Pass.......Joe............Pass........Sue.........Pass

For each line in file, I need to determine if all students have passed the course. If all students have passed then I need to make a notation in another cell which indicates "OK". If one or more students have failed, then I need to make a notation of "Review". For example:

Line 1 = Review

Line 2 = Ok

Line 3 = Review

Line 4 = Ok

I am trying to avoid having to write some lengthy nested if statement to solve. In some cases, I may have as many as 20 students in one line and just not sure how to test if all have passed or at least one has failed. Thanks in advance for any comments.

statements in order to produce the precise result based on the conditions. My

first stumbling block is knowing where to place the parentheses to establish

the precedence of the conditions to be evaluated i.e. if the first

logical_test is FALSE, the second IF statement is evaluated, and so on.

..:Rationale:.

The purpose of this function is to display a value (i.e. string of text,

date/time, or NULL) in the adjacent cell when the user selects one of the

items in the list. In this case, the drop-down list resides in cell D4 and

generates a value in cell E4.

..:Version 1.0:.

In cell D4 there is a drop-down list containing 3 elements:

-Select Status (â€śSelect Statusâ€ť is default text that the use can see)

-Submitted

-Not Returned

..:Conditional Results Anticipated:.

If D4=â€śSelect Statusâ€ť, then E4=â€ťâ€ť

If D4="Submitted", then E4=display the date & time

If D4="Not Returnedâ€ť, Then E4=â€ť-â€ś

The IF statement below resides within the cell E4 and simply displays the

results based upon which item in the drop-down list in cell D4 was selected.

However, the problem with using the â€śNOW() or TODAY()â€ť function is that they

both continue to update and as found out is not static.

=IF(D4="Submitted",NOW(), IF(D4="Not Returned", "-", IF(D4="Select

Status","")))

..:Version 2.0:.

In version 2 I tried a work around by inserting a static date & time

manually by selecting F4 and hitting Ctrl+:, Ctrl+Shift+: and this made the

time stamp static, but to me, it contradicted the point of the function:

=IF(D4="Submitted",F4, IF(D4="Not Returned", "-", IF(D4="Select Status","")))

..:Version 3.0:.

I was able to convert the NOW() function result into text and only update if

E4=â€ťâ€ť (TRUE)

=IF(D4="Submitted",E4="",TEXT(NOW(),"mm/dd/yy h:mm AM/PM"))

I have tried to nest the IF statements with the remaining 2 items in the

drop-down list and can not get it to work. Here are a few examples I have

tried and donâ€™t work.

1. =IF(D4="Submitted",E4="",TEXT(NOW(),"mm/dd/yy hh:mm"), IF(D4="Not

Returned", "-", IF(D4="Select Status",""))))

2. =IF(AND(F4="", E4=""),E4='',TEXT(NOW(),"mm/dd/yy hh:mm")))

3. =IF(AND(D13="Submitted",D13<>"Select Status"),E13="",TEXT(NOW(),"mm/dd/yy

h:mm AM/PM"))

Please explain how to nest IF statements or provide any URLâ€™s etc, as I have

searched the web high & low and had no luck in finding any meaningful

information. Ohâ€¦donâ€™t forget to show me the right way to compile this

formula. Thanks.

I am trying to return a value based on multiple criteria using a nested IF statement. Basically, I have a table that contains costs for a direct mail campaign, and I am trying to return the cost of the program based on the values that can be selected in two separate cells which both have drop-down lists. There are 20 different possible values that can be returned based on the possible combination of selections in those two separate cells.

If you look at the attached spreadsheet, the cell that I am trying to return the costs to is cell B4 (Total Program Costs). The possible values that can be returned are all in column G.

The statement should return values based on two criteria:

1) It should compare the value of B2 with the values in Row F

2) It should also compare the value of B3 and compare with the values in Row A.

It should then return the value based on the way the table is set up.

So, if a user selects "Long Mailer" in cell B3, and 100,000 in B2, then cell B4 should return a value of $43,300.

If a user selects "Jumbo Postcard" in cell B3, and 25,000 in B2, then cell B4 should return a value of $11,500.

I attempted to solve the problem using a really long nested IF statement using the AND command as well. I got an error message in Excel telling me that I had exceeded the max amount of levels in a nested statement. I am assuming there is a more elegant way to do this, but I cannot figure it out.

Help!

Thanks.

Here is my scenario: I have multiple sheets One which is named MAIN. I would like to get information from certain cells on the MAIN sheet and combine the data from those cells into one cell on the new sheet. The columns that the data resides in on the MAIN are G, H, & I. All 3 columns are used with each row of data. The data appears in one of 3 ways. Column G Alone. Column G and I or in columns G, H and I. When more than one column has information I want to seperate the data with a - (hyphen) and don't want a bunch of errant hyphens floating around hence the reason for the nested IF statements. It appears that after reading the first IF statement that answer is returned and I get nothing else. Below is my formula and input would be greatly appreciated so I can finsih this project and move onto something else. Thanks in advance.

=IF(AND(MAIN.H5="";MAIN.I5="");CONCATENATE(MAIN.G5));

IF(NOT(OR(MAIN.H6="";MAIN.I6=""));CONCATENATE(MAIN.G6;"-";MAIN.H6;"-";MAIN.I6);

CONCATENATE(MAIN.G6;"-";MAIN.I6))

The situation I'm trying to model is very simple, I'm just not sure what the excel syntax would be:

Case 1) IF (formula 1) = "NA" AND (formula 2) = "NA" --> (formula 3)

Case 2) IF (formula 1) = "NA" AND (forumla 2) != "NA" --> (formula 2)

ELSE --> (formula 1)

Any help here would be much appreciated.

=IF($P15>0,SUM(IF(APR09!$M$2:$M$500=Department!$M$1,IF(APR09!$B$2:$B$500=$Q$6,IF((AND(APR09!$G$2:$G$ 500<>Department!$N$29,APR09!$G$2:$G$500<>Department!$N$30)),1,0),$N$2))))

Is what I've come up with so far. However, this returns 0, even though there are 3 items which should resolve the criteria.

The main problem is here: IF((AND(APR09!$G$2:$G$500<>Department!$N$29,APR09!$G$2:$G$500<>Department!$N$30))

Where I am trying to make the sum increase by 1 if the cell from the row in question, having made it through the first 3 IF statements is not equal to either of those two cells (a range would be better) then the Sum needs to be increased by 1.

Is there any way of doing this?

Thanks

A1 = 5

A2 = 10

A3 = A2 - A1

Now I want to replace A3 with an IF statement that does the following:

-If A1 is empty or equal to zero, A3 = " " (blank)

-If A2 is empty or equal to zero, A3 = " " (blank)

-If both A1 and A2 are empty or equal to zero, A3 = " " (blank)

-If both A1 and A2 contain values, A3 = A2 - A1

Now I've managed to do this for one cell...like if A1 is empty, etc. with this formula:

IF(A1=0," ",A2-A1)

But I can't figure out how to do 2 conditions together. I tried using nested IF statements, and the AND function, but all of those give various problems.

Later on, I'll also have functions I want to do this with that have more than 2 conditions. Can someone show me a way to do this sort of thing for multiple conditions please? Thanks!

While only one of the issues I was trying to accomplish at the time was addressed in the previous post, that of keeping the sum of a range of cells always equal to 100%, I ended up not using the suggestion and figured that part out on my own because I needed the percentages to be top heavy so the payout would get better the better a team placed.

The part I'm having a problem with now is dealing with the end of league place standings in the case of a tie between two or three teams and with the possibility of there being more than two teams for any given place and/or more than two teams tied for different placings. I started to figure it out (I thought) but realized that each placing down in the standings, 1st to 2nd to 3rd, etc., would require an increasingly longer nested IF statement to be made and it's unbelievably confusing. See the example below of just 1st through 4th.

1 1

2 IF(D22=D23,1,2)

3 IF(AND(D22>D23,D23>D24),3,IF(AND(D22>D23,D23=D24),2,IF(AND(D22=D23,D23=D24),1,2)))

4 IF(AND(D22>D23,D23>D24,D24>D25),4,IF(AND(D22=D23,D23>D24,D24>D25),3,IF(AND(D23>D24,D24=D25),3,IF(AND (D23=D24,D24=D25),2,IF(AND(D22=D23,D23=D24),2,3)))))

By the time I get to 24th place this statement will be about 10 miles long.

Open the attachment and start messing around with the "Total Games Won This Season" column (D22:D45) by changing the scores to and from ties. I gave up at 4th place as shown above. Is there an easier way to make it so the "Place (with ties)" column (B22:B45) will automatically change the place numbers to accommodate for teams being tied at the end for multiple places and the possibility of more than just two teams tied for any specific placing?

Let me know if you need more explanation.

Thanks!

A B C D

1 15 #N/A #N/A

2 #N/A 17 #N/A

=IF(AND(ISNA(J27),J27>=0),"A",IF(L27>=0,"B",IF(K27>=0,"C","None")))

is that I have in column D and is not working for D2. Probably because A2 is #N/A. Any ideas on how to fix this?

I have four AND rules as follows that formed part of a Nested IF function. When I use the first two only included in a nested IF Statement it works. And when I use the last two only in a Nested IF it works.

But when I try to include all four AND rules in a Nested IF function it does not work. Is there another way of doing this ? I have attached the spreadsheet that I am using and the cells where I have tried to fill down this formula.

The AND rules I am using are as follows (in cell A28 in this case, ready for a fill up or down the column) :

1. AND(B28="Yes",VALUE(TRIM(MID(D25,7,3)))>70,MID(D26,4,2)="of",VALUE(TRIM(MID(D27,7,3)))

I am trying to write a function that calculates the number of weeks between two dates based on several conditions. The function I wrote does not work for all the condtions and returns impossible values:

=if(AA80,if(f80,if(E8>AJ8,round(days360(E8,AH8)/7,0),round(days360(AJ8,AH8)/7,0)),if(E8>AJ8,round(days360(E8,AH8)/7,0),round(days360(AJ8,AH8)/7,0))),if(f80,if(E8>AJ8,round(days360(AJ8,AA8)/7,0),round(days360(E8,AA8)/7,0)),if(E8>AJ8,round(days360(E8,F8)/7,0),round(days360(AJ8,F8)/7,0))))

There are only seven nested If statements, but should I try using the CONCATENATE function for this?

Here is an example for one row:

For AA8 = 12/04/04, F8 = 0, E8 = 04/01/65, AA = 12/04/04, AJ8 = 10/04/04, and AH8 = 10/02/05, the function should return 9 weeks. Instead it return 51. AH and AJ are constant dates representing the ending and beginning of the fiscal year respectively.

I am not familiar with writing VBA code. I would be very appreciative if anyone could give me any advise on how I might get this to work.

Thank you!

The first 2 column are the high and low limits. The 3rd column is the payout percent. There are 10 levels of payout hence the nested if statement limitation. Any ideas?

I have attached a sample of the table. Any ideas on how I can get the C column based on where a value falls in the range?

Thanks!

I have a meal allowance of 7.00 and want to apply it to how much the person actually spends on a meal. if they spend below the amount i want a negative amount, above I want a positive amount. and if equal to I want the full amount entered.

At present I have a statement that will report the negative and positive but enters No value if it is equal to the meal allowance.

basically i need to poor HR person to know how much they need to pay or deduct from the person travelling.

Here is the formula i am currently using.

=IF(OR(G80,G8),G8-$C$25,)

g8 is the breakfast field.

c25 is the breakfast meal allowance of 8 dollars.

currently i have a meal allowance set of breakfast is 5 dollars lunch is 12dollars and dinner is 15 dollars

currently i have g8 as the breakfast field in which the person enters the amount they spent.

I use g9 for this formula.

any help would be appreciated.

Marty

I have the following nested if statement that works very well which is designed to ensure data is entered in a specified format.

VB:The entered data is used later on to name the workbook. I use the following code to check if the file name already exists (provided by Dave Hawley, thanks Dave).Enter_Batch_Number() Dim ans As String Err_Invalid: ans = Application.InputBox("Please Enter Batch Number," & vbNewLine & "Batch Number Must Be One Letter & 4 Digits. e.g A9999") Range("D1") = ans ActiveSheet.Range("D1").Value = (UCase(ActiveSheet.Range("D1").Value)) If Range("D1") = False Then MsgBox "Batch Number Must be Entered", vbCritical + vbOKOnly, "Invalid entry" Goto Err_Invalid ElseIf IsNumeric(Left(ans, 1)) Then MsgBox "Please enter again, using 1 letter & four digits", vbCritical + vbOKOnly, "Invalid entry" Goto Err_Invalid ElseIf Len(ans) 5 Then MsgBox "Please enter again, your entry is either too short or too long.", vbCritical + vbOKOnly, "Invalid entry" Goto Err_Invalid End If End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

VB:My problem (eventually I here you say) is that once the file has been identifed as already existing what ever I do to prevent the code continuing, file exists code doesnt work. I have tried Do While, Loops and the following codeFile_Exists() Dim batch As String Dim folder As String Dim Name As String batch = Sheets("Day 1").Range("D1") folder = "C:CR Retention" Name = folder & batch file = Name & ".xls" If Dir(file) "" Then 'CHECKS IF FILENAME ALREADY EXISTS MsgBox "File already exists.", vbCritical + vbOKOnly, "Invalid entry" End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

VB:this then says that the file exists even when i know it does not. I'm sure as usual I'm missing something obvious but cant find it yet, thanks for any helpEnter_Batch_Number() Dim ans As String Dim batch As String Dim folder As String Dim Name As String batch = Sheets("Day 1").Range("D1") folder = "C:CR Retention" Name = folder & batch file = Name & ".xls" Err_Invalid: ans = Application.InputBox("Please Enter Batch Number," & vbNewLine & "Batch Number Must Be One Letter & 4 Digits. e.g A9999") Range("D1") = ans If Range("D1") = False Then MsgBox "Batch Number Must be Entered", vbCritical + vbOKOnly, "Invalid entry" Goto Err_Invalid ElseIf IsNumeric(Left(ans, 1)) Then MsgBox "Please enter again, using 1 letter & four digits", vbCritical + vbOKOnly, "Invalid entry" Goto Err_Invalid ElseIf Len(ans) 5 Then MsgBox "Please enter again, your entry is either too short or too long.", vbCritical + vbOKOnly, "Invalid entry" Goto Err_Invalid ElseIf Dir(file) "" Then 'CHECKS IF FILENAME ALREADY EXISTS MsgBox "File exists.", vbCritical + vbOKOnly, "Invalid entry" Goto Err_Invalid End If End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines

Clive

First of all, is it possible to create a Named Formula with combined IF with AND statement such as: =IF(AND(F26>=54,F26=54,F26=78,F26=108,$F26=138.01,$F26=168.01,$F30=198.01,$F30=234.01,$F30=270.01,$F30=320.01,$F30

I have tried this statement and can not get it to work:

=IF(E26=Calculations!$C$3,IF(E26=0),0,E26*$B$5/$B$7)

I tried an OR statement and could not get that to work either.

Thank you,

Karen

is slowly getting used to nested IF statements..

have one that I am having difficulty with.

cell c3 has an amount of a fine.

cell f3 needs to calculate the c3 amount according to the following rules.

if c3 is in a range from 4 to 25 then return 3

if c3 is in a range from 26 to 50 return 4

if c3 is in a range from 51 to 100 return 5

if c3 is 101 and above calculate 5 percent of c3.

i cant remember if you can/how to do a range within a statement.

The company I work for uses a bespoke database system for recording clients recurring invoice values and an invoice frequency and next invoice date. For each recurring revenue (maintenance) run an invoice is produced and the database is automatically updated with the next invoice date based on the invoice frequency value. Invoice frequency is a number from 12 to 1 i.e 12 means invoice every 12 months and 1 means invoice every month so quarterly invoicing would require an invoice frequency of 3 i.e invoice every 3 months.

I need to display this detail in Excel to show a recurring revenue forecast with clients down column A and Columns B to L to show revenue for January to December. I'm O.K until I get to July but then hit the limit of 7 nested IF statements. I am obviously placing the next invoice value in the cell depending on what the invoice frequency for the client is.

Any ideas ?

Regards

Andy

I have been trying to find an alternative to using nested IF statements to select the most recent data as they can get confusing and then you hit the 7 limit...

I have attached a spreadsheet to demonstrate what I'm trying to do.

Thanks

Tim

My formula is somehting like this:

=IF(AP4>0,"APPLE",IF(AP5>0,"ORANGE","TBC"))

I am trying to evaluate text string in a cell to determine whether that string contains "APPLE" or "ORANGE", but if it doesnt contain "APLE" being the first expression I cant get it to proceed to the next ststement?

There must be an elegant way of doing this - can anyone help?