Results 1 to 5 of 5

Modify this array formula to correct #Num error

Threaded View

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

    Modify this array formula to correct #Num error

    I'm creating a worksheet using excel 2007. In column A I've calendar weeks from 1 to 52. In column F I'm using user form to select an entry from the list. Now in order to do some calculation in column J, I've written an array
    formula. I'm trying to implement the following logic using this formula:

    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 F has occurred for the 1st time for similar value of column A
    Condition passes....
    Then, the value in Jx = ((20-Hx)/20)

    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 Jx = ((20-Hx)/20)

    Jx and Hx could be J2, H2 or J3, H3 etc. depending upon the calculation.

    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 F has occured earlier for similar value of column A
    Condition passes.....
    Then, the value in Jx = Jy-(Ix*100)

    Jy denotes the value of J where the Fx has occured last time.


    This is the formula which goes in J5. But it throws #NUM error.

    =IF(AND($A$2:$A4=A5,MATCH(F5,F:F,0)=ROW()),((20-H5)/20),INDEX(J$2:J4,SMALL(IF(F$2:F5=F5,ROW(F$2:F5)),COUNTIF(F$2:F5,F5)-1)-1,1)-I5)

    I've also attached the excel for reference.
    Attached Files Attached Files
    Last edited by daymaker; 07-13-2011 at 12:01 PM.

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