Requesting immediate response please! Nested IF statements.

achbsu

New Member
Joined
Oct 8, 2006
Messages
12
Hello all. I'm a first time poster and an Excel novice. I apologize if this question has been asked previously, but here goes:

As I understand it, Excel will not allow you to nest more than 7 IF statements in one formula. But, I've also heard that there are ways to circumvent this rule, including the VLOOKUP function, using defined names, and the CONCATENATE function.

I've tried to use these alternatives, but have yet to find one that either applies to my situation or that I can get to work properly. My situation is as follows:

I have created a spreadsheet for a client that lists employees and hire dates. Hire dates have then been assigned values. I'm attempting to create a formula that indicates that if the employee was hired between Aug 1, 1999 and Aug 1, 2002, he or she will be paid at level 1, Aug 2, 2002 to Aug 1 2005, level 2...and so on. So, one column contains the date values and a second contains the formula to assign the levels, 1 - 10. These levels will then end up corresponding to a salary hidden in a cell off to the side of the spreadsheet.

Lets say the hire values run from 1 to 40,000. I believe the formula should read something like the following, with F1 referring to the cell the formula will be placed in:

=IF(AND(F1>1,F1<=4000,1,IF(AND(F1>4000,F1<=8000,2,IF(AND(F1>8000,F1<=12000,3,IF(AND(F1>12000,F1<=16000,4,IF(AND(F1>16000,F1<=20000,5,IF(AND(F1>20000,F1<=24000,6,IF(AND(F1>24000,F1<=28000,7,IF(AND(F1>28000,F1<=32000,8,IF(AND(F1>32000,F1<=36000,9,IF(AND(F1>36000,10))))))))))

As you can see, this becomes very confusing. The FALSE statement becomes the next IF statement, rather than 0, for example. If anyone can make sense of this and can provide any guidance, I would greatly appreciate it. Please post an example of a correct formula in your response.

Thank you very much for your time.

Aaron
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

achbsu

New Member
Joined
Oct 8, 2006
Messages
12
I apologize

I misstyped. F1 refers to the cell containing the hire value. Thank you once again and let me know if I need to clarify this.

Aaron
 

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
try this
Book1
ABCDE
1Compare:40000
2Results:10
3
4
5
6
7LOOKUPS
801
940012
1080013
11120014
12160015
13200016
14240017
15280018
16320019
173600110
18
Sheet1


HTH
 

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
Hi,

Does this do what you're after:
Book1
ABCDE
111ValueLevel
24001240012
380013
4120014
5160015
6200016
7240017
8280018
9320019
103600110
Sheet2
 

achbsu

New Member
Joined
Oct 8, 2006
Messages
12

ADVERTISEMENT

VLOOKUP

I'm sorry, but I'm not familiar with the VLOOKUP function.

Here's a sample:

HIRE DATE HIRE DATE STEP
VALUE

01-Jan-94 34335 ?
30-Jul-90 33084
03-Oct-94 34610
17-Sep-90 33133
31-Jan-94 34365
15-Sep-86 31670
20-May-91 33378
09-Sep-96 35317
09-Apr-84 30781
23-Sep-96 35331

I'm looking to fill in the STEP column. Each step will correspond with a HIRE DATE VALUE range. Does the VLOOKUP function allow for ranges? Please explain how I would use the VLOOKUP function for this task. Meaning, what would my formula look like as entered where the question mark is?

Thank you once again.

Aaron
 

achbsu

New Member
Joined
Oct 8, 2006
Messages
12
Sorry

Columns are off...

Column 1 = HIRE DATE

Column 2 = HIRE DATE VALUE

Column 3 = STEP
 

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458

ADVERTISEMENT

Book1
ABCDEFG
2
3
4Hire DateHire Date ValueStep
51-Jan-94343359
630-Jul-90330849
7LOOKUPS3-Oct-94346109
80117-Sep-90331339
94001231-Jan-94343659
108001315-Sep-86316708
1112001420-May-91333789
121600159-Sep-96353179
132000169-Apr-84307818
1424001723-Sep-96353319
15280018
16320019
173600110
18
Sheet1
 

achbsu

New Member
Joined
Oct 8, 2006
Messages
12
Thank you Texasalynn

I appreciate your prompt response.

One final question. What does the "2" represent in the VLOOKUP formula you posted?

Thanks again!
 

texasalynn

Well-known Member
Joined
May 19, 2002
Messages
8,458
Take a look at the help examples
but the 2 is the column you want to return
 

Watch MrExcel Video

Forum statistics

Threads
1,114,407
Messages
5,547,774
Members
410,811
Latest member
adustin42
Top