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.

Not to clear on your expected results.

See if this in J2 works for you.

Please Login or Register  to view this content.

Drag/Fill Down.

Hope this helps.

There seems to be inconsistencies...

AND($A$2:$A4=A5,....)... checks each of A2:A4 for a match (when in an array formula)... is that want you want or do you want just AND($A4=A5,.....)

Condition 1 and condition 2 seem to result in same outcome, Jx = ((20-Hx)/20) even though first you say "if the value in column A matches with the value of column A in previous row" and "if the value in column A is not equal to the value of column A in previous row".. which are opposite conditions....

This 3rd condition formula: Jx = Jy-(Ix*100) doesn't seem to appear in your final formula.

A5 in your worksheet is not equal to anything prior, so what is the expected outcome in J5? and why?

So please review your conditions, and then perhaps show some expected results so we can better assist you.

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

Read this to understand why we ask you to do this, then please add links to your crossposts.