Choosing last positive values

ShiftyThor

New Member
Joined
Mar 4, 2013
Messages
14
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.


ABCDEFGHIJKLMN
1PositivePositivePositivePositivePositiveNullPositivePositivePositivePositiveNullPositive This will be the Value
2100.0050.0090.0040.0090.000.0090.0080.0075.0080.000.0080.00 450.00
3CountLeaveCountLeaveCount CountLeaveLeaveLeave Count I need formula above

<colgroup><col><col span="12"><col><col></colgroup><tbody>
</tbody>

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.
 

Some videos you may like

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,725
Office Version
  1. 365
Platform
  1. Windows
See if this works for you.
Excel Workbook
ABCDEFGHIJKLMNO
1PositivePositivePositivePositivePositiveNullPositivePositivePositivePositiveNullPositive
210050904090090807580080450
3CountLeaveCountLeaveCountCountLeaveLeaveLeaveCount
4
Sheet
 

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi AhoyNC

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

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
Hi ShiftyThor

What happens if there are less than 10 positives?
 

AhoyNC

Well-known Member
Joined
Oct 10, 2011
Messages
4,725
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

PGC01,

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

pgc01

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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

Well-known Member
Joined
Jul 2, 2012
Messages
4,517

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 25, 2006
Messages
19,884
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

New Member
Joined
Mar 4, 2013
Messages
14
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

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
"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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,518
Messages
5,602,138
Members
414,505
Latest member
quoctrungvu99

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top