General Betting
There is currently 1 person viewing this thread.
Is there anyway to use the large function in combination with filters like you can with subtotal?
Or in English, how do you find the best X results in a sample of data.
Or in English, how do you find the best X results in a sample of data.
sort by:
Show
per page
Replies: 9
|
By:
Not sure what you require so I am guessing ~
You can do a sum of data in an array formula like ~ {=SUM(LARGE(Data,{1,2,3,4,5,6,7,8,9,10}))}This formula sums the 10 largest values in a range called Data. If you you only wanted to sum odd LARGEST ~ {=SUM(LARGE(Data,{1,3,5,7,9,11}))} In this scenario you wont have to presort values in the data into order This is an array formula so must be entered by holding down control and shift and press enter. Don't type the outside Brackets. |
|
By:
This formula does the same ~ enter as an array formula
=AVERAGE(LARGE(C6:C20,ROW(INDIRECT("1:5")))) =AVERAGE(LARGE(Data,ROW(INDIRECT("1:5")))) ~ Data being C6:C20 as above As with Subtotals you can use average,sum etc |
|
By:
When you filter data and use the subtotal function only the filtered data is used in the summation where as with other sum functions it takes all data in the range even if it's been filtered.
Every formula you gave me, as well as the ones I've tried gives the 5 largest values in the data I have, rather than 5 largest values for the specific player. |
|
By:
If you wanted to see what the actual numbers are
Put a sequence numbers from 1 to whatever in a separate column (say column D) Put this in another column and fill down for number required =LARGE(Data,D1) |
|
By:
Why not use just the filters then
Filter for the player then filter numbers, top 10 then a box pops up and change to to 5 or top 15 or whatever you rquire |
|
By:
filter numbers, top 10 then a box pops up and change to to 5 or top 15
That filter has preference over every other filter so it returns the top X items and ties of the entire data rather than for the specific player. |
|
By:
If your data is in column C starting at row 6
After you have filtered, your answers (sum,average etc) will be in the subtotal =SUBTOTAL(3,OFFSET($C$6,0,0,COUNT(OFFSET($C$6,0,0,9999)),1)) |
|
By:
Your right I work on it a bit more
|
|
By:
Seems the easiest way is filtering with subtotals
First filter the player, then in the column that has the data X in you require filter, click select all, so no check boxes are ticked And if you want the highest figures scroll to the bottom and if you want the largest 4 tick the last 4 boxes etc |