Sorting with formulaically "empty" cells

[mikelee101]

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

 

[vezerid]

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

 

[mikelee101]

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

 

[Dave Peterson]

I'd use a helper column of cells with formulas like:

=if(x2="",99999999999,x2)
and drag down.

Then sort by that helper column.

Change the 9999999999 to a number big enough to sort at the bottom of your list.

 

[mikelee101]

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

 

[vezerid]

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