Help with a variable commission calculator

[swimwithfishes]

Hello all, new to the forum and posting a question about commission calculations.
I have been using an IF formula to calculate variable commissions but this is proving to be a little unwieldy and wondered if there was a better way to generate the figures.

Company has various bands for calculating earnings which are as follows:


BANDS PROFIT BAND %

£1,000 0.00%
£2,000 0.00%
£3,000 2.00%
£4,000 2.00%
£5,000 2.50%
£6,000 5.00%
£7,000 7.50%
£8,000 18.00%
£9,000 20.00%
£10,000 25.00%
£11,000 25.00%
£12,000 27.50%
£15,000 30.00%

so over £3000 and over they earn 2%, £5000 and over, they will earn 2,% etc.

My current formula is:

=IF(M8<D10,M8*0%,IF(M8<D11,M8*E10%,IF(M8<D12,M8*E11%,IF(M8<D13,M8*E12,IF(M8<D14,M8*E13,IF(M8<D15,M8*E14,IF(M8<D16,M8*E15,IF(M8<D17,M8*E16,IF(M8<D18,M8*E17,IF(M8<D19,M8*E18,IF(M8<D20,M8*E19,IF(M8<D21,M8*E20))))))))))))

Is there a better way to do this? I wanted the formula to scan the figure compare it to the values and generate a figure. Is this possible?

Any advice gratefully received!

 

[JackDanIce]

Welcome to the board. You may need to adjust the ranges and look up values, but try:

<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B1</TD><TD>=INDEX($A$2:$B$7,MATCH(A1,$A$2:$A$7,1),2)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">3.5</TD><TD style="BACKGROUND-COLOR: #ffff00; FONT-WEIGHT: bold">C</TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">1</TD><TD style="FONT-SIZE: 8pt">A</TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">2</TD><TD style="FONT-SIZE: 8pt">B</TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">3</TD><TD style="FONT-SIZE: 8pt">C</TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">4</TD><TD style="FONT-SIZE: 8pt">D</TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">5</TD><TD style="FONT-SIZE: 8pt">E</TD></TR><TR style="HEIGHT: 15px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right; FONT-SIZE: 8pt">6</TD><TD style="FONT-SIZE: 8pt">F</TD></TR></TBODY></TABLE>