Choosing last positive values

[ShiftyThor]

Hi all,

I am trying to work a formula where I can get the best 5 values out of the last 10 positive values. e.g.


[TABLE="width: 944"]
<colgroup><col><col span="12"><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Positive[/TD]
[TD]Positive[/TD]
[TD]Positive[/TD]
[TD]Positive[/TD]
[TD]Positive[/TD]
[TD]Null[/TD]
[TD]Positive[/TD]
[TD]Positive[/TD]
[TD]Positive[/TD]
[TD]Positive[/TD]
[TD]Null[/TD]
[TD]Positive[/TD]
[TD] [/TD]
[TD]This will be the Value[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]100.00[/TD]
[TD]50.00[/TD]
[TD]90.00[/TD]
[TD]40.00[/TD]
[TD]90.00[/TD]
[TD]0.00[/TD]
[TD]90.00[/TD]
[TD]80.00[/TD]
[TD]75.00[/TD]
[TD]80.00[/TD]
[TD]0.00[/TD]
[TD]80.00[/TD]
[TD] [/TD]
[TD]450.00[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Count[/TD]
[TD]Leave[/TD]
[TD]Count[/TD]
[TD]Leave[/TD]
[TD]Count[/TD]
[TD] [/TD]
[TD]Count[/TD]
[TD]Leave[/TD]
[TD]Leave[/TD]
[TD]Leave[/TD]
[TD] [/TD]
[TD]Count[/TD]
[TD] [/TD]
[TD]I need formula above[/TD]
[/TR]
</tbody>[/TABLE]

Therefore I need the formula in N2 to work find the last 10 positive values in the row A2:L2 (F2 and K2 will not be seleced), and then select the top 5 of those values being A2, C2, E2, G2, L2.

I think I have to use the Offset, Match and Max formula but am not sure.

Thanks all, hope the above makes sense.

 

[AhoyNC]

See if this works for you.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Positive	Positive	Positive	Positive	Positive	Null	Positive	Positive	Positive	Positive	Null	Positive
2	100	50	90	40	90	0	90	80	75	80	0	80		450
3	Count	Leave	Count	Leave	Count		Count	Leave	Leave	Leave		Count
4
Sheet

 

[pgc01]

Hi AhoyNC

If I understand correctly it's the biggest 5 of the last 10 positive, not the biggest 5 of all.

 

[pgc01]

Hi ShiftyThor

What happens if there are less than 10 positives?

 

[AhoyNC]

PGC01,

Good catch. The formula I posted would add a negative number if it was one of the 5 largest.

 

[pgc01]

Hi AhoyNC

Not only that, if in the example K2 was 10, you'd be adding the 100 in A2, that was not part of the 10 last positives.

 

[XOR LX]

Maybe (array-entered):

=SUM(LARGE(IF(ISNUMBER(MATCH(COLUMN(A2:L2),LARGE(IF(A2:L2>0,COLUMN(A2:L2)),ROW($1:$10)),0)),A2:L2),(ROW($1:$5))))

Regards

 

[pgc01]

Hi

Assuming there are 10 positives, another option:

=SUM(LARGE(INDEX(2:2,LARGE(IF(A2:L2>0,COLUMN(A2:L2)),10)):L2,{1,2,3,4,5}))

... confirm with CSE

 

[ShiftyThor]

Hi All thanks for the help so far, basically it is for a sports club, and scores from weekends. I need to select the last 10 positive values and get the best 5 so that I can get a score to choose for provincial selection team. a Zero score will be for when the person has not come for a weekend. If there are not enough positive numbers, we still take the best 5, even if they only have 3. This will be running every weekend, and my example is only an extract, which I will build onto once I get an idea on the formula.

XOR LX, I think you may be on the right track with ISNUMBER, however the formula is pointing to rows 1-10 and 1-5, and is coming up with a "0" in excel.

pgc01, I get a #VALUE when I try your formula.

Thanks again for the help so far, it is a tricky one. I am sure I can get some formula going, even if I have to insert another worksheet to extract the positive numbers and the use the LARGE command on another worksheet getting the highest values from there.

 

[XOR LX]

"XOR LX, I think you may be on the right track with ISNUMBER, however the formula is pointing to rows 1-10 and 1-5, and is coming up with a "0" in excel."

No! The formula is not "pointing" to those rows at all! The ROW constructions here are merely used to generate the 10- and 5- array returns respectively.

Are you sure you entered it as an array formula? (Do you know how to do this?)

Regards