Hello,
I've created an excel worksheet. In the column F, I've a user form which will help me to select one or more entry and display in a cell in column F.
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.
Now, lets say I've selected abc, xyz in F3:
Then in I3 results should be displayed as - "abc: 25%, xyz: 25%"
In J3, the results should be displayed as - "abc:75%, xyz :75%".
Now, again if I select abc, lmn in F4:
Then in I4 results should be displayed as "abc:25%, lmn:25%"
In J4, the results should be displayed as "abc: 50%, lmn :75%".
Please note, since in J3: the remaining effort for abc was 75% and in I4 the effort required was 25%. Hence the calculated result in J4 (I4-J3).
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 (I4=(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 (I5= (20-(G2+G3+G4))/20)
ELSE J5=Jn - I5.
and so on...
I've attached the excel for your reference.
Bookmarks