Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

multiple nested IF statements

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.


Post your answer or comment

comments powered by Disqus
I am doing a vlookup against another tab, where the range contains formulas. (A1/B1)

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.

Consider the provided spreadsheet. I had to calculate the range of data on 30 days intervals and had to use multiple nested if statements to write the function.

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

Kindly help.

Thanks,
booo

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.

I am having difficulty understanding how to formulate multiple nested IF
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 have a spreadsheet set up as follows:

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.

I am having difficulty understanding how to formulate multiple nested IF
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.

Hello Excel Gurus,

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.

I have racked my brain over this simple formula, or what should be a simple formula, for hours now and I cannot get it to produce the desired results. I would appreciate it if someone could look this over and tell me where my error is. I guess I have looked at it for so long I can't see the forest through the trees.

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

I was wondering if there's a function similar to switch statements in C++ / Java? For example, if I want to create an if statement with multiple scenarios, is there an easier way than to use nested IF statements?

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.

I've been using nested IF statements, with success for a while, but now I'm into my fourth nested statement I'm starting to get some problems.

=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

I want to have IF statements that have multiple conditions. Here is an example of what I want to do:

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!

I originally posted this attachment in Change a cell in a range and all other cells in the range change to keep the sum at 100% I don't think that this post should be part of the original because they are completely different problems. Moderators please let me know otherwise.

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!

I have a nested if statement where I have three columns. one of each column contains a number and the other two contain #N/A from a vlookup. I want to know which column contains a number.
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?

Hello,

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

Hello,

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!

I have run into the excel limit of nested if statements. I have a table of values that includes a range for payout based on performance.

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!

need a nested If statement. Have figured out basic If statements but nested ones are still giving me trouble.

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

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

	VB:
	
 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 Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
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).


	VB:
	
 File_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 Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
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 code

	VB:
	
 Enter_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 Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
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 help

Clive

I'm having trouble creating Named Formulas to handle nested IF & And statements, to get around excel's limit of 7 nested IF statements.

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

Please help me with an assignment! For a class, I have done several worksheets to create a bond amoritization schedule and am having trouble with nested if statements to place 0s in the cells after the bond is fully amoritized. I need a statement that will place 0 in the cells if there the carrying value in the above row equals the face amount of the bond or is the carrying value is 0.

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

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

Hi

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

We gather data at specific time points and I want to do an analysis using the most recent data. I have set up columns for each time point and then I have a formula using nested IF statements that work back from the last time point to the first until a non-blank cell is found and this is displayed as the most recent time point for that row.

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

How to you handle errors in nested IF statements such that if the first If statement results in a #VALUE, you can still proceed with evaluating subsequent nested statements?

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?


No luck finding an answer? You could always try Google.