Help required in modifying IF Statement

[grcshekar71]

=IF(INDEX(EmployeeMaster!O:O,MATCH(H2,EmployeeMaster!A:A,0))="Y",IF(SUM(Y2:AD2)>=SUM(S2:X2),0,IF(SUM(S2:X2)>15000,15000*12%,SUM(S2:X2)*12%)),0).

This formula is intended to work as follows.

IF SUM(S2:X2) - SUM(Y2:AD2) >= SUM(S2:X2) * 12%
THEN RESULT
ELSE 0

Also H2,EmployeeMaster!A:A,0))="Y" is only a flag check if Y then continue evaluating formula else no

But it is not working. It works only if SUM(Y2:AD2) = SUM(S2:X2)

 

[grcshekar71]

=IF(INDEX(EmployeeMaster!O:O,MATCH(H2,EmployeeMaster!A:A,0))="Y",IF(SUM(Y2:AD2)>=SUM(S2:X2),0,IF(SUM(S2:X2)>15000,15000*12%,SUM(S2:X2)*12%)),0).

This formula is intended to work as follows.

IF SUM(S2:X2)> 15000 THEN IF 15000 * 12% >=SUM(S2:X2) - SUM(Y2:AD2) then 15000 * 12% else 0

ELSE SUM(S2:X2)< 15000 THEN IF SUM(S2:X2)* 12% >=SUM(S2:X2) - SUM(Y2:AD2) then SUM(S2:X2)* 12% else 0




Also H2,EmployeeMaster!A:A,0))="Y" is only a flag check if Y then continue evaluating formula else no

 

[DanteAmor]

Hi and welcome to MrExcel!

In your formula you have a condition, but in your explanation you put another condition.
Could you explain it, but with examples. Put here a generic sample of your data, where the different conditions and the result you require for each case.
Use XL2BB tool to put the data sample.

 

[grcshekar71]

Kindly go by explanation

If you see the attachment Column AE2 should have been 0 because the balance available is only 1000 and 1800 cannot be deducted. Rs 1800 is also produced from formula above and not an entered value

 

[DanteAmor]

Try this:

=IFERROR(IF(VLOOKUP(H2,EmployeeMaster!A1:O13,15,0)="Y",IF(SUM(S2:X2)>15000,IF(15000*12%>=(SUM(S2:X2) - SUM(Y2:AD2)), 15000*12%,0),IF(SUM(S2:X2)*12%>=(SUM(S2:X2) - SUM(Y2:AD2)),SUM(S2:X2)*12%,0)),"Employee without Y"),"Employee does not exists")

 

[grcshekar71]

Your understanding of concept i think is right. Just tried =IF(SUM(S2:X2)>15000,IF(15000*12%>=(SUM(S2:X2) - SUM(Y2:AD2)), 15000*12%,0),IF(SUM(S2:X2)*12%>=(SUM(S2:X2) - SUM(Y2:AD2)),SUM(S2:X2)*12%,0))

Sum of S2;X2 is 10000 sum of Y2:AD2 is 0. so this should calculate 10000*12% which it did not do

 

[grcshekar71]

Ok can you help me this way. Now this formula =IF((AND(SUM(S2:X2)<=15000, SUM(S2:X2)-SUM(Y2:AD2)>=SUM(S2:X2)*12%)), SUM(S2:X2)*12%, 0) works correctly. In the same forumula i Need to expand to check if it is SUM(S2:X2)>15000 then calculate SUM(S2:X2)*12% with other conditions being same

 

[grcshekar71]

Ok. I do understand. Now I wrote a formula like this

=IF((AND(SUM(S2:X2)<=15000, SUM(S2:X2)-SUM(Y2:AD2)>=SUM(S2:X2)*12%)), SUM(S2:X2)*12%, 0). This works the way i need it to. Now let us see this formula =IF((AND(SUM(S2:X2)>15000, SUM(S2:X2)-SUM(Y2:AD2)>=15000*12%)), 15000*12%, 0). Now I need to combine these two formula into one to make it work on both the conditions which ever is met subject to IF(INDEX(EmployeeMaster!O:O,MATCH(H2,EmployeeMaster!A:A,0))="Y",

 

[DanteAmor]

Sum of S2;X2 is 10000 sum of Y2:AD2 is 0. so this should calculate 10000*12% which it did not do

So your explanation is not clear.
You wrote this:

ELSE SUM(S2:X2)< 15000 THEN IF SUM(S2:X2)* 12% >=SUM(S2:X2) - SUM(Y2:AD2) then SUM(S2:X2)* 12% else 0

Continued with your example, that means:
SUM(S2:X2)* 12% = 10,000 * 12% = 1,200

SUM(S2:X2) - SUM(Y2:AD2)
10,000 - 0 = 10,000

1,200 >= 10,000 = False then result 0.

 

[grcshekar71]

Yes both IF Statement i have written to be combined so either of one IF is true it works else 0 on condition IF(INDEX(EmployeeMaster!O:O,MATCH(H2,EmployeeMaster!A:A,0))="Y",