Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old September 16th 12, 03:23 PM
Junior Member
 
First recorded activity by ExcelBanter: Sep 2012
Posts: 2
Smile 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   Report Post  
Old 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
Default 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   Report Post  
Old September 17th 12, 06:45 AM
Junior Member
 
First recorded activity by ExcelBanter: Sep 2012
Posts: 2
Default

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

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   Report Post  
Old 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
Default 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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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


All times are GMT +1. The time now is 02:29 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017