Need to include more than 7 IF statements

achbsu

New Member
Joined
Oct 8, 2006
Messages
12
LaPorte Salary Ordinance 2007.xls
QRSTUVWXYZAAAB
955I(100-245)23,57924,05124,53225,02325,52326,03326,55427,08527,62728,18028,744
956
957II(250-285)25,42325,93126,45026,97927,51928,06928,63029,20329,78730,38330,991
958
959III(290-325)26,89927,43727,98628,54629,11729,69930,29330,89931,51732,14732,790
960
961IV(330-380)28,37628,94429,52330,11330,71531,32931,95632,59533,24733,91234,590
962
963V(385-485)31,32831,95532,59433,24633,91134,58935,28135,98736,70737,44138,190
964
965VI(490&Above)34,27934,96535,66436,37737,10537,84738,60439,37640,16440,96741,786
966
967HireAfterAfterAfterAfterAfterAfterAfterAfterAfterAfter
968Date6Mos.3Yrs.6Yrs.9Yrs.12Yrs.15Yrs.18Yrs.21Yrs.24Yrs.27Yrs.
Sheet1
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

achbsu

New Member
Joined
Oct 8, 2006
Messages
12
Above table

Sorry, the table was too big include text in that message, so here's my question.

Each of the columns in this table represent a pay step, 0 - 10. There are several other tables like this for different job categories. I have one large spreadsheet that includes all of these tables. What I would like to do is to create a formula like so:

=IF(A1=0,R955,IF(A1=1,S955,IF(A1=2,T955,IF(A1=3,U955,IF(A1=4,V955,IF(A1=5,W955,IF(A1=6,X955,IF(A1=7,Y955,IF(A1=8,Z955,IF(A1=9,AA955,IF(A1=10,AB955))))))))))

This formula would apply to a job that is an "I"

I would then need to create separate formulas for each type of job, such as II, III, IV, V.... etc.

Obviously, with the above formula, I've exceeded my nested IF statement limit. Any advice on how to get this formula to work? I've tried to split the formula in two and define each element.... then combine them into one master.... but I can't get it to work properly. The master ends up looking like.... =IF("Name","Name2")

With so many different formulas and 11 IF statements per formula.... I'm letting lost.

Please advise. Thank you.
 

achbsu

New Member
Joined
Oct 8, 2006
Messages
12
Any thoughts?

I'm on a bit of a time crunch and would appreciate any thoughts at all. Thank you once again for your time.
 

JohnnyTightlips

Board Regular
Joined
Aug 13, 2006
Messages
94
Have you tried creating a VLOOKUP table? It seems quite appropriate for the example formula you provided. I may be missing something though

Good luck
 

achbsu

New Member
Joined
Oct 8, 2006
Messages
12

ADVERTISEMENT

Yes

The problem is that I think I'd need to create about 60 VLOOKUP tables as there are that many salary grades (data making up the rows). Once your are placed in a grade, you then fall on a step 0 - 10.

I've also tried to use the CONCATENATE function. I believe the following would be the function for grade V.

=IF(G11=0,R963)&IF(G11=1,S963)&IF(G11=2,T963)&IF(G11=3,U963)&IF(G11=4,V963)&IF(G11=5,W963)&IF(G11=6,X963)&IF(G11=7,Y963)&IF(G11=8,Z963)&IF(G11=9,AA963)&IF(G11=10,AB963)

However, when I enter this formula, I get a "FALSEFALSEFALSE...." and so on response.

Will this CONCATENATE function circumvent the 7 max IF statement problem?

Thanks for your assistance JohnnyTightLips.
 

Dufus

Board Regular
Joined
Aug 19, 2006
Messages
176

ADVERTISEMENT

achbsu,

I'd just add a header row above the pay step table (0,1,2,3, etc.) and use a SUMIF formula.

Dufus
 

jindon

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
Re: Above table

Hi
Not sure if this is what you are after..
=Indirect(Choose(A1+1,"R","S","T","U","V","W","X","Y","Z","AA","AB")&955)
 

achbsu

New Member
Joined
Oct 8, 2006
Messages
12
Thank you so much!

You nailed it jindon! You guys are life savers.... Keep up the good work!
 

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Here's an approach that uses HLOOKUP.

2 tables: The first, starting in A1, has the grades going down and Years of Service across.
Code:
Grade   0       0.5     3       6       9       12      15      18      21      24      27
I       23579   24051   24532   25023   25523   26033   26554   27085   27627   28180   28744
II      25423   25931   26450   26979   27519   28069   28630   29203   29787   30383   30991
III     26899   27437   27986   28546   29117   29699   30293   30899   31517   32147   32790
The range from B1 to the botttom right of the table is called PayRates.

The second table looks like this -- I started it in A12 for this sample.
Code:
Employee   Hire date  YOS          Grade  Row  Rate
Employee1  1/01/2002  4.769336071  II     2    26450
Employee2  1/01/2003  3.770020534  II     2    26450
Employee3  1/01/2004  2.770704997  II     2    25931
Employee4  1/01/2005  1.768651608  II     2    25931
Employee5  1/01/2006  0.769336071  II     2    25931
Employee6  1/07/2006  0.273785079  II     2    25423
Formulas:

C13 is =(TODAY()-B13)/365.25
E13 is =MATCH(D13,$A$2:$A$7,0)
F13 is =HLOOKUP(C13,PayRates,E13+1)

Fill down to suit.

Denis
 

Watch MrExcel Video

Forum statistics

Threads
1,114,051
Messages
5,545,727
Members
410,702
Latest member
clizama18
Top