Rounding an IF statement

[grcshekar]

Dear Experts

How do I add ROUND,0 to below formula


=IF(AND(M2*12>500000,M2*12<=750000),(12500+(M2*12-500000)*10%)*(1.04/12),IF(AND(M2*12>750000,M2*12<=1000000),(37500+(M2*12-750000)*15%)*(1.04/12),IF(AND(M2*12>1000000,M2*12<=1250000),(75000+(M2*12-1000000)*20%)*(1.04/12),IF(AND(M2*12>1250000,M2*12<=1500000),(125000+(M2*12-1250000)*25%)*(1.04/12),IF(AND(M2*12>1500000),(187500+(M2*12 -1500000)* 30%)*(1.04/12),0)))))

 

[joeu2004]

Assuming the formula is correct to begin with (I did not try it):

=ROUND(IF(AND(M2*12>500000,M2*12<=750000),(12500+(M2*12-500000)*10%)*(1.04/12),IF(AND(M2*12>750000,M2*12<=1000000),(37500+(M2*12-750000)*15%)*(1.04/12),IF(AND(M2*12>1000000,M2*12<=1250000),(75000+(M2*12-1000000)*20%)*(1.04/12),IF(AND(M2*12>1250000,M2*12<=1500000),(125000+(M2*12-1250000)*25%)*(1.04/12),IF(AND(M2*12>1500000),(187500+(M2*12 -1500000)* 30%)*(1.04/12),0))))), 0)

But you really should ask for help with writing the original formula. I'm sure it can be done much more simply.

This looks like a progressive tax or commission or something similar. If you provide the original specifications (tax table; commission scale; whatever), we can help you improve the formula.

We can figure it out from the formula alone. But it would be easier if we have the original specifications.

(I suspect that 1.04/12 should be 1+4%/12. But again, we can only know that by looking at the original specifications.)

 

[grcshekar]

If I can be helped with revising formula then calculation is as under. Tax slab will refer to a cell in excel sheet. After calculating tax i need add 4% extra

Tax Slab​	Tax​
₹ 5,00,001 to ₹ 7,50,000​	₹ 12,500+(TI - ₹ 5,00,000) * 10%​
₹ 7,50,001 to ₹ 10,00,000​	₹ 37,500+(TI - ₹ 7,50,000) * 15%​
₹ 10,00,001 to ₹ 12,50,000​	₹ 75,000+(TI - ₹ 10,00,000) * 20%​
₹ 12,50,001 to ₹ 15,00,000​	₹ 1,25,000+(TI - ₹ 12,50,000) * 25%​
Greater than ₹ 15,00,000​	₹ 1,87,500+(TI - ₹ 15,00,000) * 30%​

 

[grcshekar]

TI against refers to a cell which will be same as tax slab

 

[grcshekar]

I am doing *12 to multiply monthly salary into annual salary

 

[joeu2004]

Refer to the table below.

Book1
	A	B	C	D	E								M	N	O	P	Q	R	S
1	Orig	Alt #1	Alt #2	Alt #3									Monthly	Annual		over....	tax	+ pct over	diff pct
2	26,000	26,000	26,000	26,000									52,083	624,996		0	0	0%	0%
3	58,501	58,501	58,501	58,501									72,917	875,004		500,000	12,500	10%	10%
4	104,000	104,000	104,000	104,000									93,750	1,125,000		750,000	37,500	15%	5%
5	162,499	162,499	162,499	162,499									114,583	1,374,996		1,000,000	75,000	20%	5%
6	429,000	429,000	429,000	429,000									187,500	2,250,000		1,250,000	125,000	25%	5%
7	0	0	0	0									20,833	249,996		1,500,000	187,500	30%	5%
Sheet1

Formulas:
A2: =ROUND(1.04 * IF(AND(M2*12>500000, M2*12<=750000), 12500+(M2*12-500000)*10%,
     IF(AND(M2*12>750000,M2*12<=1000000), 37500+(M2*12-750000)*15%,
     IF(AND(M2*12>1000000,M2*12<=1250000), 75000+(M2*12-1000000)*20%,
     IF(AND(M2*12>1250000,M2*12<=1500000), 125000+(M2*12-1250000)*25%,
     IF(AND(M2*12>1500000), 187500+(M2*12 -1500000)* 30%, 0))))), 0)

B2: =ROUND(1.04 * IF(M2*12>1500000, 187500+(M2*12-1500000)*30%,
     IF(M2*12>1250000, 125000+(M2*12-1250000)*25%,
     IF(M2*12>1000000, 75000+(M2*12-1000000)*20%,
     IF(M2*12>750000, 37500+(M2*12-750000)*15%,
     IF(M2*12>500000, 12500+(M2*12-500000)*10%, 0))))), 0)

C2: =ROUND(1.04 * CHOOSE(MATCH(M2*12,{0,500000,750000,1000000,1250000,1500000}),
     0, 12500+(M2*12-500000)*10%, 37500+(M2*12-750000)*15%,
     75000+(M2*12-1000000)*20%, 125000+(M2*12-1250000)*25%,
     187500+(M2*12-1500000)*30%), 0)

D2: =ROUND(1.04 * IF(M2*12<=$P$3, 0,
     $Q$3+SUMPRODUCT((M2*12>$P$2:$P$7)*(M2*12-$P$2:$P$7), $S$2:$S$7)), 0)

I will discuss multiplying 1.04 v. 1.04/12 (or 1+4%/12) below.

Rows 2 through 7 have examples in each "slab" (tier), to demonstrate the correctness for all possible taxable incomes.

Column N is provided for convenience. It is simply M2*12, for example. Of course, all of the formulas would benefit by replacing M2*12 with N2.

Column A has your original formula, with some very minor improvements.

Column B has the first alternative. There is no need to use AND(). In fact, the over-specification can lead to errors if M2 is not an integer. The formula in column B takes advantage of the left-to-right evaluation of IF(). That is, the evaluation stops with the first TRUE condition.

(You indicate that you use Excel 2010. If you actually use a more-recent version, you can use IFS instead of IF().)

Column C has the second alternative. MATCH returns an index 1 through 6, which selects the corresponding expression.

Column D is arguably the best alternative. It uses the tax table in P:S.

(Of course, you could replace the constants in the other formulas with references to columns P:S.)

I offer the several alternatives so you can choose the one that makes the most sense to you.

-----

You said that you want to increase the annual tax by 4%. So we should multiply by 1.04 or 1+4%.

I believe that when you wrote 1.04/12, you meant 1+4%/12. But either way, that seems wrong.

I presume that 4% is an annual rate, and you intended to multiply a monthly rate of 4%/12.

But even if you had calculated a monthly tax, you would still increase it by 4%, not 4%/12.

If I have misunderstood, please explain your intention by multiplying by 1.04/12.

 

[grcshekar]

I will go with below since I do not have tax table to use D2. I need little change. I need to divide the output of below formula by 12 so that I get monthly tax rate. How do I do this

=ROUND(1.04 * CHOOSE(MATCH(M2*12,{0,500000,750000,1000000,1250000,1500000}),
0, 12500+(M2*12-500000)*10%, 37500+(M2*12-750000)*15%,
75000+(M2*12-1000000)*20%, 125000+(M2*12-1250000)*25%,
187500+(M2*12-1500000)*30%), 0)

 

[grcshekar]

Also in the above formula round function is not working

 

[grcshekar]

Sorry, I will use this, but i need to divide result by 12 and then round by 0 in order to calculate monthly deductions

D2: =ROUND(1.04 * IF(M2*12<=$P$3, 0,
$Q$3+SUMPRODUCT((M2*12>$P$2:$P$7)*(M2*12-$P$2:$P$7), $S$2:$S$7)), 0)

 

[joeu2004]

i need to divide result by 12 and then round by 0 in order to calculate monthly deductions

I suspect as much. But you should consider whether to ROUNDUP to ensure that you pay no less tax than required (possibly getting a refund later), or to ROUNDDOWN to ensure that you pay no more tax than necessary (possibly owing more later).

The formulas become:

A2: =ROUND(1.04 * IF(AND(M2*12>500000, M2*12<=750000), 12500+(M2*12-500000)*10%,
     IF(AND(M2*12>750000,M2*12<=1000000), 37500+(M2*12-750000)*15%,
     IF(AND(M2*12>1000000,M2*12<=1250000), 75000+(M2*12-1000000)*20%,
     IF(AND(M2*12>1250000,M2*12<=1500000), 125000+(M2*12-1250000)*25%,
     IF(AND(M2*12>1500000), 187500+(M2*12 -1500000)* 30%, 0))))) / 12, 0)

B2: =ROUND(1.04 * IF(M2*12>1500000, 187500+(M2*12-1500000)*30%,
     IF(M2*12>1250000, 125000+(M2*12-1250000)*25%,
     IF(M2*12>1000000, 75000+(M2*12-1000000)*20%,
     IF(M2*12>750000, 37500+(M2*12-750000)*15%,
     IF(M2*12>500000, 12500+(M2*12-500000)*10%, 0))))) / 12, 0)

C2: =ROUND(1.04 * CHOOSE(MATCH(M2*12,{0;500000;750000;1000000;1250000;1500000}),
     0, 12500+(M2*12-500000)*10%, 37500+(M2*12-750000)*15%,
     75000+(M2*12-1000000)*20%, 125000+(M2*12-1250000)*25%,
     187500+(M2*12-1500000)*30%) / 12, 0)

D2: =ROUND(1.04 * IF(M2*12<=$P$3, 0,
     $Q$3+SUMPRODUCT((M2*12>$P$2:$P$7)*(M2*12-$P$2:$P$7), $S$2:$S$7)) / 12, 0)

Also in the above formula round function is not working

What does "not working" mean?

I suspect that you mean the syntax does not work.

You always need to account for regional and language differences in postings.

That said, I must admit that the syntax for array constants ( {...} ) might be non-intuitive in some languages.

I use comma (",") to separate columns and semicolon (";") to separate rows.

You might use back-slash ("\") to separate columns and semicolon to separate rows.

I should remember to post array constants with row separators, because I believe that semicolon is used more universally.

Even though you intend to use the SUMPRODUCT formula, which does not require array constants (as I wrote it), try the following for educational purposes.

C2: =ROUND(1.04 * CHOOSE(MATCH(M2*12,{0;500000;750000;1000000;1250000;1500000}),
     0, 12500+(M2*12-500000)*10%, 37500+(M2*12-750000)*15%,
     75000+(M2*12-1000000)*20%, 125000+(M2*12-1250000)*25%,
     187500+(M2*12-1500000)*30%) / 12, 0)

C3: =ROUND(1.04 * CHOOSE(MATCH(M3*12,{0\500000\750000\1000000\1250000\1500000}),
     0, 12500+(M3*12-500000)*10%, 37500+(M3*12-750000)*15%,
     75000+(M3*12-1000000)*20%, 125000+(M3*12-1250000)*25%,
     187500+(M3*12-1500000)*30%) / 12, 0)