SOLVED
Home

Formula help

%3CLINGO-SUB%20id%3D%22lingo-sub-551649%22%20slang%3D%22en-US%22%3EFormula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-551649%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Guys%2C%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20really%20need%20help%20with%20a%20formula%20to%20help%20me%20with%20a%20commission%20banding%20structure%2C%20so%20I%20can%20just%20enter%20one%20amount%20of%20money%20and%20it%20does%20all%20the%20calculations%20behind%20it%20%3A)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECommission%20Structure%3A%3C%2FP%3E%3CP%3E0-%C2%A310%2C000%20%3D%2015%25%3C%2FP%3E%3CP%3E%C2%A310%2C001%20-%20%C2%A320%2C000%20%3D%2020%25%3C%2FP%3E%3CP%3E%C2%A320%2C001%20-%20%C2%A330%2C000%20%3D%2025%25%3C%2FP%3E%3CP%3E%C2%A330%2C001%20%2B%20%3D%2030%25%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20example%20if%20someone%20made%20%C2%A333%2C000%2C%20they%20would%20get%20%C2%A310k%20at%2015%25%2C%20%C2%A310k%20at%2020%25%2C%20%C2%A310k%20at%2025%25%20and%20%C2%A33k%20at%2030%25.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20anyone%20can%20help%20me%20with%20this%20one%20I'd%20be%20forever%20grateful%20%3A)%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-551649%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-552168%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-552168%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F338957%22%20target%3D%22_blank%22%3E%4006hstevens%3C%2FA%3E%26nbsp%3B%2C%20if%20you%20add%20helper%20range%20as%20here%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20615px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F112730iFB69961BDCA5254E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ewhen%20the%20formula%20is%20like%3C%2FP%3E%0A%3CPRE%3E%3DSUMPRODUCT((E2-%24B%242%3A%24B%245)*((E2-%24B%242%3A%24B%245)%26gt%3B0)*%24C%242%3A%24C%245)%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
06hstevens
Occasional Visitor

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
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)
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
23 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies