Troubleshooting my IF formula

My formula below is working perfectly...EXCEPT, I want to add one more IF to
the end, I want to add IF(C17="12x", 6%,""....but it won't let me add this.
Am I limited to the number of IF's in one formula? Also - can I use just the
number 1, rather than 1x, 2x, 3x...? When I use just the 1, 2 or 3 by itself
it gives me a blank. I just can't figure it out - I greately appreciate your
help!!!!!!!!!!!!!!!!!

=IF(C17="1x",0%,IF(C17="2x",0%,IF(C17="3x",0%,IF(C17="4x",2%,IF(C17="6x",4%,IF(C17="9x",6%,IF(C17="10x",6%,IF(C17="11x",6%,""))))))))

Kim, excel does not support more than 7 if's

hth
regards from Brazil
Marcelo

"Kim" escreveu:

> My formula below is working perfectly...EXCEPT, I want to add one more IF to
> the end, I want to add IF(C17="12x", 6%,""....but it won't let me add this.
> Am I limited to the number of IF's in one formula? Also - can I use just the
> number 1, rather than 1x, 2x, 3x...? When I use just the 1, 2 or 3 by itself
> it gives me a blank. I just can't figure it out - I greately appreciate your
> help!!!!!!!!!!!!!!!!!
>
> =IF(C17="1x",0%,IF(C17="2x",0%,IF(C17="3x",0%,IF(C17="4x",2%,IF(C17="6x",4%,IF(C17="9x",6%,IF(C17="10x",6%,IF(C17="11x",6%,""))))))))
>
>

Thanks, can you recommend a different type of formula that I should learn if
I need to use more than 7 if's in a single formula?

"Marcelo" wrote:

> Kim, excel does not support more than 7 if's
>
> hth
> regards from Brazil
> Marcelo
>
> "Kim" escreveu:
>
> > My formula below is working perfectly...EXCEPT, I want to add one more IF to
> > the end, I want to add IF(C17="12x", 6%,""....but it won't let me add this.
> > Am I limited to the number of IF's in one formula? Also - can I use just the
> > number 1, rather than 1x, 2x, 3x...? When I use just the 1, 2 or 3 by itself
> > it gives me a blank. I just can't figure it out - I greately appreciate your
> > help!!!!!!!!!!!!!!!!!
> >
> > =IF(C17="1x",0%,IF(C17="2x",0%,IF(C17="3x",0%,IF(C17="4x",2%,IF(C17="6x",4%,IF(C17="9x",6%,IF(C17="10x",6%,IF(C17="11x",6%,""))))))))
> >
> >

Kim,
Take a look at the VLOOKUP function.

"Kim" wrote:

> Thanks, can you recommend a different type of formula that I should learn if
> I need to use more than 7 if's in a single formula?
>
> "Marcelo" wrote:
>
> > Kim, excel does not support more than 7 if's
> >
> > hth
> > regards from Brazil
> > Marcelo
> >
> > "Kim" escreveu:
> >
> > > My formula below is working perfectly...EXCEPT, I want to add one more IF to
> > > the end, I want to add IF(C17="12x", 6%,""....but it won't let me add this.
> > > Am I limited to the number of IF's in one formula? Also - can I use just the
> > > number 1, rather than 1x, 2x, 3x...? When I use just the 1, 2 or 3 by itself
> > > it gives me a blank. I just can't figure it out - I greately appreciate your
> > > help!!!!!!!!!!!!!!!!!
> > >
> > > =IF(C17="1x",0%,IF(C17="2x",0%,IF(C17="3x",0%,IF(C17="4x",2%,IF(C17="6x",4%,IF(C17="9x",6%,IF(C17="10x",6%,IF(C17="11x",6%,""))))))))
> > >
> > >

You are limited to 7 IF Statements nested together in a single cell.

You are returning the same results for 1x, 2x and 3x (0%). Also, 9x, 10x,
11x, 12x (6%). You can combine these in an OR function:

=IF(OR(C17={"1x","2x","3x"}),0%,IF(C17="4x",2%,IF(C17="6x",4%,IF(OR(C17={"9x","10x","11x","12x"}),6%,""))))

If you want to use just 1, 2, 3... instead of 1x, 2x, 3x... make sure that
C17 contains just those numbers, then change your formula accordingly.

HTH,
Elkar


"Kim" wrote:

> My formula below is working perfectly...EXCEPT, I want to add one more IF to
> the end, I want to add IF(C17="12x", 6%,""....but it won't let me add this.
> Am I limited to the number of IF's in one formula? Also - can I use just the
> number 1, rather than 1x, 2x, 3x...? When I use just the 1, 2 or 3 by itself
> it gives me a blank. I just can't figure it out - I greately appreciate your
> help!!!!!!!!!!!!!!!!!
>
> =IF(C17="1x",0%,IF(C17="2x",0%,IF(C17="3x",0%,IF(C17="4x",2%,IF(C17="6x",4%,IF(C17="9x",6%,IF(C17="10x",6%,IF(C17="11x",6%,""))))))))
>
>

When you use just 1, 2, or 3 by itself in C17, is your formula C17="1" or
C17=1 ? You should use C17=1 because 1, 2, and 3 are numbers. C17="1" would
only work when you preformat C17 to text before entering the number, or if
you typed '1 (apostrophe before 1) in C17.

-Simon

"Kim" wrote:

> My formula below is working perfectly...EXCEPT, I want to add one more IF to
> the end, I want to add IF(C17="12x", 6%,""....but it won't let me add this.
> Am I limited to the number of IF's in one formula? Also - can I use just the
> number 1, rather than 1x, 2x, 3x...? When I use just the 1, 2 or 3 by itself
> it gives me a blank. I just can't figure it out - I greately appreciate your
> help!!!!!!!!!!!!!!!!!
>
> =IF(C17="1x",0%,IF(C17="2x",0%,IF(C17="3x",0%,IF(C17="4x",2%,IF(C17="6x",4%,IF(C17="9x",6%,IF(C17="10x",6%,IF(C17="11x",6%,""))))))))
>
>