4) Using IF, AND, or other logical formulas, calculate Commissions. In order to calculate it, you must first find Collections divided by Sales as a percentage for each transaction. There are three different calculations for Commissions depending on the Collections divided by Sales result. If Collection divided by Sales is greater than or equal to 70%, Commissions is 3% x Sales. If Collection divided by Sales is greater than or equal to 40% but less than 70%, Commissions is 2% x Sales. For all other transactions, Commissions is $0