|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
I have a commission statement that I need to set functions in.
I need to say if cell C30 is less than or equal to 20, then C31=$40, if C30
is between 21-40, then C31 is $60, if C30 is between 41-50, then C31 is $80,
and if C30 is greater than or equal to 51 then C31 is $100.
Please Help!!!
|
|
IF(C30<=20,40,IF(AND(C30>21,C30<40),60,IF(AND(C30>41,C30<50),80,IF(C31>=51,100)))
Note that the way you have your logic set up, this statement doesn't
consider valyes of 21, 40, 41, or 50. If you want those values to be
considered, then the formula should be re-written with >=21, <=40, etc.
Dave
--
Brevity is the soul of wit.
"Chris B." wrote:
[Quoted Text] > I have a commission statement that I need to set functions in.
>
> I need to say if cell C30 is less than or equal to 20, then C31=$40, if C30
> is between 21-40, then C31 is $60, if C30 is between 41-50, then C31 is $80,
> and if C30 is greater than or equal to 51 then C31 is $100.
>
> Please Help!!!
|
|
Thank you so much. This helped tremendously. I knew the basic concept but
couldn't figure out how to separate all of my variables.
Thanks Again
Christine
"Dave F" wrote:
[Quoted Text] > IF(C30<=20,40,IF(AND(C30>21,C30<40),60,IF(AND(C30>41,C30<50),80,IF(C31>=51,100)))
>
> Note that the way you have your logic set up, this statement doesn't
> consider valyes of 21, 40, 41, or 50. If you want those values to be
> considered, then the formula should be re-written with >=21, <=40, etc.
>
> Dave
> --
> Brevity is the soul of wit.
>
>
> "Chris B." wrote:
>
> > I have a commission statement that I need to set functions in.
> >
> > I need to say if cell C30 is less than or equal to 20, then C31=$40, if C30
> > is between 21-40, then C31 is $60, if C30 is between 41-50, then C31 is $80,
> > and if C30 is greater than or equal to 51 then C31 is $100.
> >
> > Please Help!!!
|
|
You can use
=CHOOSE(1+(C30>20)+(C30>40)+(C30>50),40,60,80,100)
or
=VLOOKUP(C30,{0,40;21,60;41,80;51,100},2)
I try to stay away from nested IFs when I can, but it's really a matter of
preference.
"Chris B." wrote:
[Quoted Text] > I have a commission statement that I need to set functions in.
>
> I need to say if cell C30 is less than or equal to 20, then C31=$40, if C30
> is between 21-40, then C31 is $60, if C30 is between 41-50, then C31 is $80,
> and if C30 is greater than or equal to 51 then C31 is $100.
>
> Please Help!!!
|
|
|