How would I calculate commisions (with a cap)?
 

I Work in an office that pays by commision. I get 30% until 10,000 dollars
have been paid. At that point, I get 90%. How would I write this in a
formula? I track each month. on a different row And the 10,000 cap is reset
each year.

My Gross Income is in Col G and I want to have the net income in col L

Thanks
Mc

a formula of the form:


=if(g1<10000,g1*.3,g1*.9)


should do the trick - you can replace the 20000 with a
reference to make it variable...

Thanks Ben,
But this would not quite work as it only looks at that months commission. I
need to see if 10000 has been paid, and then if so, then it is 90%,
otherwise, I need to deduct 70% as operating cost, and then once 10,000 has
been paid, then change percentage to 90%. So the back half is correct, I
just need to figure out the "test". Also, if say 9000 has been paid, and
this sale would bump the paid commission to being OVER 10,000, then only
deduct UPTO 10,000. Maybe I oversiplified it in original post:.(


Would it be easier to store the amount of commission already paid, and then
check that? (I need to keep track of it anyways... This is for working up a
budget as well)
Again, once 10000 has been paid during a given _year_ (accumulative), then
my commission bumps up to 90%

tia,
Mc

"Ben McBen" wrote in message
...
a formula of the form:


=if(g1<10000,g1*.3,g1*.9)


should do the trick - you can replace the 20000 with a
reference to make it variable...

Take a look he

http://www.mcgimpsey.com/excel/variablerate.html

If you have only the two rates, put total paid in A1, say, then use
something like:

=SUMPRODUCT(--(A1{0,10000}),(A1-{0,10000}),{0.1,0.8})


See the link for more flexible methods.




In article <ynjvd.753530$8_6.116095@attbi_s04,
"Mctabish" <mc@ <nospamjohnsonclan.net wrote:

Thanks Ben,
But this would not quite work as it only looks at that months commission. I
need to see if 10000 has been paid, and then if so, then it is 90%,
otherwise, I need to deduct 70% as operating cost, and then once 10,000 has
been paid, then change percentage to 90%. So the back half is correct, I
just need to figure out the "test". Also, if say 9000 has been paid, and
this sale would bump the paid commission to being OVER 10,000, then only
deduct UPTO 10,000. Maybe I oversiplified it in original post:.(


Would it be easier to store the amount of commission already paid, and then
check that? (I need to keep track of it anyways... This is for working up a
budget as well)
Again, once 10000 has been paid during a given _year_ (accumulative), then
my commission bumps up to 90%

tia,
Mc

"Ben McBen" wrote in message
...
a formula of the form:


=if(g1<10000,g1*.3,g1*.9)


should do the trick - you can replace the 20000 with a
reference to make it variable...

When I visited your site, I got a virus, and now I can not get rid of
IGETNET. Please be aware of this and have your site scanned! I have spent
all day trying to get this resolved! :.(

Mc

"JE McGimpsey" wrote in message
...
Take a look he

http://www.mcgimpsey.com/excel/variablerate.html

If you have only the two rates, put total paid in A1, say, then use
something like:

=SUMPRODUCT(--(A1{0,10000}),(A1-{0,10000}),{0.1,0.8})


See the link for more flexible methods.




In article <ynjvd.753530$8_6.116095@attbi_s04,
"Mctabish" <mc@ <nospamjohnsonclan.net wrote:

Thanks Ben,
But this would not quite work as it only looks at that months commission.
I
need to see if 10000 has been paid, and then if so, then it is 90%,
otherwise, I need to deduct 70% as operating cost, and then once 10,000
has
been paid, then change percentage to 90%. So the back half is correct, I
just need to figure out the "test". Also, if say 9000 has been paid, and
this sale would bump the paid commission to being OVER 10,000, then only
deduct UPTO 10,000. Maybe I oversiplified it in original post:.(


Would it be easier to store the amount of commission already paid, and
then
check that? (I need to keep track of it anyways... This is for working up
a
budget as well)
Again, once 10000 has been paid during a given _year_ (accumulative),
then
my commission bumps up to 90%

tia,
Mc

"Ben McBen" wrote in message
...
a formula of the form:


=if(g1<10000,g1*.3,g1*.9)


should do the trick - you can replace the 20000 with a
reference to make it variable...

What do you suggest I scan? My site is an XHTML site, which consists of
text files that *can't* contain a virus. Neither can the pictures
associated with it.

It runs on a Unix (Apache) server, and was created on a Mac, neither of
which are susceptible to, nor propagate, IGETNET.

There are no executables available at the site. I have a few XL files
available for download, which have the potential for a macro virus, but
not IGETNET. These have been checked many times, and I'm reasonably
certain that none of them have ever been near a Windows machine. Of
course, I'm willing to check again if you can tell me what you
downloaded.

However, I think you should look elsewhere...

In article <lhwvd.257700$R05.201498@attbi_s53,
"Mctabish" <mc@ <nospamjohnsonclan.net wrote:

When I visited your site, I got a virus, and now I can not get rid of
IGETNET. Please be aware of this and have your site scanned! I have spent
all day trying to get this resolved! :.(

Hummmm...
I scanned my system about 1 hr before, and then when I clicked on your link,
it took a while to open. And ever since, I have been bombarded with popups
etc (yes I do have a PU Blocker, firewall etc...)
I am sorry if this caused you any inconvenance, I was just trying to let who
ever (and visiting your site was the only thing I did since my last scan...)
know that there was an issue. I felt that that was the responsible thing to
do.

Sorry,
Mc

"JE McGimpsey" wrote in message
...
What do you suggest I scan? My site is an XHTML site, which consists of
text files that *can't* contain a virus. Neither can the pictures
associated with it.

It runs on a Unix (Apache) server, and was created on a Mac, neither of
which are susceptible to, nor propagate, IGETNET.

There are no executables available at the site. I have a few XL files
available for download, which have the potential for a macro virus, but
not IGETNET. These have been checked many times, and I'm reasonably
certain that none of them have ever been near a Windows machine. Of
course, I'm willing to check again if you can tell me what you
downloaded.

However, I think you should look elsewhere...

In article <lhwvd.257700$R05.201498@attbi_s53,
"Mctabish" <mc@ <nospamjohnsonclan.net wrote:

When I visited your site, I got a virus, and now I can not get rid of
IGETNET. Please be aware of this and have your site scanned! I have spent
all day trying to get this resolved! :.(