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(F1>500000,F1*.004,F1*.0035)))

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

Hi Tim,

Take a look here, you should be able to adapt this to suit your needs.

http://www.yqcomputer.com/

HTH

Martin

Take a look here, you should be able to adapt this to suit your needs.

http://www.yqcomputer.com/

HTH

Martin

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?

This works in every scenario with the exception of the first one. It should

calculate .0025 and it is calculating .0035. Any suggestions?

1. My I's are coming up i's. (lower case)

2. Fonts not clear, i's and L's are red in color

3. Please can Word spell liaise, liaison etc with 2 i's!

4. What to do when -I's are too long

6. Outlook 2000 Service Release I's Foolish Security Enhancements

7. I's confused..and have a font/message issue

8. Dotting the I's and crossing the T's

9. Graffiti 2 - Capital k's, t's and i's

10. Capital I's are a pain, how do I get rid of them

11. i's

12. I's better to be fat and a smoker than slim and healthy.

13. I's possible to use a RadioButtonGroup cellrenderer???

14. I's callin out john lewis, walter mitty, tim o and more.

15. TRS-80 Model II Emulator? / 200,000 Model I's?

4 post • Page:**1** of **1**