Sorting numbers with #div/0! in column

**Barking_Mad** · 10-20-2009, 07:10 AM

Hi,

I have some numbers in a column which due to other cells not yet being filled in are returning a supressed #div/0! error. This is fine, but when i go to sort the column it puts them in the wrong order.

If it helps out any, I would like to record a macro, and assign it to the column header in order to sort the column.

Thanks

**zbor** · 10-20-2009, 07:31 AM

Can you uplaod example and clearify how you want them to be ordered!

**Richard Buttrey** · 10-20-2009, 07:32 AM

Hi,

You don't say what the 'right' order should be. Do you want to ignore the div/0 values, sort them all to the top, or bottom or what?

Rgds

**Palmetto** · 10-20-2009, 07:33 AM

You can avoid this by nesting your division formula in the IF function and return a value of zero in place of division errors.

=IF(ISERROR(A2/B2),0,A2/B2)

You should get correct sort results with a valid number in the cell.

**Barking_Mad** · 10-20-2009, 08:17 AM

Hi,

Sorry I would like to put all the #div/0! errors below the numbers that are being correctly sorted.

So it would look like..

67.54
45.34
22.90
3.76
2.79
#div/0!
#div/0!
#div/0!

**Barking_Mad** · 10-20-2009, 08:23 AM

Originally Posted by Palmetto

You can avoid this by nesting your division formula in the IF function and return a value of zero in place of division errors.

=IF(ISERROR(A2/B2),0,A2/B2)

You should get correct sort results with a valid number in the cell.

Hi, thanks that works just super