if then else

[sjhc1177]

Hi I'm trying to create a commision statement for my employees.

I need to pay all employees that sell between 150 and 185 1%. Employees that sell over 185 would get 2%.

I need a formula not VBa.

Thanks,

 

[Gerald Higgins]

Assume sales figure is in A1

=if(A1>185,0.02,if(a1>150,0.01,0))

You might want to think about >= instead of >

 

[Richard Schollar]

Hi

For an employee that sells 200, would (s)he get:

1% of (185-150) plus 2% of (200-185)?

 

[Gerald Higgins]

I'm guessing that the "1%" is 1% of something completely different, like their monthly salary.

 

[sjhc1177]

Comission

Sorry I was in a rush and wasnt very clear.

I only want to pay comission on sales that are over $150. Sales between 150 and 185 would receive 1% sales over 185 will get 2%. Anything less that 150 will get no comisssion.

Thanks.

 

[Richard Schollar]

=MAX(0,A1-150)*1%+MAX(0,A1-185)*1%

Where A1 holds the sales made

 

[Gerald Higgins]

So I assume my suggestion does that for you ?

 

[sjhc1177]

Comission

The example you gave does not work the way I need it to.

Here is what I need:

Employee sales between 0 and 149 = 0%
Sales beteen 150 and 184 = 1%
Sales over 185 =2%

What I started with was an if then else.

Thanks,

 

[sjhc1177]

Comission

The example you gave does not work the way I need it to.

Here is what I need:

Employee sales between 0 and 149 = 0%
Sales beteen 150 and 184 = 1%
Sales over 185 =2%

What I started with was an if then else.

Thanks,

 

[Pauline V.]

Assume sales figure is in A1

=IF(A1<149;0;IF(AND(A1>=150;A1<=184);1%;2%))

Hope this helps,
Pauline