Sorting Issues

When choosing to sort one of my columns Largest to Smallest it lists all the cells at the top with the no data instead of the largest numbers. I have a formula in this column and it puts all the cells at the top with no data because it's producing back an error value. Anyway to adjust this so that the error number is counted as 0 instead of an incorrect formula?

Thanks!

You can use IFERROR() or IF(ISERROR() depending on your version of Excel. This will return whatever value you specify (e.g 0) in the event of an error.

Originally Posted by Hercules1946

You can use IFERROR() or IF(ISERROR() depending on your version of Excel. This will return whatever value you specify (e.g 0) in the event of an error.

Ya this is the formula that I currently have, it puts all the IFERROR 0's at the top.

Originally Posted by mlbdc2012

Ya this is the formula that I currently have, it puts all the IFERROR 0's at the top.

Ive tried this with a small workbook of mine and it works fine with the zero at the bottom. However, if its displaying the error message (eg #DIV/0!) these have a sort value thats higher than everything apart from blanks.
If the IFERROR is told to return zero, then if the other values being sorted are all numbers, the error cell should be at the bottom with a Largest to Smallest sort.
One point to note:
Make sure that you enter the IFERROR like this: =IFERROR(100/0, 0)
and not like this: IFERROR(100/0, "0")
because the text representation of a numeric character has a higher sort value than the number one.

Originally Posted by Hercules1946

Ive tried this with a small workbook of mine and it works fine with the zero at the bottom. However, if its displaying the error message (eg #DIV/0!) these have a sort value thats higher than everything apart from blanks.
If the IFERROR is told to return zero, then if the other values being sorted are all numbers, the error cell should be at the bottom with a Largest to Smallest sort.
One point to note:
Make sure that you enter the IFERROR like this: =IFERROR(100/0, 0)
and not like this: IFERROR(100/0, "0")
because the text representation of a numeric character has a higher sort value than the number one.

Here's the current formula I have, what should I adjust it to?

=IFERROR(INDEX(Database!$AV$4:$AV$3612,MATCH(E11,Database!$B$4:$B$3612,0)),"0")

Originally Posted by shg

As Hercules suggested, replace "0" with 0

I did this and it's still placing the errors at the Top when I sort largest to smallest.

Originally Posted by mlbdc2012

I did this and it's still placing the errors at the Top when I sort largest to smallest.

Just to clear up one possibility, you do realise that after you have removed the double quotes from the zero, you then need to perform the sort again in order for it to take effect ?
If this isn't the explanation, then we will need to see the problem demonstrated in a workbook, as shg has said.