Long nested IF statement

dc1TN

New member
Joined
Apr 25, 2012
Messages
4
Reaction score
0
Points
0
I need to do a nested IF statement that uses one table of rates with 11 rows to calculate against about 1000 rows of variables. The 11 rows are age-banded rates (<25, 25-29, 29-34, 35-39, etc.) and the 1000 rows are the ages of individual people. I need to multiply the appropriate rate for the specific age times the number of thousands in another column to develop a premium amount. I can get it to work with two IF statements but once I try to add the third, it fails.

To recap, colunn a has age, column b has number of thousands coumn n has age bands for rates and column o has the rates for the age band. I've tried everything I can think of and I've tried to find examples on the 'net but no luck so far. Is there a better way to do this than an IF statement?

Thanks so much!
 

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
A little confusing to follow.. can you post a sample workbook... with no confidential info?
 

dc1TN

New member
Joined
Apr 25, 2012
Messages
4
Reaction score
0
Points
0
I will try to post the spreadsheet example. This is my first post so I'm not quite sure how to do that. Hold on.......
 

dc1TN

New member
Joined
Apr 25, 2012
Messages
4
Reaction score
0
Points
0
Long nested IF statement - sample file

Here we go.
thx
 

Attachments

  • Imputed Income Calculation.xlsx
    9.5 KB · Views: 60

CheshireCat

New member
Joined
Dec 30, 2011
Messages
121
Reaction score
0
Points
0
Location
Victoria, Canada
Excel Version(s)
Microsoft Excel 2013
Hi,

In the green cells (column H) I added a numerical value to represent the age you have in column I of the sample sheet. In the yellow cells (column F) I use a VLOOKUP formula to find the IRS Rate, then multiply by the salary.

View attachment dc1TN 2012-04-25.xlsx

Cheers,
 

dc1TN

New member
Joined
Apr 25, 2012
Messages
4
Reaction score
0
Points
0
SHAZAAM! Thanks very much.

Be blessed
dc1TN
 
Top