# Returning Top and Bottom values of a column

#### JFUT

##### Board Regular
Hi there

Is there a formula that will return the following into 2 active cells???
1) the top value in a column of values
2) the bottom value in a column of values

e.g.

45
34
1
23
34
5

Top = 45 Bottom = 5

Replies would be much appreciated.

### Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Do you mean the First and Last value, or the MAX/MIN values?

lenze

Hi there

Thanks for replying but this wasn't quite what I wanted. Basically, I don't need the largest and smallest value, I need the value in the top cell and the value in the bottom cell within a column of cells

e.g.

56
5888
67888
56666
3
45

So, top cell = 56 and bottom cell = 45

These formulas presume that your range of interest is J6:J20, which can be adjusted as necessary. They also presume that the cell values are all numbers, and that there are no blank cells between the first cell of data and the last cell of data.

To get the first non-blank entry in this range, use this formula:

=OFFSET(J\$6, MATCH(1E+300, J\$6:J\$20, 1)-COUNT(J\$6:J\$20), 0)

To get the last non-blank entry in this range, use this formula:

=OFFSET(J\$6, MATCH(1E+300, J\$6:J\$20, 1)-1, 0)

Mistercrash can you explain the formula. it will help me for better understanding the logic.

Let's say that you have numbers in cells J8 through J16.

1. This part of the formula will return the row of the last numerical entry from the range J6:J20:

MATCH(1E+300, J\$6:J\$20, 1)

Basically, it asks Excel to find the first instance in that range of a number that is larger than an enormous number (10^300). Because no entry will work, Excel will end up returning the last entry with a number in it in that range. In this case, the last entry is in cell J16, which is the 11th cell in the range (since J6 is the first cell). So, this formula will return 11.

[If you're using text entries, the corresponding formula would be something like: MATCH("ZZZZZ", J\$6:J\$20, 1)]

2. Now, we need to figure out what the actual value is in the 11th row of our range. To do this, we use the OFFSET function. The starting cell of our range is J6, and we want to go to the 11th row of the range, which is 10 rows down from the starting cell. So, we have to subtract 1 from the result provided by the MATCH function in step 1 to get the number of rows that we want to offset from the base cell. This gives us:

=OFFSET(J\$6, MATCH(1E+300, J\$6:J\$20, 1)-1, 0)

3. The first number in the range is a little trickier. Basically, we can find the last cell in the range through the function in step 1. If there are no blank spaces in the cells (other than before the first cell of data and after the last cell of data), then each cell will contain a number. This function will tell us how many numbers there were in the range:

COUNT(J\$6:J\$20)

In my example, there are numbers in rows 8 through 16. That's a total of 9 numbers, so this formula will return 9.

4. If the last entry in the range is in the 11th row, and there are 9 numbers listed consecutively, then the first entry must be in the 3rd row of the range. Since our base cell for our offset is the first row of the range, we need to go 2 rows down from the base cell. This is calculated as the value in step 1 (11) minus the value in step 3 (9). So, our offset formula becomes:

=OFFSET(J\$6, MATCH(1E+300, J\$6:J\$20, 1)-COUNT(J\$6:J\$20), 0)

Thank you very much. Much appreciated.

Thanks MRCrash:

I was able to find this post through a search and with a minor modification retrieve the information needed for my spread sheet.

I continue to love this forum.

Replies
5
Views
107
Replies
1
Views
145
Replies
5
Views
214
Replies
3
Views
302
Replies
1
Views
129

1,196,414
Messages
6,015,123
Members
441,873
Latest member
saustark

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back