Forum Discussion
Auto sorting of data with Blanks/zeros at the bottom.
How do I rank my auto sort results from largest to smallest with zero/blanks at the bottom. I attached a workbook below. The other problem I have is when I wrote a similar equation in another workbook I get "#spell! message , what's wrong with my equation.
1 Reply
Liphor In R14 you could use this one:
=SORTBY(FILTER(O14:O63,O14:O63>0),IFERROR(RANK(O14:O63,O14:O63,0),999999),1)The RANK function creates an array of ranks (1 = highest number, 999999 = all blanks). Combine it with SORTBY to sort the filtered list.
The SPILL error you mentioned is due the fact the the function can't spill (i.e. output) the result as
not all cells are empty. Try by typing something, e.g. in R25 with the above formula in R14. You will get #SPILL!. The blue dashed line indicated where Excel want to put the result. Remove whatever you typed in R25 and the result will show correctly, surrounded by a solid blue line.
