Depending on next cell value formula

[rnd37]

Hi.
In A2:AO101 I have numbers from 1 to 100
Please, help me to create a formula for AQ2 and down which would do following:
Look at the last cell of the range A2:AO2 which is AO2 and return 0 if the value is < 41;
if the value of AO2 is > 40
then find the same value in that range (could be more than one or not present)
- return 0 if same value is not present in the row
- return 0 if the right side next value is < 41 at least one time
- return 1 if the right side next value is >40

Here are short examples:

12 67 43 2 86 43 90 7 3 77 15 31 67 22 43 78 55 - 0 (value 55 is present only in the last column)

75 6 37 91 65 49 6 82 22 32 76 32 75 87 11 99 6 - 0 ( found less than 41 value right after 6 (37) and the value 6 itself is not greater than 40 )

53 29 8 61 53 1 16 98 29 53 41 37 52 33 19 61 61 - 1 ( all values right after 61 are greater than 40)

Thanks.

[Andrew-R]

I'm not entirely sure I understand your requirements, but I think the formula you want is:

=IF(AO2<41,0,IF(COUNTIF(A2:AO2,AO2)=1,0,IF(MIN(IF(A2:AN2=AO2,OFFSET(A2:AN2,0,1)))<41,0,1)))

This is an array formula, so you must use Ctrl-Shift-Enter to enter it into the cell, not just enter. When you do this Excel will put curly brackets around the formula to show it is an array.

[rnd37]

Works great.
Thanks a lot!