<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Sort a list of values without macro or "Sort" button: the table-style calculation

Published on
10,144 Points
3,944 Views
2 Endorsements
Last Modified:
Approved
When working with tables in Excel, we often think of cell by cell calculation.
Sometimes, we use several cells to perform a calculation and get one result, like range sum, average, match ...

What if your need is to use 1 cell or a range of cells to fill several cells with results?
In case your range contains only numbers or dates and if there are no repeated values, then it is possible.

Example:
You need to sort a range of 25 different values from column A, range A1:A25 in ascending order, and store it in B1:B25.
What comes to mind first is the sort function called from a macro.
But now, you think that it would be better if there was no macro to run, and the dream come true would be to do it in one single step ...
So, you want:
in B1 the min value of A1:A25
in B2 the min value of A1:A25 but without taking into account values <= B1
in B3 the min value of A1:A25 but without taking into account values <= B2 ... and so on

Let's try:
In B1 we type: MIN(A1:A25)
In B2 we type: MIN(IF(A$1:A$25<=B1;MAX(A$1:A$25);A$1:A$25))
In theory, in B2 we are asking to change values below B1 to the max value of the column so we are sure it will not interfere with the minimum we want to retrieve. And we take the min of it.
But it won't work, it will perform the test on A1 only (test result is FALSE) and not on the whole range ... it won't work, unless you tell Excel that you're working on the whole table, by validating the formula of your cell B2 with the combination of keys Shift + Enter.
You'll notice it puts your formula in brackets "{ = MIN ... }"
Now, you can extend the formula down to cell B25, it works !

There are many applications of this range-style calculation, up to you to find how it can apply in your case !
Capture.PNG
2
Comment
Author:sbaughan
0 Comments

Featured Post

Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Join & Write a Comment

This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month