I am trying to get Excel to calculate commission based on different factors.

In column B I have "number of units" - if the value is less than 10 the
commission is .0025, if the value is greater than 10 the commission is .0035.
But there is more....

In column F I have dollar amounts that can also change the same commission
amount. If the value in column F is 199,999 or less the commission amount is
..0025, if the value is 200,000 - 499,999 the commission amount is .0035 and
if the value is 500,000 or more the commission amount is .0040.

The commission amounts that are referenced will go into column D.

Scenarios -

9 loans and less than 199,999 in column F should calculate .0025 commission.

9 loans and 350,000 in column F should calculate .0035 in commission.

10 loans and 150,000 in column F should calculate .0035 in commission.

The dollar amount that the commission rate is calculated off of is in column
C.

I would appreciate any help I can get. I have spent hours on this and I
can't figure it out.

Thanks in advance,

Try using the And function in the ifs. So an untested attempt I would do

=IF(And(B1<10,F1<200,000),F1*.0025,IF(AND(B1<10,F1 199,999,F1<500,000),F1*.0035,IF(F1500000,F1*.004 ,F1*.0035)))

"Tim" wrote:

I am trying to get Excel to calculate commission based on different factors.

In column B I have "number of units" - if the value is less than 10 the
commission is .0025, if the value is greater than 10 the commission is .0035.
But there is more....

In column F I have dollar amounts that can also change the same commission
amount. If the value in column F is 199,999 or less the commission amount is
.0025, if the value is 200,000 - 499,999 the commission amount is .0035 and
if the value is 500,000 or more the commission amount is .0040.

The commission amounts that are referenced will go into column D.

Scenarios -

9 loans and less than 199,999 in column F should calculate .0025 commission.

9 loans and 350,000 in column F should calculate .0035 in commission.

10 loans and 150,000 in column F should calculate .0035 in commission.

The dollar amount that the commission rate is calculated off of is in column
C.

I would appreciate any help I can get. I have spent hours on this and I
can't figure it out.

Thanks in advance,

Hi Tim,

Take a look here, you should be able to adapt this to suit your needs.
http://mcgimpsey.com/excel/variablerate.html

HTH
Martin


"Tim" wrote in message
...
I am trying to get Excel to calculate commission based on different
factors.

In column B I have "number of units" - if the value is less than 10 the
commission is .0025, if the value is greater than 10 the commission is
.0035.
But there is more....

In column F I have dollar amounts that can also change the same commission
amount. If the value in column F is 199,999 or less the commission amount
is
.0025, if the value is 200,000 - 499,999 the commission amount is .0035
and
if the value is 500,000 or more the commission amount is .0040.

The commission amounts that are referenced will go into column D.

Scenarios -

9 loans and less than 199,999 in column F should calculate .0025
commission.

9 loans and 350,000 in column F should calculate .0035 in commission.

10 loans and 150,000 in column F should calculate .0035 in commission.

The dollar amount that the commission rate is calculated off of is in
column
C.

I would appreciate any help I can get. I have spent hours on this and I
can't figure it out.

Thanks in advance,

Thank you for your help.

This works in every scenario with the exception of the first one. It should
calculate .0025 and it is calculating .0035. Any suggestions?

"akphidelt" wrote:

Try using the And function in the ifs. So an untested attempt I would do

=IF(And(B1<10,F1<200,000),F1*.0025,IF(AND(B1<10,F1 199,999,F1<500,000),F1*.0035,IF(F1500000,F1*.004 ,F1*.0035)))

"Tim" wrote:

I am trying to get Excel to calculate commission based on different factors.

In column B I have "number of units" - if the value is less than 10 the
commission is .0025, if the value is greater than 10 the commission is .0035.
But there is more....

In column F I have dollar amounts that can also change the same commission
amount. If the value in column F is 199,999 or less the commission amount is
.0025, if the value is 200,000 - 499,999 the commission amount is .0035 and
if the value is 500,000 or more the commission amount is .0040.

The commission amounts that are referenced will go into column D.

Scenarios -

9 loans and less than 199,999 in column F should calculate .0025 commission.

9 loans and 350,000 in column F should calculate .0035 in commission.

10 loans and 150,000 in column F should calculate .0035 in commission.

The dollar amount that the commission rate is calculated off of is in column
C.

I would appreciate any help I can get. I have spent hours on this and I
can't figure it out.

Thanks in advance,