Sorting Problem

  • Thread starter Thread starter djeans
  • Start date Start date
D

djeans

I have a spreadsheet that calculates check averages for sales persons.
Column a contains salespersons name, column b is their check average.
Column b contains a formula to return a blank cell if the salesperson
hasn't worked yet ie,
=IF(ISERROR(AVERAGE(C154:I154)),"",AVERAGE(C154:I154)). I have it set
up to sort by descending so that the salesperson with the highest check
average is at the top of the list. The problem, is that the blank cells
(salespersons who havent worked this week) are being sorted above the
cells with numbers in them. How do I fix this problem so that the empty
cells are sorted at the bottom?

I have tried changing the error formula to return a 0 if there are no
numbers to average, but they 0's still sort above the other numbers.

Please help

Thanks

Darron
 
djeans said:
I have a spreadsheet that calculates check averages for sales persons.
Column a contains salespersons name, column b is their check average.
Column b contains a formula to return a blank cell if the salesperson
hasn't worked yet ie,
=IF(ISERROR(AVERAGE(C154:I154)),"",AVERAGE(C154:I154)). I have it set
up to sort by descending so that the salesperson with the highest check
average is at the top of the list. The problem, is that the blank cells
(salespersons who havent worked this week) are being sorted above the
cells with numbers in them. How do I fix this problem so that the empty
cells are sorted at the bottom?

I have tried changing the error formula to return a 0 if there are no
numbers to average, but they 0's still sort above the other numbers.

Please help

Thanks

Darron
Click to expand...

Returning 0 is the thing to do.
You need the number 0 in your formula, not "0".
Are all the cells in this formula column formatted the same? If any have got
to be text format, this will mess up sorting. (Try formatting as General
without any overriding alignment, when numbers will be right-aligned and
text will be left-aligned.)
 
Thank you for your fast reply.

I did set it to replace with a Zero, but that still sorted above the
other numbers. Right now I have column B formatted as currency, which
is how i would like it to appear. When I had it set to text or general,
the numbers were usually several decimal places long. Can I still have
the numbers rounded to two decimal places if the cells are formatted to
general?

Thanks again

darron
 
When you made the change that Paul suggested, did you do:

=IF(ISERROR(AVERAGE(C154:I154)),"0",AVERAGE(C154:I154))
or
=IF(ISERROR(AVERAGE(C154:I154)),0,AVERAGE(C154:I154))

Try losing those double quotes around the 0 ("0").
 
I had left it with the double quotes. I will try it without and see how
it works.

Thanks again for the suggestions.

Darron
 
Back
Top