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.

 

[MARK858]

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

Just to help, below is the complete first part

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 or 90 or 98 (<100)

 

[Fluff]

I don't see how you can combine them as they are contradictory.
1 formula is saying if T2= "Appointment" then return E2
the other is saying to return the result of the CEILING formula

 

[baidya91]

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

This is correct input.......

 

[baidya91]

Just to help, below is the complete first part



where C2 = 81 or 90 or 98 (<100)

Yes Sir

 

[baidya91]

Yes, Sir

 

[baidya91]

I don't see how you can combine them as they are contradictory.
1 formula is saying if T2= "Appointment" then return E2
the other is saying to return the result of the CEILING formula

Sir, I have eliminated the option of returning to E2 and revised the parts as follows:
Part - I:= <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)
IF(OR($C$1&" "&C2={"ROPA 81","ROPA 90","ROPA 98"}),IF(OR(T2={"Annual Increment","10 Years' Benefit","18 Years' Benefit","20 Years' Benefit"}),IF(R1<G1,MIN(G1,SUM(R1+F1))...IF(R1<P1,MIN(,SUM(R1+O1)

Part - II:= <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
IF($C$1&" "&C2<>"ROPA 2009","",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","10 Years' Benefit","18 Years' Benefit","20 Years' Benefit"}),CEILING(INT(R1*1.03),10))))))))))))).

But still it does not work with the second part. Can you sort out the problem?</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<p1,min(,sum(r1+o1)

 

[baidya91]

This is correct input.......

Can anyone help me revising the formula....