Solving for 1st Occurrence in a row

[dj1cincy]

I am trying to display the number in the cell above the row of percentages that 1st hit above a particular percent.

[TABLE="width: 640"]
<colgroup><col width="64" span="10" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]1[/TD]
[TD="class: xl63, width: 64"]1[/TD]
[TD="class: xl63, width: 64"]2[/TD]
[TD="class: xl63, width: 64"]2[/TD]
[TD="class: xl63, width: 64"]3[/TD]
[TD="class: xl63, width: 64"]3[/TD]
[TD="class: xl63, width: 64"]4[/TD]
[TD="class: xl63, width: 64"]4[/TD]
[TD="class: xl63, width: 64"]5[/TD]
[TD="class: xl63, width: 64"]5[/TD]
[/TR]
[TR]
[TD="class: xl64"]1.05% [/TD]
[TD="class: xl64"](1.01%)[/TD]
[TD="class: xl64"]0.90% [/TD]
[TD="class: xl64"](0.85%)[/TD]
[TD="class: xl64"]1.30% [/TD]
[TD="class: xl64"](0.75%)[/TD]
[TD="class: xl64"]1.62% [/TD]
[TD="class: xl64"](0.38%)[/TD]
[TD="class: xl64"]1.75% [/TD]
[TD="class: xl64"](0.47%)
[/TD]
[/TR]
</tbody>[/TABLE]

If I want to see the first time the percent is above 1.50%, how do I do this? The result of the formula should be 4 in this example.

Thank you

 

[Scott T]

=INDEX(A1:J1,SMALL(IF(A2:J2>=1.5%,COLUMN($A$1:$J$1)-COLUMN($A$1)+1),1))

This is an array formula and must be committed with CTRL+SHIFT+ENTER not just enter. If done correctly you will see {} around the formula in the formula bar.

 

[sheetspread]

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	1	2	2	2	3	3	4	4	5	5
2	1.05%	-1.01%	0.90%	-0.85%	1.30%	-0.75%	1.62%	-0.38%	1.75%	-0.47%
3
4	4
Sheet7

Cell Formulas
Range	Formula
A4	{=INDEX($A$1:$J$1,MATCH(TRUE,$A$2:$J$2>0.015,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

works also

 

[dj1cincy]

Excel 2010

	A	B	C	D	E	F	G	H	I	J

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]5[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1.05%[/TD]
[TD="align: right"]-1.01%[/TD]
[TD="align: right"]0.90%[/TD]
[TD="align: right"]-0.85%[/TD]
[TD="align: right"]1.30%[/TD]
[TD="align: right"]-0.75%[/TD]
[TD="align: right"]1.62%[/TD]
[TD="align: right"]-0.38%[/TD]
[TD="align: right"]1.75%[/TD]
[TD="align: right"]-0.47%[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet7​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A4[/TH]
[TD="align: left"]{=INDEX($A$1:$J$1,MATCH(TRUE,$A$2:$J$2>0.015,0))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



works also

This works really well. Thank you for the formula. I do have one additional question. Is there a way to return a number other than the column number if the criteria is not true? For instance, if the highest number in the row is 5 and we are solving doe greater than 10, can we return a specific number we place in the formula, for instance 100?

 

[sheetspread]

This?

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	1	2	2	2	3	3	4	4	5	5
2	1.05%	-1.01%	0.90%	-0.85%	1.30%	-0.75%	1.62%	-0.38%	1.75%	-0.47%
3
4	100
Sheet7 (2)

Cell Formulas
Range	Formula
A4	=IF(MAX($A$1:$J$1)<10,100)

 

[Rick Rothstein]

This works really well. Thank you for the formula. I do have one additional question. Is there a way to return a number other than the column number if the criteria is not true? For instance, if the highest number in the row is 5 and we are solving doe greater than 10, can we return a specific number we place in the formula, for instance 100?

Give this array-entered** formula a try...

=MIN(IF(A2:J2>0.015,A1:J1,100))

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

 

[sheetspread]

I might have misread the requirements so here's another version to consider:

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	1	2	2	2	3	3	4	4	5	5
2	1.05%	-1.01%	0.90%	-0.85%	1.30%	-0.75%	1.62%	-0.38%	1.75%	-0.47%
3
4	4
5
6
7	1	2	2	2	3	3	4	4	5	5
8	1.05%	-1.01%	0.90%	-0.85%	1.30%	-0.75%	1.62%	-0.38%	1.75%	-0.47%
9
10	100
Sheet7

Cell Formulas
Range	Formula
A4	{=IFERROR(INDEX($A$1:$J$1,MATCH(1,1/($A$2:$J$2>0.015),0)),100)}
A10	{=IFERROR(INDEX($A$1:$J$1,MATCH(1,1/($A$2:$J$2>0.02),0)),100)}
Press CTRL+SHIFT+ENTER to enter array formulas.