Trying to work out how to do an IF query in Excel. Attempting to set up some basic stuff for work.

Commission rate A 4% is in B6, Commission rate B 8% is in C6.
Commission Limit of 1000 A is in B7 and Limit 2000 B is in C7.

What I'm trying to work out, is how to do it so that if my guys sell enough during the week (we only run a family furniture business, starting out), how to calculate their comission.

If B12 is where their sales go, and c12 is where I want the commission to be calculated to, how do I work it so that IF its equal to or greater than 2000, it's calculated by 8%, IF its greater than 1000 but less than 2000 the get 4%. If they sell under 1000 a week they get 0?

I've been trying all day and just can't get it. Advice?

On Sun, 16 Sep 2012 14:23:32 +0000, Weresmurf wrote:


Trying to work out how to do an IF query in Excel. Attempting to set up
some basic stuff for work.

Commission rate A 4% is in B6, Commission rate B 8% is in C6.
Commission Limit of 1000 A is in B7 and Limit 2000 B is in C7.

What I'm trying to work out, is how to do it so that if my guys sell
enough during the week (we only run a family furniture business,
starting out), how to calculate their comission.

If B12 is where their sales go, and c12 is where I want the commission
to be calculated to, how do I work it so that IF its equal to or greater
than 2000, it's calculated by 8%, IF its greater than 1000 but less than
2000 the get 4%. If they sell under 1000 a week they get 0?

I've been trying all day and just can't get it. Advice?

Try: =IF(B12=$C$7,B12*$C$6,IF(B12$B$7,B12*$B$6,0))

Note that I have interpreted what you have written that if your sales person sells $2,000 or more, he gets 8% on the total sales figure, and not just on the portion that is equal to or more than $2,000.

If he makes 4% on the 2nd thousand, and 8% on anything above that, then the formula would be different.

Thank you, worked wonderfully, you've helped immensely and I can see where I went wrong too!!!! Thanks a million :)

As the old saying goes, "There is more than one way to skin a cat"

Try a LOOKUP:

=LOOKUP(B12,{0,1000,2000},{0,0.04,0.08})*B12

On Mon, 17 Sep 2012 05:45:02 +0000, Weresmurf wrote:

Thank you, worked wonderfully, you've helped immensely and I can see
where I went wrong too!!!! Thanks a million :)

Glad to help. Thanks for the feedback.