Excel formula for portion of named range

[CB1234]

Hi: I'm trying to figure out a way to have a formula refer to just a portion of a named range. For example, if the range "myrange" contains the following pieces of data:

10
15
20
25
30
35

I want a formula that refers to just the first x rows of myrange.

So for example, if x = 3, and I had a formula looking for the max of that portion of myrange, it would return 20 (the max of the first three pieces of data)

Is there a simple way to do this? Thank you for any help!

 

[Scott Huish]

Assuming B1 held the number of rows, something like this:

Book1
	A	B	C
1	10	3	20
2	15
3	20
4	25
5	30
6	35
Sheet1

Cell Formulas
Range	Formula
C1	C1	=MAX(OFFSET(myrange,,,B1))

 

[J.Ty.]

You can do it as follows:

Book1
	L	M
1	1	10
2	2
3	3
4	4
5	5
6	6
7	7
8	7
9	8
10	9
11	0
Sheet1

Cell Formulas
Range	Formula
M1	M1	=SUM(INDEX(X,1):INDEX(X,4))

X is defined as =Sheet1!$L$1:$L$11.

J.Ty.

 

[CB1234]

Thank you! I didn't realize you could "colon" together two index formulas like that!

 

[J.Ty.]

Thank you! I didn't realize you could "colon" together two index formulas like that!

Yes, that works and has the advantage over OFFSET that the formula is not volatile.

J.Ty.