Exceljet

Quick, clean, and to the point

Commission split formula example

Excel formula: Commission split formula example
Generic formula 
=commission*VLOOKUP(amount,split_table,column,TRUE)
Explanation 

To calculate a commission, and split the Commission between Agent and Broker according to a pre-established schedule, you can use the VLOOKUP function. In the example shown, the formula in D5 is:

=C5*VLOOKUP(B5,split_table,2,TRUE)

where split_table is the named range G7:I11

How this formula works

This formula assumes all commissions are 3%, as set in cell H4, the named range comm_rate.

The commission amount is split between the agent and broker, starting with a 50/50 split, and changing at fixed intervals as shown in the named range split_table (G7:I11).

The commission amount is calculated in column C with this formula:

=B5*comm_rate // total commission

The Agent and Broker amounts in columns D and E are calculated like this:

=C5*VLOOKUP(B5,split_table,2,TRUE) // agent
=C5*VLOOKUP(B5,split_table,3,TRUE) // broker

Notice these formulas are identical except for the column index. The formula for agent returns a value from column 2 in the table, and the formula for broker returns a value from column 3 in the table. In both formulas, VLOOKUP is set to approximate match explicitly, by providing the optional forth argument as TRUE.

When doing an approximate match, VLOOKUP assumes the table is sorted in ascending order. If VLOOKUP finds an exact match, it returns a value from matching row. If VLOOKUP encounters a value greater than the lookup value, it will return a value from the previous row

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.