Find the best apps for taking care of business.Track finances, handle accounting tasks, manage contacts, keep organized, and collaborate.Business-Budget-Template-Excel-Format.jpg' alt='Business Math Using Excel Free Download' title='Business Math Using Excel Free Download' />Calculate Percentile and Conditional Ranking in Excel Using SUMPRODUCTThe Excel functions for performing ranking and establishing percentiles are poorly described and confusing to use on the best of days.The PERCENTILE function doesnt give the percent ranking of the item, but the instead the value at a given percentile which might not even exist.This makes it difficult to calculate even simple percent rankings in Excel.But what if you want to rank a sub set of a list based on some criteria In this tutorial, well walk through the challenges of calculating percentiles and ranking values based on filtering criteria using a basic example.Examine the Data.For this exercise, we need a data set of values that can be filtered by a criteria.In this example, well use a list of the populations of capital cities around the world.Our challenge will be to calculate the percentile ranking for capital cities within each continent separately.Lets take a quick look at the dataIn this example, we can see that while its easy to see that Beijing has the largest population overall, it is difficult to see where other cities rank in population on each continent.They are all jumbled together There are two ways to approach this problem.If this data set is static and you will never need to update it, it may help to sort it by the criteria in this case, Continent and perform your rankings and percentiles from there.Lets look at this option in detail.Sort, Rank, and Calculate Percentiles using RANK and COUNTTo filter the data by criteria manually, we can perform a sort on it.Select the columns that contain the data.Then click the Sort button in the Sort Filter section of the Data menu tab.This will bring up the Sort dialog box.In the window that appears, choose the column that contains the criteria to sort by.In this example, it is the Continent column.Click OK to apply the sort.Now we can determine percentiles for each Continent separately.Project-plan-excel-template-free-download-small.png' alt='Business Math Using Excel Free Download' title='Business Math Using Excel Free Download' />To do so, we will need to use the RANK and COUNT functions.The RANK function gives the rank order of a value in a list.The syntax for RANK is as follows RANKnumber, ref, orderIn Excel 2.RANK has been re named to RANK.EQ with the same syntax RANK.EQnumber, ref, orderThe number is the value you want to find the rank for.Department-budget-ss_0.JPG' alt='Business Math Using Excel Free Download' title='Business Math Using Excel Free Download' />The ref is the cell range that contains the list of numbers you want to compare it to.By default, RANK and RANK.EQ will rank in descending order largest number at the top, ranked 1st.The optional order can be a 0 for descending order the default or a 1 for ascending order smallest number at the top, ranked 1st.RANK will give us the spot in line for each value in our data, but to get a percentile, we need to know how many other values there are in the list.For this, we use COUNT.The syntax for the COUNT function is as follows COUNTvalue.Each value in the COUNT function can be a cell or a range of cells.COUNT will count all the cells that have values in them.Lets find the percentile ranking of all the populations for African capital citiesWe want to compare the population of each row to all the populations in Africa to get the rank.Then we want to divide that rank by the total number of African cities there are in the list.The syntax for the formula in the first row is as follows RANK.EQD2,D2 D5.COUNTD2 D5.D2 is the population in the first African city on the list.The range D2 D5.African populations on the list.It has dollar signs in front of the column letter and the row numbers to lock the range in place.We can copy the formula down the row for all African populations, and RANK will still compare each row only to the other African cities.COUNT will similarly only count the number of African cities in the list.The RANK is put in the descending order 1 so that the largest population ranks 1st 1.You can drag the formula in E2 down for all the rows that have African cities.After changing the formatting to show percentages, you will have the percentile ranking for each African capital city by population size You can repeat the process for the other continents individually.You will have to manually change the ref range of the RANK function and the value range of the COUNT function to indicate the new continent set.Automatic Conditional Ranking and Percentiles with SUMPRODUCTThe above process works for simple data sets and one time sort and filter operations, but if you have a large data set or need to be able to refresh it and re rank often, you need a more automatic way to do the filtering and sorting of criteria.Fortunately, SUMPRODUCT can help us do just that using a trick called an array formula and Boolean TRUE and FALSE equations.Boolean Equations in Excel.Excel can test equations to see whether they are TRUE or FALSE.For example, if you entered 123.Excel would show TRUE in the cell because 12 is equal to 3.Try an inaccurate comparison 124.Excel shows FALSE.Pretty simple.The trick is that Excel can do math with Boolean statements.It treats TRUE as 1 and FALSE as 0.Try entering the following in a cell TRUETRUETRUEExcel will calculate the cells value as 3.Using Boolean Arrays with SUMPRODUCTSUMPRODUCT can use booleans an an array to filter results.If you compare an array to a criteria and multiply it with itself, youll get a filtered sum, just like if you were using a SUMIF function.For example The above example evaluates to 6.It is comparing each row to 4.When the number is less than 4, it returns TRUE or 1.When the number is not less than 4, it returns FALSE or 0.The SUMPRODUCT multiplies that set of results against the same numbers and adds them up.This is the same as if we did the multiplication and addition long hand 1 1 1.Building a SUMPRODUCT Percentile Formula.We are going to apply the same strategy to filter the city populations by matching their continents in our example data set.We dont even need to sort it first Lets go back to that original, un sorted city list In the first row, enter the following formula SUMPRODUCTC2 C2.C2D2 D2 D2.COUNTIFC2 C2.C2Pay attention to the locked ranges.They are selecting the entire column of data for Continent C2 C2.Population D2 D2.Lets examine what the formula is doing by looking at each of the Boolean statements C2 C2.C2This is comparing each rows Continent to the continent in the current row.It will return TRUE for each match and FALSE for each mis match.This means we will only be considering populations in the same continent.D2 D2 D2.This is comparing each rows Population to the population in the current row.It will return TRUE for each value that is smaller than or equal to the current row and FALSE for each value that is greater than the current row.Remember, this will only count for populations in the same continent because of the earlier Boolean statement.This is doing a conditional ranking, similar to the RANK statement in the manual example earlier in the tutorial.Its just doing it for the un sorted list.You could add or remove rows from the list and it would automatically re calculate the rank.The COUNTIF function is the same as the COUNT function in the manual, sorted example.Its just counting only the rows that meet the criteria.The syntax for COUNTIF is as follows COUNTIFrange, criteriaIn this case, the range is the continent column, and the criteria is the current rows continent.Now that the formula has been built, we can simply drag it down to all the rows in the data set.It will automatically calculate a separate percentile ranking for each country within its own continentNote that the largest city in Europe Moscow is ranked in the 1.Europe even though it is third largest overall.This shows that the formula is working correctlyDownload the Example File.You can experiment with the example data in the embed below, or download the spreadsheet by clicking the green Excel button in the bottom right.Andrew Roberts has been solving business problems with Microsoft Excel for over a decade.Excel Tactics is dedicated to helping you master it.You can read more of his writing on his personal blog at Napkin.Math.Join the newsletter to stay on top of the latest articles.Sign up and youll get a free guide with 1. Articulate Storyline Free Download Crack Autocad . Windows 8 32 64Bit Genuine Activated Charcoal Teeth '>Windows 8 32 64Bit Genuine Activated Charcoal Teeth .
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
November 2017
Categories |