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
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,033
Messages
5,856,941
Members
431,841
Latest member
jaybeem

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top