Returning Top and Bottom values of a column

[JFUT]

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.

 

[lenze]

Do you mean the First and Last value, or the MAX/MIN values?

lenze

 

[JFUT]

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

 

[MisterCrash]

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)

 

[vinod9111]

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


thanx in advance

 

[MisterCrash]

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)

 

[JFUT]

Thank you very much. Much appreciated.

 

[nevillestoke]

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.