Desperately needing help...

[Weresmurf]

Trying to work out how to do an IF query in Excel. Attempting to set u
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 sel
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 commissio
to be calculated to, how do I work it so that IF its equal to or greate
than 2000, it's calculated by 8%, IF its greater than 1000 but less tha
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

 

[Ron Rosenfeld]

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.

 

[Weresmurf]

;1605565']On Sun, 16 Sep 2012 14:23:32 +0000, Weresmur

Trying to work out how to do an IF query in Excel. Attempting to se 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 o greater
than 2000, it's calculated by 8%, IF its greater than 1000 but les 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 sale
person sells $2,000 or more, he gets 8% on the total sales figure, an
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, the
the formula would be different.

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

 

[KevUK]

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})*B1

 

[Ron Rosenfeld]

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.