• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

IF Formula Closing Bracket is a issue for me

A

Augustinraj.a

New Member
Dear Team,

i have created the IF condition for the one cell(Income Tax Purpose) where am not able to close the brackets.

This is the formula - can you please help me this

=ROUND(IF(((TODAY()-E3)/365)<60,IF(BB3<=250000,0,IF(BB3<=500000,((BB3-250000)*5%),IF(BB3<=750000,((BB3-500000)*10%+12500),IF(BB3<=1000000,((BB3-750000)*20%+37500),IF(BB3<=1250000,((BB3-1000000)*25%+75000),IF(BB3<=1500000,((BB3-1250000)*30%+125000),((BB3-1500000)*30%+187500))))))),IF(((TODAY()-E3)/365)>80,IF(BB3<=500000,0,IF(BB3<=750000,((BB3-500000)*10%),IF(BB3<=1000000,((BB3-750000)*15%+25000),IF(BB3<=1250000,((BB3-1000000)*20%+62500),IF(BB3<=1500000,((BB3-1250000)*25%+112500),((BB3-1500000)*30%)+175000)),IF(BB3<=300000,0,IF(BB3<=500000,((BB3-300000)*5%),IF(BB3<=750000,((BB3-500000)*10%+10000),IF(BB3<=1000000,((BB3-750000)*15%+35000),IF(BB3<=1250000,((BB3-1000000)*20%+72500),IF(BB3<=1500000,((BB3-1250000)*25%+122500),((BB3-1500000)*30%+185000))))))),0)

Augustin -
9941678910
 

Attachments

  • Taxation-Format-FY2020-2021.xlsx
    19.4 KB · Views: 7
vletm said:
Augustinraj.a
Your formula cannot work at all.
Can You try to explain - how it would work? ... without 'that'!
Click to expand...
Dear VLETM - I try to apply the multiple if condition to calculate the tax where in its say the error as "Your formula is missing a parenthesis--) or (.check the formula,and than add the parenthesis in the appropriate place"
Please guide me what needful can be done - BB3 cell is Total Taxable value from that this formula should apply.
 
vletm said:
Augustinraj.a
a) Did You skip next?
Can You try to explain - how it would work? ... without 'that'!

b) syntax =IF(logical_test, [value_if_true], [value_if_false])
Click to expand...


Correct Formula
=ROUND(IF(((TODAY()-E3)/365)<60,IF(BC3<250000,0,IF(BC3<=500000,((BC3-250000)*5%),IF(BC3<=1000000,((BC3-500000)*20%+12500),((BC3-1000000)*30%)+112500))),IF(((TODAY()-E3)/365)>80,IF(BC3<500000,0,IF(BC3<=1000000,((BC3-500000)*20%),((BC3-1000000)*30%)+100000)),IF(BC3<300000,0,IF(BC3<=500000,((BC3-300000)*5%),IF(BC3<=1000000,((BC3-500000)*20%+10000),((BC3-1000000)*30%)+110000))))),0)

The above said formula is working perfectly in my tax calculation whereas the same formula i have added more "IF" but unfortunately that didnt work and showed the error.

Error Formula
=ROUND(IF(((TODAY()-E3)/365)<60,IF(BB3<=250000,0,IF(BB3<=500000,((BB3-250000)*5%),IF(BB3<=750000,((BB3-500000)*10%+12500),IF(BB3<=1000000,((BB3-750000)*20%+37500),IF(BB3<=1250000,((BB3-1000000)*25%+75000),IF(BB3<=1500000,((BB3-1250000)*30%+125000),((BB3-1500000)*30%+187500))))))),IF(((TODAY()-E3)/365)>80,IF(BB3<=500000,0,IF(BB3<=750000,((BB3-500000)*10%),IF(BB3<=1000000,((BB3-750000)*15%+25000),IF(BB3<=1250000,((BB3-1000000)*20%+62500),IF(BB3<=1500000,((BB3-1250000)*25%+112500),((BB3-1500000)*30%)+175000)),IF(BB3<=300000,0,IF(BB3<=500000,((BB3-300000)*5%),IF(BB3<=750000,((BB3-500000)*10%+10000),IF(BB3<=1000000,((BB3-750000)*15%+35000),IF(BB3<=1250000,((BB3-1000000)*20%+72500),IF(BB3<=1500000,((BB3-1250000)*25%+122500),((BB3-1500000)*30%+185000))))))),0)
 
vletm said:
'that'! ( 't
Click to expand...
Dear Vletm,
It’s a taxation formula to arrive the tax amount based on the income as per the income tax slab and the slab given below for your reference.
Taxable Income – XXXXXXXXX like this from this we need to derive the tax amount.
BA3 is a Taxable income in the sample sheet for your reference
Note – I don’t know how to do the syntax hence I don’t perform this.
In my sample sheet
If we select “H3” as OLD then the following table should work in the column of “BD3”
Income Tax for General( Age is <60)Tax Bracket
0 -250000
0%​
250001 - 500000
5%​
500001 - 1000000
20%​
500001 +
30%​
Income Tax for Senior Citizen( Age is >60<80)Tax Bracket
0 -300000
0%​
300001 - 500000
5%​
500001 - 1000000
20%​
500001 +
30%​
Income Tax for very Senior Citizen( Age is >80)Tax Bracket
0 - 500000
0%​
500001 - 1000000
20%​
1000001 +
30%​

If we select “H3” as New then the following table should work in the column of “BD3”
Income Tax for General ( Age is <60)Tax Bracket
0 - 2.5
0%​
2.5 - 5
5%​
5 - 7.5
10%​
7.5 - 10
15%​
10 - 12.5
20%​
12.5 - 15
25%​
15+
30%​
Income Tax for Senior Citizens( Age is >60<80)Tax Bracket
0 - 3
0%​
3 - 5
5%​
5 - 7.5
10%​
7.5 - 10
15%​
10 - 12.5
20%​
12.5 - 15
25%​
15+
30%​
Income Tax for Super Senior Citizens ( Age is >80)Tax Bracket
0 - 2.5
0%​
2.5 - 5
0%​
5 - 7.5
10%​
7.5 - 10
15%​
10 - 12.5
20%​
12.5 - 15
25%​
15+
30%​
 

Attachments

  • Taxation-Format-FY2020-2021.xlsx
    24.4 KB · Views: 2
Augustinraj.a
... if someone is 60yrs or 80yrs ... hmm? .... then no matter of those taxes or how?
as well as 'lower' tables shows many duplicates eg 2.5, 5, 7.5, 10, 12.5
... only if over 15 then clear ... hmm?
 
Yes u r correct it should be =>60yrs and =>80 years

If we select “H3” as New then the following table should work in the column of “BD3”
Income Tax for General ( Age is <60)Tax Bracket
0 - 2500000%
250001 - 5000005%
500001 - 75000010%
750001 - 100000015%
1000001 - 125000020%
1250001 - 150000025%
150000130%
Income Tax for Senior Citizens( Age is =>60<80)Tax Bracket
0 - 3000000%
300001 - 5000005%
500001 - 75000010%
750001 - 100000015%
1000001 - 125000020%
1250001 - 150000025%
150000130%
Income Tax for Super Senior Citizens ( Age is =>80)Tax Bracket
0-5000000%
500001 - 75000010%
750001 - 100000015%
1000001 - 125000020%
1250001 - 150000025%
150000130%
 
Augustinraj.a said:
am not able to close the brackets.
This is the formula - can you please help me this

=ROUND(IF(((TODAY()-E3)/365)<60,IF(BB3<=250000,0,IF(BB3<=500000,((BB3-250000)*5%),IF(BB3<=750000,((BB3-500000)*10%+12500),IF(BB3<=1000000,((BB3-750000)*20%+37500),IF(BB3<=1250000,((BB3-1000000)*25%+75000),IF(BB3<=1500000,<snip>)
Click to expand...
This is very hard work!
Hard to maintain when things change.
In the attached I've used a quite different formula. In cell L3 there is this:
Code: 
=IF((TODAY()-E3)/365<60,SUMPRODUCT((BC3>($BK$5:$BK$8))*(BC3-($BK$5:$BK$8))*($BL$5:$BL$8-$BL$4:$BL$7)),IF((TODAY()-E3)/365>80,SUMPRODUCT((BC3>($BQ$5:$BQ$7))*(BC3-($BQ$5:$BQ$7))*($BR$5:$BR$7-$BR$4:$BR$6)),SUMPRODUCT((BC3>($BN$5:$BN$8))*(BC3-($BN$5:$BN$8))*($BO$5:$BO$8-$BO$4:$BO$7))))
This is only an alternative to your own working formula (which I put in cell H3 for comparison of results).
As you can see, it uses 3 tables of tax brackets around cells BK3:BR8.
Now that this produces the same results as yours (for all ages) you have some work to do to adjust the three tax bracket tables by adding and changing rows, and how they're referred to in the formula.
I've left some helper columns which aren't needed, but they'll hep you to see how the longer formula was produced.
I'm sorry, I forgot to reinstate the ROUND function in these formulae.

In a try to simplify your task, I've added a second sheet which uses a longer formula, but one which you should never have to adjust should your tax bracket levels (or numbers of them) or percentages, change in the future:
Code: 
=IF((TODAY()-E3)/365<60,SUMPRODUCT((BC3>(Under60))*(BC3-(Under60))*(OFFSET(Under60,0,1)-OFFSET(Under60,-1,1))),IF((TODAY()-E3)/365>80,SUMPRODUCT((BC3>(Over80))*(BC3-(Over80))*(OFFSET(Over80,0,1)-OFFSET(Over80,-1,1))),SUMPRODUCT((BC3>(From60to80))*(BC3-(From60to80))*(OFFSET(From60to80,0,1)-OFFSET(From60to80,-1,1)))))
It uses 3 Named ranges (Under60, From60to80 and Over80) which I've shaded in light green on that second sheet (called WithNamedRanges). What you will have to do is if you have more tax brackets, you'll need to adjust the size(s) of those named ranges in the Name Manager. Apart from that you need do nothing apart from look at the new results.

The named ranges don't have to be on the same sheet, they can be hidden away on another, perhaps hidden, sheet.
 

Attachments

  • Chandoo44142.xlsx
    18.6 KB · Views: 3
p45cal said:
This is very hard work!
Hard to maintain when things change.
In the attached I've used a quite different formula. In cell L3 there is this:
Code: 
=IF((TODAY()-E3)/365<60,SUMPRODUCT((BC3>($BK$5:$BK$8))*(BC3-($BK$5:$BK$8))*($BL$5:$BL$8-$BL$4:$BL$7)),IF((TODAY()-E3)/365>80,SUMPRODUCT((BC3>($BQ$5:$BQ$7))*(BC3-($BQ$5:$BQ$7))*($BR$5:$BR$7-$BR$4:$BR$6)),SUMPRODUCT((BC3>($BN$5:$BN$8))*(BC3-($BN$5:$BN$8))*($BO$5:$BO$8-$BO$4:$BO$7))))
This is only an alternative to your own working formula (which I put in cell H3 for comparison of results).
As you can see, it uses 3 tables of tax brackets around cells BK3:BR8.
Now that this produces the same results as yours (for all ages) you have some work to do to adjust the three tax bracket tables by adding and changing rows, and how they're referred to in the formula.
I've left some helper columns which aren't needed, but they'll hep you to see how the longer formula was produced.
I'm sorry, I forgot to reinstate the ROUND function in these formulae.

In a try to simplify your task, I've added a second sheet which uses a longer formula, but one which you should never have to adjust should your tax bracket levels (or numbers of them) or percentages, change in the future:
Code: 
=IF((TODAY()-E3)/365<60,SUMPRODUCT((BC3>(Under60))*(BC3-(Under60))*(OFFSET(Under60,0,1)-OFFSET(Under60,-1,1))),IF((TODAY()-E3)/365>80,SUMPRODUCT((BC3>(Over80))*(BC3-(Over80))*(OFFSET(Over80,0,1)-OFFSET(Over80,-1,1))),SUMPRODUCT((BC3>(From60to80))*(BC3-(From60to80))*(OFFSET(From60to80,0,1)-OFFSET(From60to80,-1,1)))))
It uses 3 Named ranges (Under60, From60to80 and Over80) which I've shaded in light green on that second sheet (called WithNamedRanges). What you will have to do is if you have more tax brackets, you'll need to adjust the size(s) of those named ranges in the Name Manager. Apart from that you need do nothing apart from look at the new results.

The named ranges don't have to be on the same sheet, they can be hidden away on another, perhaps hidden, sheet.
Click to expand...
Thnaks for your esteem support
 
Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.


Note: As you are new I will add it for you.Please read forum rules which you might have missed

IF Formula Closing Bracket is a issue for me

=round(if(((today()-e3)/365)<60,if(bb3<=250000,0,if(bb3<=500000,((bb3-250000)*5%),if(bb3<=750000,((bb3-500000)*10%+12500),if(bb3<=1000000,((bb3-750000)*20%+37500),if(bb3<=1250000,((bb3-1000000)*25%+75000),if(bb3<=1500000,((bb3-1250000)*30%+125000),((bb3-1500000)*30%+187500)))))),0),if(((today()-e...
www.excelforum.com
 
You must log in or register to reply here.
Back
Top