Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting with formulaically "empty" cells
Hello,
I have a table of data, and one of the columns generates a percentage. Since it will sometimes give a DIV/0 error, the formula that generates the percentage is: =IF(SUM(I22:J22)=0,"",I22/SUM(I22:J22)) That way, if the divisor would be 0, it leaves the cell "empty." However, when I then sort by this column in descending order, all of the rows with "empty" percentages float to the top of the list. Below them, the ones with data sort correctly. I can change the formula to =IF(SUM(I22:J22)=0,0,I22/SUM(I22:J22)) but that creates confusion between the ones that have no percentage and the ones that legitimately have a percentage of zero. Is there a way that I can have a truly empty cell instead of one with "" in it? If so, would that help the sort? Is there another way I can sort it? Any advice would be greatly appreciated... Thanks to all. Mike |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting with formulaically "empty" cells
Mike,
I think the simplest is to Tools | Options | View tab, uncheck Zero Values. Make your formula return 0. The reason for this behavior is that Text sorts "larger" than numbers in alphanumeric sorts with different data types. This option setting will affect the entire workbook. If you only want this for some cells, you can use Format | Conditional Formatting. HTH Kostis Vezerides |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting with formulaically "empty" cells
Kostis,
Thanks for the advice. Another quick question. I don't want to get rid of all zero values, because some of them will legitimately be zero. However, the conditional formatting seems like an interesting option. Using the example above, I could make the conditional format "Formula Is" and use something like SUM(I22:J22)=0 and set the format based on the divisor, rather than the result. However, when I open the conditional formatting box, I only see tabs for Font, Border and Patterns. Is there a way to set the number format (i.e. don't return zero values) based on a condition? Thanks again. Mike |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting with formulaically "empty" cells
Kostis,
Your post gave me an idea. Since the percentage will always be a positive value (between 0 and 1), I set the formula to return -1, then used a custom number format on the column of 0.000;;0.000;@. That seems to be working, as I still see the true zeros, but the formatting suppresses the negative numbers. This allows me to sort the list in descending order. However, I'd still be curious if there's a way to do it with the conditional formatting, in case I ever have to sort it in ascending order. Thanks again for the help. Mike |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Sorting with formulaically "empty" cells
Mike,
My turn to thank you for the idea on the custom format. The whole exchange goes into my "multiple solutions" folder. I had not noticed it, but yes, the conditional format does not include Number format options. In this case, conditional formating would help by setting the font color to white when the value is zero. This would render the 0's invisible. Regards, Kostis |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sorting master sheet messes up cells in other sheets linked to it | Excel Worksheet Functions | |||
sorting master sheet messes up cells in other sheets linked to it | Excel Worksheet Functions | |||
identically size merged cells for sorting rows | Excel Discussion (Misc queries) | |||
sorting cells according to all cells in column A | Excel Worksheet Functions | |||
Convert data type of cells to Text,Number,Date and Time | Excel Worksheet Functions |