Productif with different ranges

[Apra]

<!--[if gte mso 9]><xml> <w:WordDocument> <w:View>Normal</w:View> <w:Zoom>0</w:Zoom> <w:PunctuationKerning/> <w:ValidateAgainstSchemas/> <w:SaveIfXMLInvalid>false</w:SaveIfXMLInvalid> <w:IgnoreMixedContent>false</w:IgnoreMixedContent> <w:AlwaysShowPlaceholderText>false</w:AlwaysShowPlaceholderText> <w:Compatibility> <w:BreakWrappedTables/> <w:SnapToGridInCell/> <w:WrapTextWithPunct/> <w:UseAsianBreakRules/> <w:DontGrowAutofit/> </w:Compatibility> <w:BrowserLevel>MicrosoftInternetExplorer4</w:BrowserLevel> </w:WordDocument> </xml><![endif]--><!--[if gte mso 9]><xml> <w:LatentStyles DefLockedState="false" LatentStyleCount="156"> </w:LatentStyles> </xml><![endif]--><!--[if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable {mso-style-name:"Table Normal"; mso-tstyle-rowband-size:0; mso-tstyle-colband-size:0; mso-style-noshow:yes; mso-style-parent:""; mso-padding-alt:0in 5.4pt 0in 5.4pt; mso-para-margin:0in; mso-para-margin-bottom:.0001pt; mso-pagination:widow-orphan; font-size:10.0pt; font-family:"Times New Roman"; mso-ansi-language:#0400; mso-fareast-language:#0400; mso-bidi-language:#0400;} </style> <![endif]--> Sir, I want formula for tax purposes for different values as per the following slabs:

[FONT=&quot]a) [/FONT]0.1% of amount up to USD 1,000.00 subject to minimum amount of USD 25.00
[FONT=&quot]b) [/FONT]USD 100 and 0.05% of amount exceeding USD 1,000.00 and upto USD 10,000.00
[FONT=&quot]c) [/FONT]USD 550.00 and 0.01% of amount exceeding USD 10,000.00 subject to maximum amount of USD 5000.00
Please help….

 

[GlennUK]

You could just do three IF functions and add them together, like:

=IF(A1<=1000,MAX(0.1%*A1,25),0)+IF(AND(A1>1000,A1<=10000),0.05%*A1+100,0)+IF(A1>10000,MIN(0.01%*A1+550,5000),0)

 

[jasonb75]

[FONT=&quot]a) [/FONT]0.1% of amount up to USD 1,000.00 subject to minimum amount of USD 25.00
[FONT=&quot]b) [/FONT]USD 100 and 0.05% of amount exceeding USD 1,000.00 and upto USD 10,000.00
[FONT=&quot]c) [/FONT]USD 550.00 and 0.01% of amount exceeding USD 10,000.00 subject to maximum amount of USD 5000.00
Please help….

I would question those % values, the USD figures suggest 10%, 5% and 1%.

With the amount to tax in A1

=MIN(MAX(25,A1*10%),100)+MIN(MAX(0,A1-1000)*5%,450)+MIN(MAX(0,A1-10000)*1%,4450)

Should produce the correct sliding scale based on the threshold values specified.

Note that you don't see the expected 550 or 5000 limits in the formula, the cap is progressive. (100 + 450 = 550) + 4450 =5000

 

[Apra]

Thnx "jasonb75". It helped me a lot...

 

[Apra]

Thnx for help.