Hi All,
Wonder if you can help me with something.
I need to work out commission for a number of individuals.
The problem is there are two rates of commission, depending on whether a product is sold to a new customer, or an existing customer.
1% for existing customers
1.25% for new customers.
To establish whether a product (loans, in this example) was sold to a new customer or an existing customer, I need to look at the amount of the loan, and the interest rate charged to the customer.
So I have printed a sheet which shows loan bands and interest rates. I use this to manually determine whether the loan was sold to a new or existing customer. The sheet looks like this:
A Loan Amount / B New cust Rate / C Existing cust rate
$1,000 - $2,999 / 14.9% / 13.9%
$3,000 - $4,999 / 12.9% / 11.9%
$5,000 - $7,499 / 10.9% / 9.9%
$7,500 - $9,999 / 10.5% / 9.5%
$10,000 - $15,000 / 9.9% / 8.9%
Using this, I can tell that if a customer has a loan of $1,500, and has interest at 13.9%, he is an existing customer, so commission should be paid at 1%.
The commission is worked out in a table with
Column A - Loan amount
Column B - Interest Rate
Column C - New/Existing customer
Column D - =IF(C1="New Customer",(A1*1.25%),(A1*1%))
What I would like to do, is replace the manual input in Column C. I'd assumed it could be done with a LookUp - but it's proving to be beyond me at present....
I think I need to split column A in the first sheet, and have an upper and lower limit, but I'm now stumped.
Any help gratefully received.
Regards
Any
Wonder if you can help me with something.
I need to work out commission for a number of individuals.
The problem is there are two rates of commission, depending on whether a product is sold to a new customer, or an existing customer.
1% for existing customers
1.25% for new customers.
To establish whether a product (loans, in this example) was sold to a new customer or an existing customer, I need to look at the amount of the loan, and the interest rate charged to the customer.
So I have printed a sheet which shows loan bands and interest rates. I use this to manually determine whether the loan was sold to a new or existing customer. The sheet looks like this:
A Loan Amount / B New cust Rate / C Existing cust rate
$1,000 - $2,999 / 14.9% / 13.9%
$3,000 - $4,999 / 12.9% / 11.9%
$5,000 - $7,499 / 10.9% / 9.9%
$7,500 - $9,999 / 10.5% / 9.5%
$10,000 - $15,000 / 9.9% / 8.9%
Using this, I can tell that if a customer has a loan of $1,500, and has interest at 13.9%, he is an existing customer, so commission should be paid at 1%.
The commission is worked out in a table with
Column A - Loan amount
Column B - Interest Rate
Column C - New/Existing customer
Column D - =IF(C1="New Customer",(A1*1.25%),(A1*1%))
What I would like to do, is replace the manual input in Column C. I'd assumed it could be done with a LookUp - but it's proving to be beyond me at present....
I think I need to split column A in the first sheet, and have an upper and lower limit, but I'm now stumped.
Any help gratefully received.
Regards
Any
