Finding second positive number in row

[Tdorman]

The formula I used below allows me to find the first positive number in the row. I cant seem to get it to help me find the second positive number after. I would also need it to find the third, fourth, fifth, and so on.

=INDEX($J$8:$S$8,MATCH(1,INDEX(--($J$8:$S$8>0),1,0),0))

 

[Fluff]

Are you still using xl2016?

 

[Tdorman]

Yes

 

[Fluff]

Ok, how about

Fluff.xlsm
	J	K	L	M	N	O	P	Q	R	S
8	-1	2	-5	3	6	4	-5	-6	-4	10
9	2	3	6	4	10
Data

Cell Formulas
Range	Formula
J9:O9	J9	=IFERROR(INDEX($J$8:$S$8,AGGREGATE(15,6,(COLUMN($J$8:$S$8)-COLUMN($J$8)+1)/($J$8:$S$8>0),COLUMNS($J9:J9))),"")

 

[Tdorman]

Sorry for the confusion, below is the table that it needs to go into. The amounts below the year are what I'm looking to carry down. The first positive, in this case 1,000,000 goes against the first negative amount, (33,688,598). There's still an amount left over after the 1,000,000 is netted against that amount so the second positive amount, or 5,000,000, can go against that as well. These will be listed in the same column, so the 5,000,000 will go below the 1,000,000 in column J

CBT-100U Workpapers 6-5TEST.xlsm
	J	K	L	M	N	O	P	Q	R	S
5
6	2009	2010	2011	2012	2013	2014	2015	2016	2017	2018
7
8	(33,688,598)	(29,587,046)	1,000,000	(28,027,007)	(15,752,937)	(28,579,386)	5,000,000	(50,355,149)	(43,146,535)	(40,980,658)
9	1,000,000
10
11
12
13
14
15
16
NOL-TEMPLATE

Cell Formulas
Range	Formula
J8:K8,Q8:S8,M8:O8	K8	=-(IFERROR(INDEX('DATA 500U-P-22'!$G$12:$G$3000,MATCH(1,INDEX(('DATA 500U-P-22'!$C$12:$C$3000=$A$6)*(YEAR('DATA 500U-P-22'!$F$12:$F$2000)=K6),0),0),0),0))
J9	J9	=INDEX($J$8:$S$8,MATCH(1,INDEX(--($J$8:$S$8>0),1,0),0))

 

[awoohaw]

I can never seem to get AGGREGATE to work for me like Fluff can. But, this is what I cam up with:

Book1
	I	J	K	L	M	N	O	P	Q	R	S
8		6	5	-1	1	-4	-1	-2	4	-4	2
9
10
11
12
13		6
14		5
15		1
16		4
17		2
18
19
20
Sheet1

Cell Formulas
Range	Formula
J13:J18	J13	=IFERROR(INDEX($J$8:$S$8,1/LARGE(1/(IFERROR((1/($J$8:$S$8>0))*(COLUMN($J$8:$S$8)-COLUMN($J$8)+1),9999)),IF(ROWS($J$13:J13)<=COUNTIF($J$8:$S$8,">0"),ROWS($J$13:J13)))),"")

 

[Fluff]

In that case how about

Fluff.xlsm
	J	K	L	M	N	O	P	Q	R	S
5
6	2009	2010	2011	2012	2013	2014	2015	2016	2017	2018
7
8	-33,688,598	-29,587,046	1,000,000	-28,027,007	-15,752,937	-28,579,386	5,000,000	-50,355,149	-43,146,535	-40,980,658
9	1,000,000
10	5,000,000
11
12
Data

Cell Formulas
Range	Formula
J9:J12	J9	=IFERROR(INDEX($J$8:$S$8,AGGREGATE(15,6,(COLUMN($J$8:$S$8)-COLUMN($J$8)+1)/($J$8:$S$8>0),ROWS(J$9:J9))),"")

 

[Tdorman]

Thank you! that worked

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Tdorman]

@Fluff is there anyway to have the formula not count any amount that had been used already? For instance, in this example, because the 1,000,000 and 5,000,000 were already used they cant then be used in any of the subsequent years.