cannot calculate formulas correctly for commissions

hello,

i am new to excel and i happened to stumble upon this mecca of excel help! glad to be here.

my dilemma revolves around accurately calculation each salesman's commission based off of several factors:

inventory type:
SR Broker
SR Inventory
Corp. Inventory
Corp. Broker

i use the first sheet, USA to enter the data. if sales rep. sells a machine that is a SR Broker or SR Inventory, i use a drop down to mark machine sold in USA and consult the subtotal (column X) on sheet 2, USA CALCULATIONS. i then need to consult the table found in $F$3:$G$11 in VARIABLES, sheet 3.

each salesmen listed in column F and his specific % is listed next to him in column G.

if a salesman sells a Corp. Inv. or Corp. Broker piece of machinery, salesman gets a flat fee based off of the net sales (which i call subtotal) the subtotal reflects all expenses involved.
if column X<= 3999.99 = $500
<= 5999.99 = $1000
>= 6000.00 = $1500

this is the current formula our it guy made for us but it is not calculating correctly.
=IF(ISERROR((IF(C3="Corp Inventory",((IF((AA3-X3)>1999.99,IF((AA3-X3)<4000,500,"0"),"0"))+(IF((AA3-X3)>3999.99,IF((AA3-X3)<6000,1000,"0"),"0"))+(IF((AA3-X3)>5999.99,IF((AA3-X3)<40000000,1500,"0"),"0"))),IF(C3="SR Inventory",((AA3-X3)*VLOOKUP(AF3,VARIABLES!$F:$G,2,FALSE)),0)))),0,((IF(C3="Corp Inventory",((IF((AA3-X3)>1999.99,IF((AA3-X3)<4000,500,"0"),"0"))+(IF((AA3-X3)>3999.99,IF((AA3-X3)<6000,1000,"0"),"0"))+(IF((AA3-X3)>5999.99,IF((AA3-X3)<40000000,1500,"0"),"0"))),IF(C3="SR Inventory",((AA3-X3)*VLOOKUP(AF3,VARIABLES!$F:$G,2,FALSE)),0)))))


another column is called MF Commission column Q. i need to construct a formula in this column that does the following: this guy gets 10% on all SR Broker/SR Inventory sold machines.
if it is Corp. Inv./Corp. Broker he gets 20%. at the end of the year he gets a 1% bonus based off of the gross sales.

this is the formula for the MF column and it is not calculating correctly, either.

=IF(AF3="Blake",(0),IF(AF3="Mike",(0),(IF(ISERROR((IF(C3="Corp Inventory",((AA3-X3)*20%),IF(C3="SR Inventory",((AA3-X3)*10%),0)))),0,((IF(C3="Corp Inventory",((AA3-X3)*20%),IF(C3="SR Inventory",((AA3-X3)*10%),0))))))))


note: blake is owner so he may sell machines, but he does not have a commission rate.

any help would be greatly appreciated. below i have attached a sample of our chart with formulas.

thank you!

Originally Posted by ChemistB

So which is it; OR
At a glance, there are shorter ways to write the formula and your "0" should not be in quotes but want to make sure we're doing the right calculation first.

AA3-X3 option

thank you

i think that might do it for the MF category. any thoughts on the salesmen calculation?
thank you!

ok, we are almost there!!! the only hiccup i see is that in the SR Commission column, Column O-- it still counts Mike as a salesman and therefore gives him the salesman 500, 1000 or 1500.. and not the 10% of (AA-X)... any thoughts?

THANK YOU!! i had a little issue, but i tweaked the formula to this: =IF((AF16={"Blake"}),0, IFERROR(IF(OR(C16={"Corp Inventory","Corp Broker"}),(AA16-X16)*20%,IF(OR(C16={"SR Inventory","SR Broker"}),(AA16-X16)*10%,0)),0))

and now it works!! thank you so much for your patience and help!!!

i am starting on year end accounting and i have another question..

do you have a recommendation of how i can tweak the formula you gave me for the salesmen if there is a split commission? (co-broker) it would be split 50/50. there are some circumstances when it is a machine that is either SR Broker/ SR Inv. or Corp. Broker/ Corp. Inv.

the commission would then need to be applied to both salesmen.

any advice would be greatly appreciated!

thank you