» Calculating Commissions Based on Sales Rank
CATEGORY - Excel Formula and Function
VERSION - All Microsoft Excel Versions
Problem: Columns A & B list the sales totals and IDs of five salespersons. We want to calculate each person's commission based on ranking in total sales, as per the following scheme:
Highest sales total - 15%
2nd highest total - 12%
3rd highest total - 10%
4th highest total - 8%
5th highest total - 5%
Solution:
Use the CHOOSE and RANK functions in the following formula:
=CHOOSE(RANK(B2,$B$2:$B$6),15%,12%,10%,8%,5%)*B2
Book Store:
Recommended Books:
- The 22 Immutable Laws of Marketing : Exposed and Explained by the World's Two
- Accounting the Easy Way
- Special Edition Using Microsoft Office XP
- Marketing Planning for Services
- How to Use Financial Statements: A Guide to Understanding the Numbers
- Managing by the Numbers: A Commonsense Guide to Understanding and Using Your Company's Financials: An Essential Resource for Growing Businesses
No comments have been submitted.
