HELP with Comparing Odd and Even rows

[kuanthye]

dear all,

i have been looking for solutions for my previous excel problems in this forum, and i must say i can find lots of info...thanks to all who have contributed!

however, i have this problem which i am not able to solve...hope u guys can help

I have the following range:

Column A
1 (row 1)
1000 (row 2)
1000 (row 3)
1002 (row 4)
1001 (row 5)
1003 (row 6)
1004 (row 7)
1005 (row 8)
(row 9)

Is there a formula (or UDF) to compare a row (A2 and A3+A1), (A4 and A5+A1), (A6 and A7+A1), (A8 and A9+A1)? The objective is to find how many values in the odd are less than or equal to the corresponding values in the preceding even cell.

The logic is something like this:

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
For the selected range in column A starting from A2
If row is (not empty and it's an integer)
If row is even row
If (value in nextrow + value in A1) <= (value in row)
count = count + 1
End if
End if
End if
Increment next row

Return count
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

your help is very much appreciated. Thanks in advance!

 

[Rick Rothstein]

Does this formula yield the result you want...

=SUMPRODUCT(--(A3:A1000+A1<=A2:A999)*(A3:A1000<>""))

Note that the two ranges both contain the same number of cells, but they are offset by one row from each other... important to keep that in mind for when you expand them to cover your actual data.

 

[Marcelo Branco]

Hi,

Welcome to the forum!

One question: is 1 the expected result with your data-sample? (only row 4 satisfies the condition)

If so, maybe this
=SUMPRODUCT(--(MOD(ROW(A2:A8),2)=0),--(A3:A9+$A$1<=A2:A8),--(A3:A9<>""))

M.

 

[kuanthye]

Dear Rick,

The formula does not quite yield the desired result..But thanks nevertheless


Dear Marcelo,

The ‘1’ is not the expected result, but rather a constant which is to be added to the even row value…the expected result is to be input in row 10.
The formula works!! Thank u very much


Best regards,
Kuan Thye

 

[Marcelo Branco]

Dear Marcelo,

The ‘1’ is not the expected result, but rather a constant which is to be added to the even row value…the expected result is to be input in row 10.
The formula works!! Thank u very much


Best regards,
Kuan Thye

Kuan,

You are welcome and tks for the feedback

M.
ps: i understand that the '1' is a constant. I was only asking, for checking purpose, if with the data-sample you posted in #1 the expected (correct)result is 1.