I'm creating a worksheet using excel 2007. In column A I've calendar weeks from 1 to 52. In column E I'm using data validation to select an entry from the list. Now in order to do some calculation in column N, I've written an array formula. I'm trying to implement the following logic using this formula -" =IF(AND($A$2:$A2=A3,MATCH(E3,E:E,0)=ROW()),(100-((SUM($M$2:$M3)*100))/100),INDEX(N$2:N2,SMALL(IF(E$2:E3=E3,ROW(E$2:E3)),COUNTIF(E$2:E3,E3)-1)-1,1)-M3)".
For N3:
IF(A3=A2) && the occurrence of an entry in E3 is for the first time THEN (N3=((100-((M2+M3)*100))/100))
ELSE N3=Nn - M3.
Here, Nn denotes the last occurrence of the an entry displayed E3.
For N4:
IF(A4=A3 & A3=A2) && the occurrence of an entry in E4 is for the first time THEN (N4=((100-((M2+M3+M4)*100))/100))
ELSE N4=Nn - M4.
Similarly,
For N5:
IF(A5=A4 & A4=A3& A3=A2) && the occurrence of an entry in E5 is for the first time THEN (N5= ((100-(M2+M3+M4+M5)*100))/100))
ELSE N5=Nn - M5.
and so on...
But there seems to be a problem. In E2 I've selected an entry, the result is fine and it shows the correct result 75%. But in E3, If I select a different entry other than the one selected in E2, then the result in N3 is diplayed as 50%.
This is incorrect as according to the calculation it should show 75%. The result 50% in N3 is correct only if E2=E3.
I've attached the excel for reference. Could you please check and tell me what's wrong with my formula?
Bookmarks