Trying to calculate a commission

[darkwing_duck]

I have variable payments for two components to a sales plan:

# of customers maintained which pays a rep .05%
# of customers added which pays a rep 1%

I receive a report each quarter telling me the number of current
customers around the world. I have a spreadsheet that has the number
of customers by continent at the beginning of the quarter (in column
b) and at the end of the quarter (in column c). For instance, if the
title "# of customers" is in cell A1:

# of customers 1/1/10 4/1/10
North America 15000 16000
South America 500 400
Europe 2000 2000

If the number in cell C2 is greater than cell B2, then presumably the
rep kept all the North American customers and added 1000 (using the
above example). In that case, I want to multiply 15000 by .05% of his
variable and then multiply the new 1000 by 1% of his variable.

Assume the variable is in cell A20.

If the rep lost customers, or didn't add any, then the only component
would be the .05%.

I think this is going to start with an "IF" and involve "SUM", but
beyond that, I'm looking for some help. Any suggestions?

TIA,
Robert

 

[ozgrid.com]

Perhaps the links below will get you started.
http://www.ozgrid.com/Excel/sliding-bracket.htm
http://www.ozgrid.com/VBA/sliding-bracket-vba.htm