Help in correcting excel formula

[baidya91]

Can anyone help me correct the following formula:

=IF(C2>100,IF(T2="Change of Ropa",ROUNDUP(VLOOKUP(DATE(2006,1,1),$Q$2:$R$28,2,0)*1.86,-1)+G2),IF(C2>100,IF(OR(T2={"Superannuation","Appointment","Annual Increment","Enhancement of Qualification","10 Years' Benefit","18 Years' Benefit","20 Years' Benefit"}),CEILING(INT((R2-1)*1.03),10))))

When separated each of the following formulas work:
1

=IF(C2>100,IF(T2="Change of Ropa",ROUNDUP(VLOOKUP(DATE(2006,1,1),$Q$2:$R$28,2,0)*1.86,-1)+G2))

2

=IF(C2>100,IF(OR(T2={"Superannuation","Appointment","Annual Increment","Enhancement of Qualification","10 Years' Benefit","18 Years' Benefit","20 Years' Benefit"}),CEILING(INT(R1*1.03),10)))

But when combined, the first works but the second does not. Please help me.

 

[Zack Barresse]

What doesn't work? Have you tried stepping through it? Besides omitting your false statements, it looks syntactically correct.

 

[baidya91]

What doesn't work? Have you tried stepping through it? Besides omitting your false statements, it looks syntactically correct.

Result is giving false when combined..

 

[Fluff]

The combined formula will only move to the 2nd formula if C2 <= 100, but the 2nd formula starts with If(C2>100 which it never will be, hence you get false returned.

 

[baidya91]

The combined formula will only move to the 2nd formula if C2 <= 100, but the 2nd formula starts with If(C2>100 which it never will be, hence you get false returned.

How shall I have to combine the formula if I want to use C2>100 (it may any other value, i.e, C2 = "Revise"/or "Correction") as a common part of the two formulas?

 

[Fluff]

Maybe
=IF(C2<=100,"",IF(T2="Change of Ropa",ROUNDUP(VLOOKUP(DATE(2006,1,1),$Q$2:$R$28,2,0)*1.86,-1)+G2,IF(OR(T2={"Superannuation","Appointment","Annual Increment","Enhancement of Qualification","10 Years' Benefit","18 Years' Benefit","20 Years' Benefit"}),CEILING(INT(R1*1.03),10))))

But I don't understand why you are checking if C2 > 100 if it can be any value

 

[baidya91]

Maybe
=IF(C2<=100,"",IF(T2="Change of Ropa",ROUNDUP(VLOOKUP(DATE(2006,1,1),$Q$2:$R$28,2,0)*1.86,-1)+G2,IF(OR(T2={"Superannuation","Appointment","Annual Increment","Enhancement of Qualification","10 Years' Benefit","18 Years' Benefit","20 Years' Benefit"}),CEILING(INT(R1*1.03),10))))

But I don't understand why you are checking if C2 > 100 if it can be any value

Sir, I want to use a particular figure in place of 'C2<=100" and that is "$C$1&" "&C2 = "ROPA 2009". How can I put that?

 

[Fluff]

Just replace the C2<=100 with your criteria, except change the = to <>

 

[baidya91]

Just replace the C2<=100 with your criteria, except change the = to <>

Sir, don't mind if I am really bothering you.

I am again encroaching upon your valuable time by asking you:
My formula has two parts:
Part-I =IF(C2<=100,IF(OR(T2={"Appointment","Enhancement of Qualification","10 Years' Benefit","18 Years' Benefit","20 Years' Benefit","Change of Ropa"}),E2,IF(T2="Annual Increment",IF(R1< g1,min(g1,sum(r1+f1)),if(r1< i1,min(i1,sum(r1+h1)),if(r1< k1,min(k1,sum(r1+j1)),if(r1< m1,min(m1,sum(r1+l1)),if(af5< o1,min(o1,sum(r1+n1)),if(r1< p1,min(,sum(r1+o1)))))))))))))), where="" c2="81/90/98" (<100)


Part-II = IF(C2<=100,"",IF(T2="Change of Ropa",ROUNDUP(VLOOKUP(DATE(2006,1,1),$Q$2:$R$28,2,0)*1.86,-1)+G2,IF(OR(T2={"Superannuation","Appointment","Annual Increment","Enhancement of Qualification","10 Years' Benefit","18 Years' Benefit","20 Years' Benefit"}),CEILING(INT(R1*1.03),10)))), where C2 = 2009 in reality

I want to combine the parts into one formula but I cannot find way.

Please help me by giving a working solution.
Thanks in advance........ </k1,min(k1,sum(r1+j1)),if(r1<>

 

[baidya91]

Sorry to note that I cannot quote the first part completely:


IF(C2<=100,IF(OR(T2={"Appointment","Enhancement of Qualification","10 Years' Benefit","18 Years' Benefit","20 Years' Benefit","Change of Ropa"}),E2,IF(T2="Annual Increment",IF(R1

Please send your mail id so that I can send it completely....<g1,min(g1,sum(r1+f1)),if(r1<i1,min(i1,sum(r1+h1)),if(r1<k1,min(k1,sum(r1+j1)),if(r1<m1,min(m1,sum(r1+l1)),if(af5<o1,min(o1,sum(r1+n1)),if(r1
</g1,min(g1,sum(r1+f1)),if(r1<i1,min(i1,sum(r1+h1)),if(r1<k1,min(k1,sum(r1+j1)),if(r1<m1,min(m1,sum(r1+l1)),if(af5<o1,min(o1,sum(r1+n1)),if(r1