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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
achbsu,

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

Dufus
 
Upvote 0
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)
 
Upvote 0
Thank you so much!

You nailed it jindon! You guys are life savers.... Keep up the good work!
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

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
Back
Top