Need help with formula

%3CLINGO-SUB%20id%3D%22lingo-sub-1735234%22%20slang%3D%22en-US%22%3ENeed%20help%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1735234%22%20slang%3D%22en-US%22%3E%3CP%3EHello.%3C%2FP%3E%3CP%3EI%20am%20sure%20this%20is%20a%20simple%20problem.%20Formula%20to%20multiply%20part%20of%20the%20cell%20value%2C%20based%20on%20range%2C%20by%20a%20different%20%25.%26nbsp%3B%20Please%20see%20a%20simple%20example%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECell%20value%3A%20%24100%2C000%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERow%201%3A%26nbsp%3B%20If%20cell%20value%20is%20between%20%240%20to%2010%2C000%2C%208%25.%3CBR%20%2F%3ERow%202%3A%20If%20cell%20value%20is%20between%20%2410%2C001%20to%2015%2C000%2C%2010%25.%3CBR%20%2F%3ERow%203%3A%26nbsp%3B%20If%20cell%20value%20is%20%2415%2C0001%20to%2020%2C000%2C%2015%25.%3CBR%20%2F%3ERow%204%3A%26nbsp%3B%20If%20cell%20value%20is%20%2420%2C001%20to%2025%2C000%2C%2020%25%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1735234%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1735362%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1735362%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F816611%22%20target%3D%22_blank%22%3E%40Jaymes1995%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWith%20such%20helper%20range%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20669px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F223379iF55B5A15D19F4242%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Eformula%20could%20be%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DSUMPRODUCT((IF(%24A%242%26gt%3B%24E%242%3A%24E%245%2C%24E%242%3A%24E%245%2C%24A%242)-%24D%242%3A%24D%245%2B1)*(%24A%242%26gt%3B%24D%242%3A%24D%245)*%24F%242%3A%24F%245)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1736684%22%20slang%3D%22en-US%22%3ERe%3A%20Need%20help%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1736684%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20my%20friend.%26nbsp%3B%20This%20is%20all%20I%20needed%20to%20get%20me%20started.%26nbsp%3B%20Cheers.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello.

I am sure this is a simple problem. Formula to multiply part of the cell value, based on range, by a different %.  Please see a simple example below.

 

Cell value: $100,000

 

Column A

 

Row 1:  If cell value is between $0 to 10,000, 8%.
Row 2: If cell value is between $10,001 to 15,000, 10%.
Row 3:  If cell value is $15,0001 to 20,000, 15%.
Row 4:  If cell value is $20,001 to 25,000, 20%

3 Replies

@Jaymes1995 

With such helper range

image.png

formula could be

=SUMPRODUCT((IF($A$2>$E$2:$E$5,$E$2:$E$5,$A$2)-$D$2:$D$5+1)*($A$2>$D$2:$D$5)*$F$2:$F$5)

@Sergei Baklan 

 

Thank you my friend.  This is all I needed to get me started.  Cheers. 

@Jaymes1995 , you are welcome