i am trying to use the if condition to findout the commission for salespersons. the condition is as follows: sales below $20 attracts no commision; the next $20 attracts a commission of 5%,prorator; the next $100 atrracts10% prorator; the next $660 atrracts17.5% prorator;the next $800 atrracts25% prorator;

I'm sure there must be a simpler way to do this, but here's what I came up with. It uses MIN and MAX rather than IF. Note once the sale gets above $1600, it will no longer give commission. If that's not what you want to happen, and you want to keep on giving 25% for everything above $1600, use the revised formula =MAX(0,MIN(A1-20,20))*0.05+MAX(0,MIN(A1-40,100))*0.1+MAX(0,MIN(A1-140,660))*0.175 +MAX(0,MIN(A1-800,800))*0.25 and revised to keep giving commission at 25% over $1600 =MAX(0,MIN(A1-20,20))*0.05+MAX(0,MIN(A1-40,100))*0.1+MAX(0,MIN(A1-140,660))*0.175 +MAX(0,MIN(A1-800))*0.25 Note I've dropped in a couple of carriage returns up there to make it fit in the forum. Remove these before pasting into the sheet. HTH

A couple of posting suggestions first: 1 - Please try to use a subject line that is relevant to the content of the post. If everyone used subject lines like "MS excel" or "Help Me", it would be impossible to distinguish one question from another. It also makes searching the archive extremely difficult. 2 - It customary to actually ask a question, even if it's as simple as "Can anyone offer a suggestion on how to do this? Thanks!" Simply telling us what you are trying to do and expecting answers doesn't come across quite as "friendly". That said, I think we need a few more details. When you say "the next $20 attracts a commission of 5%, prorator" Do you mean that only the $20 above the first $20 attracts a 5% bonus or does the whole $40 attract 5%? What exactly do you mean by "prorator"? When you say "the next $100 atrracts 10%, prorator" do you mean the first $20 attracts 0%, 21 to $40 attracts 5% and $41 to $140 attracts 10%? Perhaps if you supplied some example data, matching dollar amounts to commission amounts, that might help. We look forward to helping you find a solution.