Commission Calculations

[claire]

Hi

I need a formula (or anything!) that will either return
the value for commission or the percentage based on year
to date sales.

Commission rate is

<220000 4%

220001 <440000 6%
440001 <550000 9%
550001 10%

Thanks

PS can I get ISERROR to return a value of 3?

 

[Jeff Jacobson]

-----Original Message-----
Hi

I need a formula (or anything!) that will either return
the value for commission or the percentage based on year
to date sales.

Commission rate is

<220000 4%

Thanks

PS can I get ISERROR to return a value of 3?
.

Try this:
=IF(A1<220000,0.04,IF(A1<440000,0.06,IF(A1<550000,0.09,IF
(A1>54999,0.1,"N/A"))))
Where A1 is your first value.

 

[claire]

Thanks for that I was trying to make it more complicated
then it needed to be! I do have one further question -
how can I stop returning a value of 4% when blank?

 

[claire]

Sorted thanks for the help

-----Original Message-----
Thanks for that I was trying to make it more complicated
then it needed to be! I do have one further question -
how can I stop returning a value of 4% when blank?
.

 

[Van]

Try this,

=IF(AND(A1<220000,A1<>""),0.04,IF(AND
(A1<440000,A1<>""),0.06,IF(AND(A1<550000,A1<>""),0.09,IF
(AND(A1>549999,A1<>""),0.1,"N/A"))))

Van