Need help with RANK function

Does anyone know how to rank a group of numbers without having excel give 2 alike numbers the lower value instead of splitting the difference between the 2 tied values? Say I'm ranking A1:A12 and two of the cells "tie" for highest value. Instead of giving each cell a rank of 11.5, excel gives both 11. Does anyone know a workaround for this issue? I need the sum of the rankings to equal 1+2+3+4+5+6+7+8+9+10+11+12 and everytime there is a tie i lose a unit. Please help.

Instead of using the SUM function, use the following formula to calculate your required sum:

=SUM(ROW(INDIRECT("1:"&COUNT(A1:A12))))

press control shift enter

no need to calculate the rank specially for summing purpose.


Mangesh

Have you tried searching the Microsoft Knowledge Base?
http://support.microsoft.com/kb/828678
may be relevant.

Jerry

butters14 wrote:

> Does anyone know how to rank a group of numbers without having excel
> give 2 alike numbers the lower value instead of splitting the
> difference between the 2 tied values? Say I'm ranking A1:A12 and two of
> the cells "tie" for highest value. Instead of giving each cell a rank of
> 11.5, excel gives both 11. Does anyone know a workaround for this issue?
> I need the sum of the rankings to equal 1+2+3+4+5+6+7+8+9+10+11+12 and
> everytime there is a tie i lose a unit. Please help.

Have you tried searching the Microsoft Knowledge Base?
http://support.microsoft.com/kb/828678
may be relevant.

Jerry

butters14 wrote:

> Does anyone know how to rank a group of numbers without having excel
> give 2 alike numbers the lower value instead of splitting the
> difference between the 2 tied values? Say I'm ranking A1:A12 and two of
> the cells "tie" for highest value. Instead of giving each cell a rank of
> 11.5, excel gives both 11. Does anyone know a workaround for this issue?
> I need the sum of the rankings to equal 1+2+3+4+5+6+7+8+9+10+11+12 and
> everytime there is a tie i lose a unit. Please help.

Have you tried searching the Microsoft Knowledge Base?
http://support.microsoft.com/kb/828678
may be relevant.

Jerry

butters14 wrote:

> Does anyone know how to rank a group of numbers without having excel
> give 2 alike numbers the lower value instead of splitting the
> difference between the 2 tied values? Say I'm ranking A1:A12 and two of
> the cells "tie" for highest value. Instead of giving each cell a rank of
> 11.5, excel gives both 11. Does anyone know a workaround for this issue?
> I need the sum of the rankings to equal 1+2+3+4+5+6+7+8+9+10+11+12 and
> everytime there is a tie i lose a unit. Please help.

Have you tried searching the Microsoft Knowledge Base?
http://support.microsoft.com/kb/828678
may be relevant.

Jerry

butters14 wrote:

> Does anyone know how to rank a group of numbers without having excel
> give 2 alike numbers the lower value instead of splitting the
> difference between the 2 tied values? Say I'm ranking A1:A12 and two of
> the cells "tie" for highest value. Instead of giving each cell a rank of
> 11.5, excel gives both 11. Does anyone know a workaround for this issue?
> I need the sum of the rankings to equal 1+2+3+4+5+6+7+8+9+10+11+12 and
> everytime there is a tie i lose a unit. Please help.

Have you tried searching the Microsoft Knowledge Base?
http://support.microsoft.com/kb/828678
may be relevant.

Jerry

butters14 wrote:

> Does anyone know how to rank a group of numbers without having excel
> give 2 alike numbers the lower value instead of splitting the
> difference between the 2 tied values? Say I'm ranking A1:A12 and two of
> the cells "tie" for highest value. Instead of giving each cell a rank of
> 11.5, excel gives both 11. Does anyone know a workaround for this issue?
> I need the sum of the rankings to equal 1+2+3+4+5+6+7+8+9+10+11+12 and
> everytime there is a tie i lose a unit. Please help.

Have you tried searching the Microsoft Knowledge Base?
http://support.microsoft.com/kb/828678
may be relevant.

Jerry

butters14 wrote:

> Does anyone know how to rank a group of numbers without having excel
> give 2 alike numbers the lower value instead of splitting the
> difference between the 2 tied values? Say I'm ranking A1:A12 and two of
> the cells "tie" for highest value. Instead of giving each cell a rank of
> 11.5, excel gives both 11. Does anyone know a workaround for this issue?
> I need the sum of the rankings to equal 1+2+3+4+5+6+7+8+9+10+11+12 and
> everytime there is a tie i lose a unit. Please help.

Have you tried searching the Microsoft Knowledge Base?
http://support.microsoft.com/kb/828678
may be relevant.

Jerry

butters14 wrote:

> Does anyone know how to rank a group of numbers without having excel
> give 2 alike numbers the lower value instead of splitting the
> difference between the 2 tied values? Say I'm ranking A1:A12 and two of
> the cells "tie" for highest value. Instead of giving each cell a rank of
> 11.5, excel gives both 11. Does anyone know a workaround for this issue?
> I need the sum of the rankings to equal 1+2+3+4+5+6+7+8+9+10+11+12 and
> everytime there is a tie i lose a unit. Please help.

Have you tried searching the Microsoft Knowledge Base?
http://support.microsoft.com/kb/828678
may be relevant.

Jerry

butters14 wrote:

> Does anyone know how to rank a group of numbers without having excel
> give 2 alike numbers the lower value instead of splitting the
> difference between the 2 tied values? Say I'm ranking A1:A12 and two of
> the cells "tie" for highest value. Instead of giving each cell a rank of
> 11.5, excel gives both 11. Does anyone know a workaround for this issue?
> I need the sum of the rankings to equal 1+2+3+4+5+6+7+8+9+10+11+12 and
> everytime there is a tie i lose a unit. Please help.

Have you tried searching the Microsoft Knowledge Base?
http://support.microsoft.com/kb/828678
may be relevant.

Jerry

butters14 wrote:

> Does anyone know how to rank a group of numbers without having excel
> give 2 alike numbers the lower value instead of splitting the
> difference between the 2 tied values? Say I'm ranking A1:A12 and two of
> the cells "tie" for highest value. Instead of giving each cell a rank of
> 11.5, excel gives both 11. Does anyone know a workaround for this issue?
> I need the sum of the rankings to equal 1+2+3+4+5+6+7+8+9+10+11+12 and
> everytime there is a tie i lose a unit. Please help.

Have you tried searching the Microsoft Knowledge Base?
http://support.microsoft.com/kb/828678
may be relevant.

Jerry

butters14 wrote:

> Does anyone know how to rank a group of numbers without having excel
> give 2 alike numbers the lower value instead of splitting the
> difference between the 2 tied values? Say I'm ranking A1:A12 and two of
> the cells "tie" for highest value. Instead of giving each cell a rank of
> 11.5, excel gives both 11. Does anyone know a workaround for this issue?
> I need the sum of the rankings to equal 1+2+3+4+5+6+7+8+9+10+11+12 and
> everytime there is a tie i lose a unit. Please help.

Have you tried searching the Microsoft Knowledge Base?
http://support.microsoft.com/kb/828678
may be relevant.

Jerry

butters14 wrote:

> Does anyone know how to rank a group of numbers without having excel
> give 2 alike numbers the lower value instead of splitting the
> difference between the 2 tied values? Say I'm ranking A1:A12 and two of
> the cells "tie" for highest value. Instead of giving each cell a rank of
> 11.5, excel gives both 11. Does anyone know a workaround for this issue?
> I need the sum of the rankings to equal 1+2+3+4+5+6+7+8+9+10+11+12 and
> everytime there is a tie i lose a unit. Please help.