find largest data (part2) hard work..

[Mark]

Hi,

It's request for a good programmers.
I have following table:

Owner Nr_flight Date Distance Pigeon Points
X 1 10-05-2003 100 X1 20,2
X 1 10-05-2003 100 X2 21,3
Y 1 10-05-2003 102 Y1 22,1
Y 1 10-05-2003 102 Y2 23,5
N 1 10-05-2003 105 N1 19,1
N 2 15-05-2003 120 N1 25,1
X 2 15-05-2003 121 X1 26,1
X 2 15-05-2003 121 X2 27,4
Y 2 15-05-2003 125 Y3 25,2
X 3 20-05-2003 130 X5 30,4
Y 4 25-05-2003 150 Y3 32,1

There are above is thousands records...
Range contents of columns:
-50 Owners,
-20 Nr_flights,
-Date (no matter),
-Distance in km (no matter),
-Each Owner has dozens pigeons,
-Points (no matter)

I'd like folowing automatic result:

W = sum best of points of 2 pingeons in range beetwen 100
and 125 km.
N = sum best of points of 3 pingeons in range beetwen 126
and 140 km.

count_optimum = W + N
Necessary condition:
Total distance counted pingeons must be minimum 650 km.

(in another sheet)
Owner perform condition (e.g. X) sum of points = ...
item Nr_flight Date Distance Pingeon Points
1 (e.g. 1) 10-05-2003 100 (e.g. X1) 20,2
2
3
4
5
sum of total distance ...

(3 rows empty)

Owner perform condition (e.g. Y) sum of points = ...
item Nr_flight Date Distance Pingeon Points
1 (e.g. 1) 10-05-2003 102 (e.g Y1) 22,1
2
3
4
5
sum of total distance ...

etc....all case perform condition

How could I do this?
It will appreciate if anyone can help.

Best Regards
Mark

 

[Tom Ogilvy]

=Sum(large(if((D22000>=100)*(D22000<=125),F2:F2000),Row(1:2))) +
Sum(large(if((D22000>=126)*(D22000<=140),F2:F2000),Row(1:3)))

Not sure how the total distance figures in. If you mean only pigeons that
have at a total of at least 650 should be considered, then I think the
easiest approach would be to use an extra column on the end that does a
sumif of the range to mark which records should be included. Then you could
add this column to the criteria above in the if statement.

Not sure what you are asking about the two sheets after the first.

--
Regards,
Tom Ogilvy




Entered with Ctrl+Shift+Enter rather than just enter. This is written in
the US English format with the list separator being a comma. Make
adjustments for your settings.