daymaker
09-19-2011, 11:45 PM
I need to modify a complex formula.
Here's the 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...
Please note that if value in A changes it should not take into consideration any values of E, M or N etc for any other values of A.
To implement this logic I've the formula
=IF(A20="","",IF(SUMPRODUCT(--($A$2:$A20&$E$2:$E20=A20&E20))=1,(100-(M20*100)),(100-(SUMIF($E$2:$E20,E20,$M$2:$M20))*100)))/100
in N20.
But over here, the formula doesn't give me the expected result (50%). Instead it shows -100%.
Could you help me correct this?
I've attached the excel for reference.
Here's the 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...
Please note that if value in A changes it should not take into consideration any values of E, M or N etc for any other values of A.
To implement this logic I've the formula
=IF(A20="","",IF(SUMPRODUCT(--($A$2:$A20&$E$2:$E20=A20&E20))=1,(100-(M20*100)),(100-(SUMIF($E$2:$E20,E20,$M$2:$M20))*100)))/100
in N20.
But over here, the formula doesn't give me the expected result (50%). Instead it shows -100%.
Could you help me correct this?
I've attached the excel for reference.