+ Reply to Thread
Results 1 to 1 of 1

Need to modify 2 complex formulas using IF, THEN, ElSE

  1. #1
    Registered User
    Join Date
    06-16-2011
    Location
    Bangalore
    MS-Off Ver
    Excel 2007
    Posts
    40

    Need to modify 2 complex formulas using IF, THEN, ElSE

    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.
    Attached Files Attached Files
    Last edited by daymaker; 08-02-2011 at 12:32 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1