Automatic sorting using workbook formulas

Optimizing the conclusions presented by an Excel worksheet data processing algorithm usually needs a solution for information sorting in order for it to be displayed top-down by importance factor to the subject.

 Additional formulas needed

You need to insert the formulas to calculate automatically the position of each item in the list based on it’s value. This is done by adding to the left of the table the Countif formulas that calculates how many values in the list are greater that the current item’s value. This way you get an automatic classification of the items. Don’t forget to add one position since the first (larger value) doesn’t have any others greater that in, getting zero as the Countif result.

Automatic Sorting using Excel formulas

The automatic sorting output table using workbook formulas

In the printable area or the Excel Worksheet you need to insert the results table with the position column (numbers from 1 to n). Now all you need is the Vlookup formula to help you find the items and values in the original table for each position number. Use the same formula to get also the values for each item, searching for the position or the item name in the first table.

Automatic sorting is great when you often must apply manual modifications to the reports you make in order to obtain a good looking slide and also present the conclusions starting from the most important one.


Write a Reply or Comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.