SEQUENTIAL ARRANGEMENT OF TEXT AND NUMBERS WITH EXCEL
To sequentially arrange text and numbers with excel based on ranks assigned to the text and numbers in another cell, we can use the COUNTIF and ISTEXT functions. The examples below show how to deal with duplicates, combined text and numbers, and sorted values. In example 1 below, the formula for Cell E7 is given below.
HOW TO SEQUENTIALLY ARRANGE TEXT AND NUMBERS WITH EXCEL
Input your data as shown in figure 1 below.
Click on Cell E7 as shown in figure 2 and type the content as shown on the figure into the cell.
Click on ENTER and your result should be as shown in figure 3
To treat the raw data on Cell D12 (200), simply change D7 in the formula to D12 as shown.
Click on OK and get your result as shown below.
EXPLAINING THE COUNTIF FUNCTION
- This COUNTIF function produces a rank provided all of the data are either numeric or alphabetic all through. So, if your data contains only text or only numeric characters, this function is okay and there won’t be need to type the function from the plus sign.
(COUNT(G7:G13)*ISTEXT(D7))– This COUNTIF function is added due to both text and numbers that are present on the data. This function helps to count numeric values present in the raw data.
Example 1 above discusses how to rank your data once you have the sorted arrangement for them. This example will discuss how you will rank the raw data above.
Type the raw data and the have a cell for rank as shown in figure 1. Do not type the sorted data. Leave it out.
Click on Cell E7 and type the content as shown on the fx bar. Click on ENTER and your result should be as shown in figure 7.
You can treat the others by changing D7 on the fx bar to D8, D9, and so on.
RANKING DUPLICATE VALUES
Let us change the data in Cell D13 to 200. This results in cell D12 and D13 having the same values. To rank them, use the formula below.
- Formula for D12
- Formula for D13
When you are done, you should have something like this below.