How can I "RANK" time in Excel 2003?

Hi there

I am working on a spreadsheet to measure agent performance. I want to rank
agents on the time they spend in "wrap up" after each call. I've tried using
the RANK formlua but it returns a value #DIV/0! and the error states "the
formula or function used is dividing by zero or empty cells".

However, the cells are empty. I've tried changing the cell format but its
not working.

Any Ideas?

THanks

can you show the exact formula you have used and the inputs to it.

Mangesh




"Michael" <[email protected]> wrote in message
news:[email protected]...
> Hi there
>
> I am working on a spreadsheet to measure agent performance. I want to
rank
> agents on the time they spend in "wrap up" after each call. I've tried
using
> the RANK formlua but it returns a value #DIV/0! and the error states "the
> formula or function used is dividing by zero or empty cells".
>
> However, the cells are empty. I've tried changing the cell format but its
> not working.
>
> Any Ideas?
>
> THanks

Hi Mangesh

Here is an example of the data and formula.

A
1 0:31:51
2 0:00:00
3 0:30:20
4 0:00:09

The formuala to caculate the rank is =RANK(A1,$A$2:$A$4,1). I've also tried
=RANK(A1,$A$2:$A$4,0).

It returns the value #DIV/0! and the error states "the
> > formula or function used is dividing by zero or empty cells".

The cells are in TIME format. Although I have tried number and general and
even text.

Thanks

Michael

"Mangesh Yadav" wrote:

> can you show the exact formula you have used and the inputs to it.
>
> Mangesh
>
>
>
>
> "Michael" <[email protected]> wrote in message
> news:[email protected]...
> > Hi there
> >
> > I am working on a spreadsheet to measure agent performance. I want to
> rank
> > agents on the time they spend in "wrap up" after each call. I've tried
> using
> > the RANK formlua but it returns a value #DIV/0! and the error states "the
> > formula or function used is dividing by zero or empty cells".
> >
> > However, the cells are empty. I've tried changing the cell format but its
> > not working.
> >
> > Any Ideas?
> >
> > THanks
>
>
>

Try:

=RANK(A1,$A$1:$A$4,1)

You are not including the cell for which you want to find the rank.



Mangesh


"Michael" <[email protected]> wrote in message
news:[email protected]...
> Hi Mangesh
>
> Here is an example of the data and formula.
>
> A
> 1 0:31:51
> 2 0:00:00
> 3 0:30:20
> 4 0:00:09
>
> The formuala to caculate the rank is =RANK(A1,$A$2:$A$4,1). I've also
tried
> =RANK(A1,$A$2:$A$4,0).
>
> It returns the value #DIV/0! and the error states "the
> > > formula or function used is dividing by zero or empty cells".
>
> The cells are in TIME format. Although I have tried number and general
and
> even text.
>
> Thanks
>
> Michael
>
> "Mangesh Yadav" wrote:
>
> > can you show the exact formula you have used and the inputs to it.
> >
> > Mangesh
> >
> >
> >
> >
> > "Michael" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi there
> > >
> > > I am working on a spreadsheet to measure agent performance. I want to
> > rank
> > > agents on the time they spend in "wrap up" after each call. I've
tried
> > using
> > > the RANK formlua but it returns a value #DIV/0! and the error states
"the
> > > formula or function used is dividing by zero or empty cells".
> > >
> > > However, the cells are empty. I've tried changing the cell format but
its
> > > not working.
> > >
> > > Any Ideas?
> > >
> > > THanks
> >
> >
> >

Hi Mangesh

Thanks for the help but that did not work either. It returns the same error

Michael

"Mangesh Yadav" wrote:

> Try:
>
> =RANK(A1,$A$1:$A$4,1)
>
> You are not including the cell for which you want to find the rank.
>
>
>
> Mangesh
>
>
> "Michael" <[email protected]> wrote in message
> news:[email protected]...
> > Hi Mangesh
> >
> > Here is an example of the data and formula.
> >
> > A
> > 1 0:31:51
> > 2 0:00:00
> > 3 0:30:20
> > 4 0:00:09
> >
> > The formuala to caculate the rank is =RANK(A1,$A$2:$A$4,1). I've also
> tried
> > =RANK(A1,$A$2:$A$4,0).
> >
> > It returns the value #DIV/0! and the error states "the
> > > > formula or function used is dividing by zero or empty cells".
> >
> > The cells are in TIME format. Although I have tried number and general
> and
> > even text.
> >
> > Thanks
> >
> > Michael
> >
> > "Mangesh Yadav" wrote:
> >
> > > can you show the exact formula you have used and the inputs to it.
> > >
> > > Mangesh
> > >
> > >
> > >
> > >
> > > "Michael" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > Hi there
> > > >
> > > > I am working on a spreadsheet to measure agent performance. I want to
> > > rank
> > > > agents on the time they spend in "wrap up" after each call. I've
> tried
> > > using
> > > > the RANK formlua but it returns a value #DIV/0! and the error states
> "the
> > > > formula or function used is dividing by zero or empty cells".
> > > >
> > > > However, the cells are empty. I've tried changing the cell format but
> its
> > > > not working.
> > > >
> > > > Any Ideas?
> > > >
> > > > THanks
> > >
> > >
> > >
>
>
>
>

What have you not told us about your data? Are there any error values
in the range you are actually trying to rank from? What do you get from
=ISNUMBER(A1) ?

If your "times" were all text instead of time values, then the formula
would return #N/A instead of the #DIV/0! that you report. In that case,
you would have to convert to Excel time values [=VALUE(A1) formatted as
time] before ranking, since the RANK function does not support
internally coercing the values.

Jerry

Michael wrote:

> Hi Mangesh
>
> Thanks for the help but that did not work either. It returns the same error
>
> Michael
>
> "Mangesh Yadav" wrote:
>
>
>>Try:
>>
>>=RANK(A1,$A$1:$A$4,1)
>>
>>You are not including the cell for which you want to find the rank.
>>
>>
>>
>>Mangesh
>>
>>
>>"Michael" <[email protected]> wrote in message
>>news:[email protected]...
>>
>>>Hi Mangesh
>>>
>>>Here is an example of the data and formula.
>>>
>>>A
>>>1 0:31:51
>>>2 0:00:00
>>>3 0:30:20
>>>4 0:00:09
>>>
>>>The formuala to caculate the rank is =RANK(A1,$A$2:$A$4,1). I've also
>>>
>>tried
>>
>>>=RANK(A1,$A$2:$A$4,0).
>>>
>>>It returns the value #DIV/0! and the error states "the
>>>
>>>>>formula or function used is dividing by zero or empty cells".
>>>>>
>>>The cells are in TIME format. Although I have tried number and general
>>>
>>and
>>
>>>even text.
>>>
>>>Thanks
>>>
>>>Michael
>>>
>>>"Mangesh Yadav" wrote:
>>>
>>>
>>>>can you show the exact formula you have used and the inputs to it.
>>>>
>>>>Mangesh
>>>>
>>>>
>>>>
>>>>
>>>>"Michael" <[email protected]> wrote in message
>>>>news:[email protected]...
>>>>
>>>>>Hi there
>>>>>
>>>>>I am working on a spreadsheet to measure agent performance. I want to
>>>>>
>>>>rank
>>>>
>>>>>agents on the time they spend in "wrap up" after each call. I've
>>>>>
>>tried
>>
>>>>using
>>>>
>>>>>the RANK formlua but it returns a value #DIV/0! and the error states
>>>>>
>>"the
>>
>>>>>formula or function used is dividing by zero or empty cells".
>>>>>
>>>>>However, the cells are empty. I've tried changing the cell format but
>>>>>
>>its
>>
>>>>>not working.
>>>>>
>>>>>Any Ideas?
>>>>>
>>>>>THanks

Hi Michael,

Your example works fine for me.
Please check what Jerry says in his post.

Mangesh




"Michael" <[email protected]> wrote in message
news:[email protected]...
> Hi Mangesh
>
> Thanks for the help but that did not work either. It returns the same
error
>
> Michael
>
> "Mangesh Yadav" wrote:
>
> > Try:
> >
> > =RANK(A1,$A$1:$A$4,1)
> >
> > You are not including the cell for which you want to find the rank.
> >
> >
> >
> > Mangesh
> >
> >
> > "Michael" <[email protected]> wrote in message
> > news:[email protected]...
> > > Hi Mangesh
> > >
> > > Here is an example of the data and formula.
> > >
> > > A
> > > 1 0:31:51
> > > 2 0:00:00
> > > 3 0:30:20
> > > 4 0:00:09
> > >
> > > The formuala to caculate the rank is =RANK(A1,$A$2:$A$4,1). I've also
> > tried
> > > =RANK(A1,$A$2:$A$4,0).
> > >
> > > It returns the value #DIV/0! and the error states "the
> > > > > formula or function used is dividing by zero or empty cells".
> > >
> > > The cells are in TIME format. Although I have tried number and
general
> > and
> > > even text.
> > >
> > > Thanks
> > >
> > > Michael
> > >
> > > "Mangesh Yadav" wrote:
> > >
> > > > can you show the exact formula you have used and the inputs to it.
> > > >
> > > > Mangesh
> > > >
> > > >
> > > >
> > > >
> > > > "Michael" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > Hi there
> > > > >
> > > > > I am working on a spreadsheet to measure agent performance. I
want to
> > > > rank
> > > > > agents on the time they spend in "wrap up" after each call. I've
> > tried
> > > > using
> > > > > the RANK formlua but it returns a value #DIV/0! and the error
states
> > "the
> > > > > formula or function used is dividing by zero or empty cells".
> > > > >
> > > > > However, the cells are empty. I've tried changing the cell format
but
> > its
> > > > > not working.
> > > > >
> > > > > Any Ideas?
> > > > >
> > > > > THanks
> > > >
> > > >
> > > >
> >
> >
> >
> >