PDA

View Full Version : Solved: Need to correct this excel formula immediately



ansh017
07-31-2011, 08:29 AM
I need to modify a complex array 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(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%).
I feel there's something wrong with countif logic in the formula.
Could you help me correct this?

I've attached the excel for reference.

shrivallabha
07-31-2011, 09:08 AM
No you haven't! There's no attachment

ansh017
07-31-2011, 08:57 PM
Have attached the excel now.

Bob Phillips
08-01-2011, 12:25 AM
Your data makes no sense. Column E never matches column A, theye are different data types.

Haven't you posted similar questions before under a different name? I recognise that input form for column F.