Sum of Lowest Rankings

[Celticshadow]

Hi Forum

I have as attached in the google sheets link

column A is the race id
column B which lists horses names
column C I have the starting price odds of said horses
column D I have the rank of those Starting price odds
column E I have the finishing position of those horses

What I would like if possible is (with a formula) to put in column F the sum of the two smallest ranks from column D that would be taken from the first 3 finishing positions in column E.

Thus

first pos is 1 and its coinciding Price rank is 4
second pos is 2 and its coinciding Price rank is 5
third pos is 3 and its coinciding Price rank is 9

Therefore 4 and 5 need to summed which equals 9 and placed in column D.


Many thanks for looking and I look forward to any help forum may be able to give me.

Sum Lowest Ranks

docs.google.com

Regards

 

[Fluff]

Is this for Excel or Google Sheets?

 

[Celticshadow]

Hi Fluff

This is for excel I was just using google sheets to show an example. My apologies.

Regards

 

[Fluff]

Ok, how about

=SUM(TAKE(SORT(D2:E14,{2,1}),2,1))

 

[Fluff]

Scrub that formula it's wrong, try

=SUM(TAKE(SORT(FILTER(D2:D14,E2:E14<=3)),2))

 

[Celticshadow]

Hi Fluff


Many thanks for replying it is much appreciated and your formula works a treat. However I stupidly have not explained myself fully and it is my own fault. The eg I have shown is only partial and the sheet extends through a number of races and so I have updated the sheets doc to reflect this, thus I would require a sum for each individual race as indicated by the race id.Please note that the actual sheet extends through from Race id 1 to Race id 40,000 and counting.


I hope that is a little clearer and I apologise for the confusion I have caused.

Sum Lowest Ranks

docs.google.com

Regards

 

[Fluff]

How about

Sum Lowest Ranks(1).xlsx
	A	B	C	D	E	F	G	H
1	Race ID	Horse	Starting Price	Starting Price Rank	Finishing Pos		Race	Top 3 sum
2	1	Parga Diamond	8.63	4	1		1	9
3	1	Diamond Tipp	18	5	2		2	5
4	1	Music Of Life	28	9	3		3	3
5	1	Belle Le Grand	21.93	7	4
6	1	Whitewinewednesday	8.6	3	5
7	1	Joshuas Daylami	18.81	6	6
8	1	El Fabienne	3.05	1	7
9	1	Angels Peak	220	13	8
10	1	Maries March	5.9	2	9
11	1	Annie Hope Harriet	25.23	8	10
12	1	Koo Star	81.92	11	11
13	1	Keep It Cool	36.9	10	12
14	1	Slyguff Storm	100.54	12	13
15	2	Jollie Cosmos	9.00	5	1
16	2	Lake Chad	3.00	2	2
17	2	Natural Breeze	6.00	3	3
18	2	Smitty Bacall	7.00	4	4
19	2	Room To Roam	2.00	1	5
20	2	The Abbey	10.00	6	6
21	2	Elegant Dan	12.00	7	7
22	2	Balinaboola Steel	14.00	8	8
23	3	A Penny A Hundred	1.50	1	1
24	3	Costanuci	5.00	4	2
25	3	Miss Agusta	3.00	2	3
26	3	Presenting Doyen	4.00	3	4
27	3	Miss Tempo	10.00	5	5
Sheet1

Cell Formulas
Range	Formula
G2:G4	G2	=UNIQUE(TOCOL(A2:A100,1))
H2:H4	H2	=SUM(TAKE(SORT(FILTER($D$2:$D$100,($E$2:$E$100<=3)*($A$2:$A$100=G2))),2))
Dynamic array formulas.

 

[Celticshadow]

Hi Fluff

Many thanks for replying it is much appreciated. Although that works it is not quite what I was looking for in that I was hoping a formula could be put in Column F and then copied all the way down through all the races as in the google sheets eg so that each id in col A had its top 3 sum in column F. Once again many thanks for looking.

Regards

 

[Fluff]

Ok, how about

Sum Lowest Ranks(1).xlsx
	A	B	C	D	E	F
1	Race ID	Horse	Starting Price	Starting Price Rank	Finishing Pos
2	1	Parga Diamond	8.63	4	1	9
3	1	Diamond Tipp	18	5	2	9
4	1	Music Of Life	28	9	3	9
5	1	Belle Le Grand	21.93	7	4	9
6	1	Whitewinewednesday	8.6	3	5	9
7	1	Joshuas Daylami	18.81	6	6	9
8	1	El Fabienne	3.05	1	7	9
9	1	Angels Peak	220	13	8	9
10	1	Maries March	5.9	2	9	9
11	1	Annie Hope Harriet	25.23	8	10	9
12	1	Koo Star	81.92	11	11	9
13	1	Keep It Cool	36.9	10	12	9
14	1	Slyguff Storm	100.54	12	13	9
15	2	Jollie Cosmos	9.00	5	1	5
16	2	Lake Chad	3.00	2	2	5
17	2	Natural Breeze	6.00	3	3	5
18	2	Smitty Bacall	7.00	4	4	5
19	2	Room To Roam	2.00	1	5	5
20	2	The Abbey	10.00	6	6	5
21	2	Elegant Dan	12.00	7	7	5
22	2	Balinaboola Steel	14.00	8	8	5
23	3	A Penny A Hundred	1.50	1	1	3
24	3	Costanuci	5.00	4	2	3
25	3	Miss Agusta	3.00	2	3	3
26	3	Presenting Doyen	4.00	3	4	3
27	3	Miss Tempo	10.00	5	5	3
28
Sheet1

Cell Formulas
Range	Formula
F2:F27	F2	=MAP(TOCOL(A2:A100,1),LAMBDA(m,SUM(TAKE(SORT(FILTER($D$2:$D$100,($E$2:$E$100<=3)*($A$2:$A$100=m))),2))))
Dynamic array formulas.

 

[Celticshadow]

Hi Fluff

Wow what can I say, that is a fantastic solution that works superbly well and I am so grateful for your time and expertise, many thanks.

Regards