Happy Friday Excellers. Today I want to share with you how to RANK a data set, but ignore zero values. I use RANK a LOT, and sometimes you just do not want to include zero values in your results.
If you want to download the Formula Friday examples you can so here-
Ranking Ignoring Zeros
We actually are going to make use of the IF function as well as RANK in this solution. Here is sample data set I want to rank.
I have 10 lines of data and 2 of them are zeros which I do not want to be included in the ranking values or calculations. This is the formula I am using to achieve this.
Let’s Walk Through The Formula.
If B4 contains a zero then Excel returns a blank cell “”. If the value in in cell B4 is not equal to zero then the value is ranked in the cell range of B4 to B13, which gives us the ranking of 2. We can then drag the formula down the length of the data range to populate the whole data set.
See you next time for more #FormulaFriday fun.
If you want to download the Formula Friday example workbook then you can do so here.
Dont forget to sign up to the Excel at Excel Newsletter for 3 free Excel tips the first Wednesday of the month. Just click on the Sign Up Form to the right or use the link below.