SOLVED

Formula help

Copper Contributor

Hi Guys, 

 

I'm really need help with a formula to help me with a commission banding structure, so I can just enter one amount of money and it does all the calculations behind it :)

 

Commission Structure:

0-£10,000 = 15%

£10,001 - £20,000 = 20%

£20,001 - £30,000 = 25%

£30,001 + = 30%

 

For example if someone made £33,000, they would get £10k at 15%, £10k at 20%, £10k at 25% and £3k at 30%. 

 

If anyone can help me with this one I'd be forever grateful :) 

1 Reply
best response confirmed by Jamil Mohammad (Bronze Contributor)
Solution

@06hstevens , if you add helper range as here

image.png

when the formula is like

=SUMPRODUCT((E2-$B$2:$B$5)*((E2-$B$2:$B$5)>0)*$C$2:$C$5)
1 best response

Accepted Solutions
best response confirmed by Jamil Mohammad (Bronze Contributor)
Solution

@06hstevens , if you add helper range as here

image.png

when the formula is like

=SUMPRODUCT((E2-$B$2:$B$5)*((E2-$B$2:$B$5)>0)*$C$2:$C$5)

View solution in original post