help with finding top 3 percents in last 10 dates and averageing

[xpostie]

Ok here is my problem

I have a list of the following in columns

date shoes ringers percent


Now I want to find only the last ten dates, then pick the best 3 out of the ten, and then work out the percent for the top 3.

Im completely confused on how to do this.

yes it is for a horsehoe pitching average.

So my answer would be top 3 ringers / top 3 shoes and then get percent of those 3 .

Does this make sense

tom

[Glenn Kennedy]

No, very little sense, I'm afraid!!.

Please attach a sample of your data in an Excel worksheet. Make sure there is enough data to demonstrate your need. Make sure your desired results are shown, mock them up manually if necessary. Remember to remove ALL confidential information first!!!

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[xpostie]

Ok here is the file.

i need to figure out how to select the last 10 dates , the best 3 percentages
and then calculate the top 3 ringers and top 3 shoes thrown.

thanks for your help
Tom

[FDibbins]

1st trhing you need to do here is convert those "dates" in column A to real dates. I used a helper column (G) and put this in G5, copied down...
=DATEVALUE(MID(A5,5,2)&LEFT(A5,3)&RIGHT(A5,4))

Will your dates always be consecitive, with no dates missing in the list?

[FDibbins]

And what about duplicates/ties?

[Tony Valko]

Not sure what you're wanting to do.

This array formula** will return the SUM of the highest 3 numbers from the last 10 rows.

Array entered** in H2 and copied across to I2:

=SUM(LARGE(C5:INDEX(C5:C29,LARGE(ROW(C5:C29)-ROW(C5)+1,10)),{1,2,3}))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[FDibbins]

Put this ARRAY formula where you want the answers to be, and copy down twice, then copy across once.
=LARGE(IF($G$5:$G$29>=LARGE($G$5:$G$29,10),C$5:C$29),ROW(A1))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[FDibbins]

deleted my duplicate post

[FlameRetired]

Another interpretation. In G2 of attached array-enter this formula and fill down 10 rows to get the last 10 dates.

Please Login or Register  to view this content.

Array enter means the formula must be committed by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Then preselect H2:H11 paste this formula in H2 and commit the entire range at once with Ctrl + Shift + Enter.

Please Login or Register  to view this content.

Fill across to column J. Reformat as needed.

The other formulas in attached are non-OFFSET versions. They are committed the same way.

[xpostie]

Ok this last post with the example file is very close to what im looking for.
Im sorry but im lost in all this now. but what is missing is when the top 3 percentages are found I need
the correct shoes , and ringers for that same date to be listed . Not another date.
hope that makes sense.

if on Jan 23 the percent was 76% and was one of the top 3 then i need the corresponding data from the
for shoes and ringers fromt he same date Jan 23.

thanks for your help we are getting somewhere.

Tom

[FlameRetired]

Withdrawn by FR.

[FlameRetired]

This is a painfully convoluted set of formulas. They first establish a range of the 10 latest dates and from that the 3 larges percentages. In G2 this array-entered formula filled down until you get blanks.

Please Login or Register  to view this content.

Then in H2 this array-entered (variation of above) formula and fill down until you get blanks and across to column J.

Please Login or Register  to view this content.

The re-worked file is attached.

[FlameRetired]

These are simpler but volatile. In G2 array-entered, filled down until blank.

Please Login or Register  to view this content.

In H2 array-entered filled down and across to J.

Please Login or Register  to view this content.

[xpostie]

I think that works but can you add new dates on the bottom of the list of games or do you have to delete old ones.
I would like to just keep adding newones. any idea?

[FlameRetired]

Yes you can add them. You have to extend the range(s) in the formulas though. One way to do this.......and a good alternative to editing all those formulas.....is to build Dynamic Named Range(s) (DNRs) in Name Manager. These shrink and grow with data. You then replace the hardcoded ranges in your formulas with this named range. You will likely need more than just the one for the dates.

If you search the Forum for 'Dynamic Name Range' you'll find several threads that describe different ways to do this. A blind 'Google' search will do likewise. IMPO this is something well worth studying up on. They are very handy and save a lot of future editing work.

And BTW the formulas I posted rely upon the dates to be in ascending order with no duplicates. As long as the data continues with that pattern these should be OK.