retrieve more than one midrange value

In a column of values (Trash Tonnages), which we evaluate every 4 to 6 months. We need to retrieve the 5, 6 or more middle values in order to re-evaluate our routes. Is there a method to do this in excel,

We have tried the MID() function but cant figure out how to obtain the 5 or 6 middle values (in their own cells.

The data which we are using isn't sorted. we want to avoid sorting and have excel retrieve the middle values we need.

Basically, I need to retrieve the values which are in blue (see Upload) and use them in another sheet, without sorting the table.

Originally Posted by Spasm752

Basically, I need to retrieve the values which are in blue (see Upload) and use them in another sheet, without sorting the table.

But there are only 4 values highlighted in blue and they are not the middle values. You really need to clarify exactly what you want with real example results.

They are not the IN the Middle but If you sort the list they are the 4 midrange values. I need to retrieve those values without sorting the list...

Originally Posted by Spasm752

They are not the IN the Middle but If you sort the list they are the 4 midrange values. I need to retrieve those values without sorting the list...

I did sort the list... my comment is based on the sorted values. See the image @AliGW posted in her reply... there are 3 values less than your highlighted range and 6 values higher. I echo Ali's comment: "All very unclear!"

If you actually wanted the real middle 5 values (that would remove 4 values from the top and 4 values from the bottom), this formula would do that in your copy of XL365...

Formula:

Please Login or Register  to view this content.

Thank you, Both Rick Rothstein's and your answers provide the solution i need. We can work with both these formulas and modify them to reflect our needs as our routes increase. Sorry if my request wasn't clear enough, but i didn't see any other way to explain it. Thank you once again.

Originally Posted by Spasm752

Thank you, Both Rick Rothstein's and your answers provide the solution i need. We can work with both these formulas and modify them to reflect our needs as our routes increase. Sorry if my request wasn't clear enough, but i didn't see any other way to explain it. Thank you once again.

Here is my formula adjusted so that it will report 5 or 6 middle values (depending on if the list has an odd or even number of zones), and more than 6 zones of course.

Formula:

Please Login or Register  to view this content.

Thank you, Both AliGW and your answers are what we need and we can modify them both to suit our needs if our routes increase. Thank you once again.

Originally Posted by Spasm752

Thank you, Both AliGW and your answers are what we need and we can modify them both to suit our needs if our routes increase. Thank you once again.

I reread my last post and I am not entirely sure if it explained my second formula correctly. This formula reports the 5 or 6 middle values no matter how many zones there are in your second table, so if you add more zones, it will adjust the output accordingly.