array iteration problem

[r1998]

Dear friends and respected seniors,
I have the following data in column A, starting from A2
100
200
250
300
100
350
550
400

What I am trying to do is I compare A2 with A3 . If A3<=A2+100 , then compare A2 with A4 and so on......
If A3>=A2+100 , use that cell instead of A2 and compare with the lower cells.....means compare A3 and A4 and so on
i have used this formula in column B

= IF( A3>=A2+100,A3,"")

and this in column c

= IF( A3>=$A$2+100,A3,"")

but i am getting incorrect results.
this is the link of screenshot of my excel sheet
http://tinypic.com/r/24o0j6a/9

I have manually entered the required results in column D.

Can anyone please kindly guide me as to how I can achieve the results of column D.
Awaiting your replies.
Thank you.

 

[r1998]

This is what I want in column D, just in case someone doesnt view the screenshot
d2=" "
d3=200
d4=" "
d5=300
d6=" "
d7=" "
d8=550
d9=" "

Thank you

 

[Aladin Akyurek]

What is the reasoning behind 300 in D5, that is, how does that obtain? Please use words, not Excel formulas in your explanation.

 

[r1998]

comparing A3 and A4, then A3 and A5, A5 is more than or equal to A3 plus 100, hence d5 will have value of A5

 

[Peter_SSs]

Welcome to the MrExcel board!

In D3, copied down.

Excel Workbook
	A	B	C	D
1
2	100
3	200			200
4	250
5	300			300
6	100
7	350
8	550			550
9	400
Sheet5

 

[r1998]

Thank you Aladin Akyurek for replying and thank you Peter_SSs for welcoming and for the perfect answer it worked perfectly Thanks

 

[Peter_SSs]

You are very welcome. Thanks for the follow-up.

 

[r1998]

Hello again
I have installed excel 2003 and excel 2010, but I am learning from this book "John Walkenbach-Excel 2003 Formulas-For Dummies (2003)" hence mostly I use excel 2003.
This formula worked perfectly in excel 2010, but its giving error in excel 2003 because of the IFERROR function

[COLOR=#000000][FONT=Arial]=IF(A3>=IFERROR[/FONT][/COLOR][COLOR=#000000][FONT=Arial](LOOKUP[COLOR=#0000FF](9.99E+307,D$2:D2,A$2:A2)[/COLOR],A$2)[/FONT][/COLOR][COLOR=#000000][FONT=Arial]+100,A3,"")[/FONT][/COLOR]

How can I get this formula to work in excel 2003 too
Awaiting your replies.
Thanks.

 

[Aladin Akyurek]

Let's try:

=IF(A3>=IF(ISNUMBER(LOOKUP(9.99E+307,D$2:D2,A$2:A2)),LOOKUP(9.99E+307,D$2:D2,A$2:A2),A$2)+100,A3,"")

 

[Peter_SSs]

How can I get this formula to work in excel 2003 too

I would use this.

Excel Workbook
	A	B	C	D
1
2	100
3	200			200
4	250
5	300			300
6	100
7	350
8	550			550
9	400
Sheet5