# IF Formula Closing Bracket is a issue for me

#### 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.

=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

• 19.4 KB Views: 5

#### vletm

##### Excel Ninja
Augustinraj.a
Your formula cannot work at all.
Can You try to explain - how it would work? ... without 'that'!

#### Augustinraj.a

##### New Member
Augustinraj.a
Your formula cannot work at all.
Can You try to explain - how it would work? ... without 'that'!
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

##### Excel Ninja
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])`

#### Augustinraj.a

##### New Member
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])`

 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

##### Excel Ninja
Augustinraj.a
Third time:
a) Can You try to explain - how it would work? ... without 'that'! ( 'that' is Your ... 'formula' )
b) Did You check 'syntax' of Your ... formula?

#### Augustinraj.a

##### New Member
'that'! ( 't
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

• 24.4 KB Views: 2

#### vletm

##### Excel Ninja
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?

#### Augustinraj.a

##### New Member
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 - 250000 0% 250001 - 500000 5% 500001 - 750000 10% 750001 - 1000000 15% 1000001 - 1250000 20% 1250001 - 1500000 25% 1500001 30% Income Tax for Senior Citizens( Age is =>60<80) Tax Bracket 0 - 300000 0% 300001 - 500000 5% 500001 - 750000 10% 750001 - 1000000 15% 1000001 - 1250000 20% 1250001 - 1500000 25% 1500001 30% Income Tax for Super Senior Citizens ( Age is =>80) Tax Bracket 0-500000 0% 500001 - 750000 10% 750001 - 1000000 15% 1000001 - 1250000 20% 1250001 - 1500000 25% 1500001 30%

#### p45cal

##### Well-Known Member
am not able to close the brackets.

=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>)
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

• 18.6 KB Views: 3

#### vletm

##### Excel Ninja
Augustinraj.a
Are those Your sample results 'verified'?
... anyway, I got these

#### Attachments

• 29.5 KB Views: 4

#### Augustinraj.a

##### New Member
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.