Modify this complex formula using IF THEN ELSE

[sunny17]

I need to modify a complex formula using IF-THEN-ELSE that fits my requirment.

In column I am using the formula "=((H2/20))" for calculating individual effort.
Now, in column J, I need to display the remaining effort.
For this I'm using the formula, "=(20-(COUNTIF($A$2:A2,A2)=COUNT($A$2:A2))*SUM($H1:H$2)-H2)/20"
in J3 & "=((20-H2)/20)" in J2, which needs to be modified a bit.

The logic for the formula has to be :

For J3:
IF(A3=A2) && the occurrence of an entry in F3 is for the first time THEN (J3=(20-(G2+G3))/20)
ELSE J3=Jn - I3.

Here, Jn denotes the last occurence of the an entry displayed F3.

For J4:

IF(A4=A3 & A3=A2) && the occurrence of an entry in F4 is for the first time THEN (J4=(20-(G2+G3+G4))/20)
ELSE J4=Jn - I4.

Similarly,
For J5:

IF(A5=A4 & A4=A3& A3=A2) && the occurrence of an entry in F5 is for the first time THEN (J5= (20-(G2+G3+G4))/20)
ELSE J5=Jn - I5.

and so on...

I've attached the excel for reference.

[NBVC]

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

Thread Closed.

[NBVC]

This is a duplicate post and as such does not comply with Rule 5 of our forum rules. This thread will now be closed, you may continue in your other thread.

Thread Closed.