Consulting

Results 1 to 4 of 4

Thread: Solved: Need to correct this excel formula immediately

  1. #1

    Solved: Need to correct this excel formula immediately

    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.

  2. #2
    VBAX Expert shrivallabha's Avatar
    Joined
    Jan 2010
    Location
    Mumbai
    Posts
    750
    Location
    No you haven't! There's no attachment
    Regards,
    --------------------------------------------------------------------------------------------------------
    Shrivallabha
    --------------------------------------------------------------------------------------------------------
    Using Excel 2016 in Home / 2010 in Office
    --------------------------------------------------------------------------------------------------------

  3. #3
    Have attached the excel now.
    Attached Files Attached Files

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,453
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •