if command with tiers

J

JennaOG

I have 10 levels of tiers I am trying to use an if then command with, I keep
getting an error after the 9th line. As I am entering the 10th if command
into the function, it stops highlighting the links and keeps sending me back
to the last if command in the formula. I hope this doesn't sound too
confusing...I am so fried from trying to figure this out.

Here is my formula:
=if(MTAAB>B22,D23,if(MTAAB>B23>D24,if(MTAAB>B24,D25,if(MTAAB>B25,D26,if(MTAAB>B26,D27,if(MTAAB>B27,D28,if(MTAAB>B28,D29,if(MTAAB>B29,D30,if(MTAAB>B30,D31))))))))'


Mtaab is just a named cell.

Ticket Average % Potential Pay Out
$- $13.49 0.00% $-
$13.50 $15.49 2.50% $1,901.02
$15.50 $16.49 3.50% $2,661.43
$16.50 $17.49 4.50% $3,421.84
$17.50 $18.49 5.50% $4,182.25
$18.50 $19.49 6.50% $4,942.65
$19.50 $20.49 7.50% $5,703.06
$20.50 $21.49 8.50% $6,463.47
$21.50 $22.49 9.50% $7,223.88
$22.50 + 10.50% $7,984.29

The end result needs to be the "potential pay out."

Please help me!

Thanks!

Jenna
 
G

Glenn

JennaOG said:
I have 10 levels of tiers I am trying to use an if then command with, I keep
getting an error after the 9th line. As I am entering the 10th if command
into the function, it stops highlighting the links and keeps sending me back
to the last if command in the formula. I hope this doesn't sound too
confusing...I am so fried from trying to figure this out.

Here is my formula:
=if(MTAAB>B22,D23,if(MTAAB>B23>D24,if(MTAAB>B24,D25,if(MTAAB>B25,D26,if(MTAAB>B26,D27,if(MTAAB>B27,D28,if(MTAAB>B28,D29,if(MTAAB>B29,D30,if(MTAAB>B30,D31))))))))'


Mtaab is just a named cell.

Ticket Average % Potential Pay Out
$- $13.49 0.00% $-
$13.50 $15.49 2.50% $1,901.02
$15.50 $16.49 3.50% $2,661.43
$16.50 $17.49 4.50% $3,421.84
$17.50 $18.49 5.50% $4,182.25
$18.50 $19.49 6.50% $4,942.65
$19.50 $20.49 7.50% $5,703.06
$20.50 $21.49 8.50% $6,463.47
$21.50 $22.49 9.50% $7,223.88
$22.50 + 10.50% $7,984.29

The end result needs to be the "potential pay out."

Please help me!

Thanks!

Jenna
Click to expand...

Depending upon the version of Excel you are using, there is a limit of 7 nested
IF() functions.

Look at this:

http://www.contextures.com/xlFunctions03.html
 
M

MyVeryOwnSelf

I have 10 levels of tiers I am trying to use an if then command with,
I keep getting an error after the 9th line. ...

Ticket Average % Potential Pay Out
$- $13.49 0.00% $-
$13.50 $15.49 2.50% $1,901.02
$15.50 $16.49 3.50% $2,661.43
$16.50 $17.49 4.50% $3,421.84
$17.50 $18.49 5.50% $4,182.25
$18.50 $19.49 6.50% $4,942.65
$19.50 $20.49 7.50% $5,703.06
$20.50 $21.49 8.50% $6,463.47
$21.50 $22.49 9.50% $7,223.88
$22.50 + 10.50% $7,984.29

The end result needs to be the "potential pay out."

Here is my formula:
=if(MTAAB>B22, ...
Mtaab is just a named cell.
Click to expand...

Another way to approach the problem is something like this:
=VLOOKUP(MTAAB,A:D,4,TRUE)
 
Top