Formula to return first or last value in a range

[Kamarlon]

I have a named range which I called (Y_Axis). I want to use the first and last values within this range for something. So my question is: Is there a formula which will look within the Y_Axis range and give me the first or last value (depending on which one I want)?

 

[OleMiss2010]

If I'm correct in understanding you, you want the formula to give you the largest and smallest values in a set of numbers. If that is the case you can use a LARGE and SMALL function for each case respectively.


Assuming your array is A1:A10 in this example, to get the largest number use the formula:

=LARGE(A1:A10,1)

and to get the smallest number:

=SMALL(A1:A10,1)


Is that what you were looking for?

 

[Kamarlon]

No not teh largest or smallest. Let me give an exam. let teh name range Y_Axis contain the following numbers listed in a column: 4 6 7 8 9 0 2 3. I want one formula that will look within the range and give me the first value which is 4 and another formula to look within the same range and give me the last value which is 3.

 

[Luke M]

Alternatives for largest/smallest would be the MAX/MIN functions. =)


If it's literally the first/last cell within the range, you can do:

=INDEX(Y_Axis,1)

for the first, and


=INDEX(Y_Axis,ROWS(Y_Axis))

for the last (if your range is horizontal, change ROWS to COLUMNS

 

[Kamarlon]

Thanks Luke M it worked like a charm. I see persons constantly using this INDEX formula, I didn't understand it's use until now. Thanks again.

 

[Luke M]

You're welcome, glad I could help. =)