Problems with nested IF functions

[CrampedHands]

Hi, I've poked around here a few times when I've need some help but couldn't find the answer to this one lurking in the shadows.

I'm using the formula, =IF(A2=9,A3,IF(A3>9,A4,IF(A4>9,A5,IF(A5>9,A6,IF(A6>9,5))))) to get the 2nd row to look like the one in the last sample. When I run the formula it doesn't make it past the first row, I've tried everything I can think of.The "9" and "5" is just to give me some values to sort with.

Thanks in advance!! I've been wasting some serious time on this...

Excel Workbook
	A	B
1	Customer	form2
2	9
3	9
4	9
5	9
6	1000 ABC INC
7	9
8	9
9	1001 ABEL
10	9
11	1002 ACT INC
12	9
13	1003 ADM CO
Sheet1

Excel Workbook
	A	B
1	Customer	form2
2	9	9
3	9	9
4	9	9
5	9	1000 ABC INC
6	1000 ABC INC	9
7	9	9
8	9	1001 ABEL
9	1001 ABEL	9
10	9	1002 ACT INC
11	1002 ACT INC	9
12	9	1003 ADM CO
13	1003 ADM CO	9
Excel 2010 Failed Example

Excel 2010

Cell Formulas
Range	Formula
B2	=IF(A2=9,A3,IF(A3>9,A4,IF(A4>9,A5,IF(A5>9,A6,IF(A6>9,5)))))
B3	=IF(A3=9,A4,IF(A4>9,A5,IF(A5>9,A6,IF(A6>9,A7,IF(A7>9,5)))))
B4	=IF(A4=9,A5,IF(A5>9,A6,IF(A6>9,A7,IF(A7>9,A8,IF(A8>9,5)))))
B5	=IF(A5=9,A6,IF(A6>9,A7,IF(A7>9,A8,IF(A8>9,A9,IF(A9>9,5)))))
B6	=IF(A6=9,A7,IF(A7>9,A8,IF(A8>9,A9,IF(A9>9,A10,IF(A10>9,5)))))
B7	=IF(A7=9,A8,IF(A8>9,A9,IF(A9>9,A10,IF(A10>9,A11,IF(A11>9,5)))))
B8	=IF(A8=9,A9,IF(A9>9,A10,IF(A10>9,A11,IF(A11>9,A12,IF(A12>9,5)))))
B9	=IF(A9=9,A10,IF(A10>9,A11,IF(A11>9,A12,IF(A12>9,A13,IF(A13>9,5)))))
B10	=IF(A10=9,A11,IF(A11>9,A12,IF(A12>9,A13,IF(A13>9,A14,IF(A14>9,5)))))
B11	=IF(A11=9,A12,IF(A12>9,A13,IF(A13>9,A14,IF(A14>9,A15,IF(A15>9,5)))))
B12	=IF(A12=9,A13,IF(A13>9,A14,IF(A14>9,A15,IF(A15>9,A16,IF(A16>9,5)))))
B13	=IF(A13=9,A14,IF(A14>9,A15,IF(A15>9,A16,IF(A16>9,A17,IF(A17>9,5)))))

Excel Workbook
	A	B
1	Customer	form2
2	9	1000 ABC INC
3	9	1000 ABC INC
4	9	1000 ABC INC
5	9	1000 ABC INC
6	1000 ABC INC	5
7	9	1001 ABEL
8	9	1001 ABEL
9	1001 ABEL	5
10	9	1002 ACT INC
11	1002 ACT INC	5
12	9	1003 ADM CO
13	1003 ADM CO	5
Desired Example

Excel 2010

Cell Formulas
Range	Formula
B5	=IF(A5=9,A6,IF(A6>9,A7,IF(A7>9,A8,IF(A8>9,A9,IF(A9>9,5)))))

 

[circledchicken]

Hi,

Here's an option to try (in B2 and drag down):

=IF(ISNUMBER(A2),INDEX(A2:$A$13,MATCH(0,--ISNUMBER(A2:$A$13),0)),5)

or

=IF(A2=9,INDEX(A2:$A$13,MATCH(1,--(A2:$A$13<>9),0)),5)

These are array formulae, so use CTRL-SHIFT-ENTER rather than just ENTER.

 

[jbeaucaire]

Odd formula, but once you copy it all the way down it will present properly.

Excel Workbook
	A	B
1	Customer	form2
2	9	1000 ABC INC
3	9	1000 ABC INC
4	9	1000 ABC INC
5	9	1000 ABC INC
6	1000 ABC INC	5
7	9	1001 ABEL
8	9	1001 ABEL
9	1001 ABEL	5
10	9	1002 ACT INC
11	1002 ACT INC	5
12	9	1003 ADM CO
13	1003 ADM CO	5
Sheet2

 

[CrampedHands]

Awesome!! That worked! Thanks jbeaucaire.

I tried the other formula as well by I must admit I don't even know what an array formula is. I can only do the basics...

I've got to watch some YouTube videos or something, I was stuck on that the whole day...

 

[circledchicken]

That is a really cool formula from jbeaucaire - I like it!

Haha ok - here's a very good tutorial for array formula's if it helps:
http://www.youtube.com/view_play_list?p=007E7E9CA63304D3