Modify this array formula to correct #Num error

[sunny17]

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)

 

[moonfish]

I've tried to reproduce your source data but to do so I have to make too many assumptions. I'll go with a general advice:

Have you tried to evaluate the formula to locate the cause of the error? Select J5 and go to Formulas > Formula Auditing > Evaluate Formula. The calculation step that produces your error for the first time is most probably the cause of your troubles.