Modify this complex formula using IF THEN ELSE - MACROS usage

[sunny17]

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

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

 

[jasonb75]

Welcome to the board!

Not sure if I'm following your logic correctly, is

=if(and(countif($A$2:$A3,$A$2)=rows($A$2:$A3),countif($F$3:$F3,$F3)=1),(20-sum($G$2:$G3))/20,lookup(2,1/($f$2:$F2=$F3)))

anywhere close to the expected result?

 

[sunny17]

Hello Jason,
Thanks a lot for the help.I've tried using the formula suggested by you. But the in J3 it shows 90%.
According to the requirement, it should actually show 0% in J3 after the calculation. The reason being the same tester (in column F) has been booked in the previous row for 10 screens for which effort required and remaining are 50%. Now, if I use the same data in next row, the result in I3 is 50% which is correct. However, in J3 it shows 90%, while the expected result has to 0%.
"Jn denotes the last occurence of the entry displayed in F3." I agree I've not specified the requirement correctly over here.
I would try to make it more clear with the example shown in the excel attached.
Now, I've selected the same entry both in F2&F3. So when I say Jn denotes the last occurence of the entry displayed in F3, it means the entry in F3(Havva) has occured in the data cell F2 of the previous. In that it should take J2 into consideration to calculate the remaining effort in J3.
So, in J2 it's 50%. Thus is J3 it has to be (J2-I3) = 50-50 = 0%.
Hope you've understood my requirement now.

Also, could you please tell me how to upload a file here? I would try to attach the excel which would explain the requirement clearly?
I've anyhow inserted a hyperlink from where you can download the file and check. http://www.4shared.com/document/PRK6Kp-U/abc1.html

Thanks & Regards,
Sunny

 

[jasonb75]

Hi Sunny,

For security reasons, the majority of forum users are either unable, or unwilling to download any files posted on share sites.

You can't upload a file directly to the forum either, only post (small) samples to the board with excel jeanie http://www.excel-jeanie-html.de/html/down_en.php html maker http://cid-8cffdec0ce27e813.office.live.com/browse.aspx/MrExcel or copy & paste directly from excel http://www.mrexcel.com/forum/showpost.php?p=2198045&postcount=2

The easiest way to make clear what you need is to use one of those methods to post a sample of around 10 rows of data, (using fictional aliases if your data is sensitive), hide any columns that have no relevance to the question before copying, and remember to manually enter your expected results in the relevant places.

 

[sunny17]

Thanks Jason. I've got the solution