Trying to find the second occurrence in a row.

[JoeSw]

Hi there, I've had a problem where I have a row of percentages and I need to find the second occurrence of a percentage that is under 40%. E.g. if I have two rows like this
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]Jan[/TD]
[TD="align: center"]Feb[/TD]
[TD="align: center"]March[/TD]
[TD="align: center"]April[/TD]
[TD="align: center"]May[/TD]
[/TR]
[TR]
[TD="align: center"]56%[/TD]
[TD="align: center"]44%[/TD]
[TD="align: center"]39%[/TD]
[TD="align: center"]25%[/TD]
[TD="align: center"]99%[/TD]
[/TR]
</tbody>[/TABLE]



Then I would like it to return April. I have found lots of ways for finding a second occurrence for rows but not for columns.


Bonus question: I was also wondering if anyone smarter than me could work out a good way to find out if there was an instance of three months in a row where the percentage was 40% above the instance of the percentage you have found previously that is found below 40%.

e.g. with the example above if you find the 2nd instance of a percentage that is below 40% which would be April (23%) then it would search down the row for an instance where there were three percentages in a row that were at least 63% or above.

Thanks so much!

Joe

 

[Ron Coderre]

With your sample data in A1:E2
and
G2: an upper limit...... 40%

Here are two approaches:
ARRAY FORMULA, completed by holding down CTRL and SHIFT when you press ENTER (insted of just pressing ENTER)

H2: =INDEX(A1:E1,SMALL(IF(A2:E2 < G2,COLUMN(A2:E2)),2))<g2,column(a2:e2)),2))[ code]

Regular formula (no C+S+E)

H2: =INDEX(A1:E1,SMALL(INDEX(COLUMN(A2:E2)*(A2:E2 < G2),0),2+COUNTIF(A2:E2,">"&G2)))<g2),0),2+countif(a2:e2,">

Remove the spaces from those formulas....This forum mis-interprets the original formulas when the spaces are not posted.
Does that help</g2),0),2+countif(a2:e2,"></g2,column(a2:e2)),2))[>

 

[Peter_SSs]

For the same set-up described by Ron, another standard-entry formula is

=INDEX(A$1:E$1,AGGREGATE(15,6,(COLUMN(A2:E2)-COLUMN(A2)+1)/(A2:E2<G2),2))

 

[JoeSw]

Hi,

Thanks both of you. So I have tried to use both of your suggestions but it still responded with an erroneous month so I was thinking of giving you the exact data that I am working with. There are some blanks so I am not sure if that is what is throwing the formulae off. Do you know if your suggestions look from left to right or from right to left?

Example:

[TABLE="width: 150"]
<tbody>[TR]
[TD]Aut 12[/TD]
[TD]Spr 13[/TD]
[TD]Sum
13[/TD]
[TD]Aut
13[/TD]
[TD]Spr
14[/TD]
[TD]Sum
14[/TD]
[TD]Aut
14[/TD]
[TD]Spr
15[/TD]
[TD]Sum
15[/TD]
[TD]Aut
15[/TD]
[TD]Spr
16[/TD]
[TD]Sum
16[/TD]
[TD]Aut
16[/TD]
[TD]Spr
17[/TD]
[TD]Sum
17[/TD]
[TD]Aut 17[/TD]
[TD]Spr 18[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]10.4%[/TD]
[TD]11.9%[/TD]
[TD]7.8%[/TD]
[TD]57.9%[/TD]
[TD]100%[/TD]
[TD]100%[/TD]
[TD]100%[/TD]
[TD]100%[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


So with the working formula I would expect it to bring Aut14 back as the second month below 40%.

Thanks again,

Joe

 

[Peter_SSs]

There are some blanks ..

That does make a diffence because blanks are treated as zero, which of course is less than 40%.

Try this version of my suggestion.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Aut 12	Spr 13	Sum 13	Aut 13	Spr 14	Sum 14	Aut 14	Spr 15	Sum 15	Aut 15	Spr 16	Sum 16	Aut 16	Spr 17	Sum 17	Aut 17	Spr 18
2						10.40%	11.90%	7.80%	57.90%	100%	100%	100%	100%
3
4	Limit	40%
5	2nd Col	Aut 14
2nd occurrence

 

[JoeSw]

That does make a diffence because blanks are treated as zero, which of course is less than 40%.

Spreadsheet Formulas

Cell Formula B5 =INDEX(A$1:Q$1,AGGREGATE(15,6,(COLUMN(A2:Q2)-COLUMN(A2)+1)/((A2:Q2<B4)*(A2:Q2<>"")),2)) <tbody> </tbody>

<tbody>
</tbody>

Dear Peter,

Thank you so much, it works perfectly. I wish I could say I understood it however, I at least understand how to use it!

 

[Peter_SSs]

Thank you so much, it works perfectly.

That's the main thing. Thanks for letting us know.