I need to modify 2 complex formulas:
1st formula Logic:
For I4:
IF(A4=A3 & A3=A2) then (L4= (220-(H2+H3+H4))/220) Else (L4=((220-H4)/220))
Similarly,
For L5:
IF(A5=A4 & A4=A3& A3=A2) then (L5= (220-(H2+H3+H4+H5))/220) Else (L5=((220-H5)/220))
and so on...
Here, in L13, it doesn't give me the expected result (81.82%).
Please note that the value in col A is 2 in both A13 and A12. Hence it should only consider H12 and H13 for calculation and not the previous values of H.
In simpler words, when the value in A changes, the calculation should start from the scratch i.e it should use the formula entered in L2.
2nd formula Logic:
1st condition:
if the value in column A matches with the value of column A in previous row;
Condition passes....
Then check
if the value in Column E has occurred for the 1st time for similar value of column A
Condition passes....
Then, the value in Nx = 100 - (Mx*100)
2nd Condition:
if the value in column A is not equal to the value of column A in previous row;
Condition passes.....
Then , the value in Nx = 100 - (Mx*100)
3rd Condition:
if the value in column A matches with the value of column A in previous row;
Condition passes....
Then check
if the value in column E has occured earlier for similar value of column A
Condition passes.....
Then, the value in Nx = Ny-(Mx*100)
Here,
Ny denotes the value of N where the Ex has occured last time.
and so on...
To implement this logic I've the formula "==IF(AND($A$2:$A12=A13,MATCH(E13,E:E,0)=ROW()),((100-(M13*100))/100),INDEX(N$2:N12,SMALL(IF(E$2:E13=E13,ROW(E$2:E13)),COUNTIF(E$2:E13,E13)-1)-1,1)-M13)" in N13.
But over here, the formula doesn't give me the expected result (75%).
Could you help me correct this?
I've attached the excel for reference.
Bookmarks