Best way to access top N cells in a table column?

[JenniferMurphy]

Is there a better (simpler, more compact) way to access the top N cells in a table column that what I did in this example?

Tables.xlsx
	B	C	D	E	F
2	N	3
3	Sum(N)	13	28	257	127
4
5		A	B	C	D
6		5	3	97	32
7		5	6	87	1
8		3	19	73	94
9		10	11	47	56
10		6	17	44	96
11		9	6	25	83
First Value

Cell Formulas
Range	Formula
C3	C3	=SUM(OFFSET(Table9[[#Headers],[A]],1,0):OFFSET(Table9[[#Headers],[A]],N,0))
D3	D3	=SUM(OFFSET(Table9[[#Headers],[B]],1,0):OFFSET(Table9[[#Headers],[B]],N,0))
E3	E3	=SUM(OFFSET(Table9[[#Headers],[C]],1,0):OFFSET(Table9[[#Headers],[C]],N,0))
F3	F3	=SUM(OFFSET(Table9[[#Headers],[D]],1,0):OFFSET(Table9[[#Headers],[D]],N,0))

Named Ranges
Name	Refers To	Cells
'First Value'!N	='First Value'!$C$2	C3:F3

Thanks

 

[Cubist]

Try

=SUM(TAKE(Table9[A],$C$2))

 

[Cubist]

Here's an array option.

Book1
	A	B	C	D	E
1	N	3
2	Sum(N)	13	28	257	127
3
4		A	B	C	D
5		5	3	97	32
6		5	6	87	1
7		3	19	73	94
8		10	11	47	56
9		6	17	44	96
10		9	6	25	83
Sheet5

Cell Formulas
Range	Formula
B2:E2	B2	=BYCOL(Table9,LAMBDA(t,SUM(TAKE(t,$B$1))))
Dynamic array formulas.

 

[JenniferMurphy]

Try

=SUM(TAKE(Table9[A],$C$2))

This is perfect. Thank you. This is a very useful function that I was not aware of.

Here's my implementation:

Tables.xlsx
	B	C	D	E	F
2	N	3
3	Sum(N)	20	20	231	89
4	Sum(3x2)	40	251	320
5		A	B	C	D
6		5	6	87	1
7		5	3	97	32
8		10	11	47	56
9		9	6	25	83
10		3	19	73	94
11		6	17	44	96
First n Values

Cell Formulas
Range	Formula
C3	C3	=SUM(TAKE(Table9[A],N,1))
D3	D3	=SUM(TAKE(Table9[B],N,1))
E3	E3	=SUM(TAKE(Table9[C],N,1))
F3	F3	=SUM(TAKE(Table9[D],N,1))
C4	C4	=SUM(TAKE(Table9[A]:Table9[B],3,2))
D4	D4	=SUM(TAKE(Table9[B]:Table9[C],3,2))
E4	E4	=SUM(TAKE(Table9[C]:Table9[D],3,2))

Named Ranges
Name	Refers To	Cells
'First n Values'!N	='First n Values'!$C$2	C3:F3

 

[JenniferMurphy]

Here's an array option.

The Take function is perfect for what I need. Is there any advantage to this solution vs Take?

 

[Cubist]

The Take function is perfect for what I need. Is there any advantage to this solution vs Take?

The advantage is that the formula spills over automatically when you add a new column. Try to add column E and see what happen.

 

[JenniferMurphy]

The advantage is that the formula spills over automatically when you add a new column. Try to add column E and see what happen.

Aha! Thank you very much for the excellent and useful solutions.

 

[Cubist]

Aha! Thank you very much for the excellent and useful solutions.

If you look at the formula again, I should clarify that it's still using SUM() and TAKE(). With the additional BYCOL(), it applies SUM() and TAKE() to each of the columns in Table 9.

And you're welcome.

 

[JenniferMurphy]

Try

=SUM(TAKE(Table9[A],$C$2))

I just found a new use for Take. See Is Take() the best way to calculate a moving sum?.

Let me know if I could have done it better.