Are you planning on calculating commission for the sales staff, but the thought of creating a spreadsheet with complex formulae scares you. We have a solution for you, by nesting the IF and the AND functions one can ensure that the set conditions are met before commission is paid out. Below we explain how commission can only be paid out once the following conditions are satisfied.
- Actual Sales is greater than or equal to Target Sales
- Employee falls under the Comm structure
Applies To: Excel 2003, 2007 and 2010
- The screen shot above will be used for this example
- Select cell F4
- Select as per screen shot below
4. Enter formulae as given below
5. Select OK
6. Copy the formula in cell F4 down to cells F5:F15
7. Select cell F16 and type =Sum (F4:F15). The final answer will be as per screen below
If you have any suggestions for an Excel Tips & Tricks topic, or you would like help with a particular function, please email enablement@alchemex.com and yours could be the next Tip of the Week.
Alchemex provides examples of MS Excel procedures for illustration only, without warranty expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The MS Excel procedures on this web site are provided “as is” cannot be guaranteed that they can be used in all situations
