Remember Me?

#1
September 16th 12, 03:23 PM
 Junior Member First recorded activity by ExcelBanter: Sep 2012 Posts: 2
Desperately needing help...

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?

#2
September 16th 12, 09:38 PM posted to microsoft.public.excel.newusers
 external usenet poster First recorded activity by ExcelBanter: Jun 2010 Posts: 1,045
Desperately needing help...

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.
#3
September 17th 12, 06:45 AM
 Junior Member First recorded activity by ExcelBanter: Sep 2012 Posts: 2

Quote:
 Originally Posted by Ron Rosenfeld[_2_] 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
#4
September 17th 12, 07:44 AM
 Junior Member First recorded activity by ExcelBanter: Feb 2012 Posts: 3

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
#5
September 17th 12, 12:54 PM posted to microsoft.public.excel.newusers
 external usenet poster First recorded activity by ExcelBanter: Jun 2010 Posts: 1,045
Desperately needing help...

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.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post Virgo Excel Discussion (Misc queries) 9 February 19th 10 08:57 PM [email protected] Excel Worksheet Functions 3 November 23rd 08 07:24 PM perfj New Users to Excel 2 April 8th 08 09:02 PM Fred Djinn Holstings Excel Discussion (Misc queries) 11 February 2nd 07 07:46 PM Paula_p New Users to Excel 1 June 13th 06 10:26 PM

All times are GMT +1. The time now is 04:54 AM.