| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
RANK Function
I'm sure there is an easy way of doing this ...
I have a list of data values that I want to rank in terms of the nearest/furthest away from zero. Some of the values are positive and some are negative. How do I use RANK such that No 1 is the value (positive or negative) that is nearest to zero, No 2 is next nearest, etc? Many thanks. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
RANK Function
You could use a helper column and put this formula in it:
=ABS(A1) and copy this down - assume it is in column B. Then you could apply the rank formula like this: =RANK(B1,B$1:B$10,1) and copy this down. Hope this helps. Pete On Oct 16, 4:50*pm, "Terry Bennett" wrote: I'm sure there is an easy way of doing this ... I have a list of data values that I want to rank in terms of the nearest/furthest away from zero. *Some of the values are positive and some are negative. How do I use RANK such that No 1 is the value (positive or negative) that is nearest to zero, No 2 is next nearest, etc? Many thanks. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
RANK Function
Try this...
Data in the range A1:A10 Enter this formula in B1 and copy down to B10: =SUMPRODUCT(--(ABS(A1)=ABS(A$1:A$10))) -- Biff Microsoft Excel MVP "Terry Bennett" wrote in message ... I'm sure there is an easy way of doing this ... I have a list of data values that I want to rank in terms of the nearest/furthest away from zero. Some of the values are positive and some are negative. How do I use RANK such that No 1 is the value (positive or negative) that is nearest to zero, No 2 is next nearest, etc? Many thanks. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
RANK Function
Correction...
If you want to exactly emulate the RANK function: =SUMPRODUCT(--(ABS(A1)ABS(A$1:A$10)))+1 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this... Data in the range A1:A10 Enter this formula in B1 and copy down to B10: =SUMPRODUCT(--(ABS(A1)=ABS(A$1:A$10))) -- Biff Microsoft Excel MVP "Terry Bennett" wrote in message ... I'm sure there is an easy way of doing this ... I have a list of data values that I want to rank in terms of the nearest/furthest away from zero. Some of the values are positive and some are negative. How do I use RANK such that No 1 is the value (positive or negative) that is nearest to zero, No 2 is next nearest, etc? Many thanks. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
RANK Function
Many thanks Pete/Biff - that's what I needed!
"T. Valko" wrote in message ... Correction... If you want to exactly emulate the RANK function: =SUMPRODUCT(--(ABS(A1)ABS(A$1:A$10)))+1 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this... Data in the range A1:A10 Enter this formula in B1 and copy down to B10: =SUMPRODUCT(--(ABS(A1)=ABS(A$1:A$10))) -- Biff Microsoft Excel MVP "Terry Bennett" wrote in message ... I'm sure there is an easy way of doing this ... I have a list of data values that I want to rank in terms of the nearest/furthest away from zero. Some of the values are positive and some are negative. How do I use RANK such that No 1 is the value (positive or negative) that is nearest to zero, No 2 is next nearest, etc? Many thanks. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
RANK Function
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Terry Bennett" wrote in message ... Many thanks Pete/Biff - that's what I needed! "T. Valko" wrote in message ... Correction... If you want to exactly emulate the RANK function: =SUMPRODUCT(--(ABS(A1)ABS(A$1:A$10)))+1 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this... Data in the range A1:A10 Enter this formula in B1 and copy down to B10: =SUMPRODUCT(--(ABS(A1)=ABS(A$1:A$10))) -- Biff Microsoft Excel MVP "Terry Bennett" wrote in message ... I'm sure there is an easy way of doing this ... I have a list of data values that I want to rank in terms of the nearest/furthest away from zero. Some of the values are positive and some are negative. How do I use RANK such that No 1 is the value (positive or negative) that is nearest to zero, No 2 is next nearest, etc? Many thanks. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
RANK Function
I have the very same issue. However, I have sub total between every 4 or 5
rows. so I would need the fomular to jump the total I may have 5 of them. If or is that possible? +2 +4 +3 total +8 +6 +9 total Please help... "T. Valko" wrote: You're welcome. Thanks for the feedback! -- Biff Microsoft Excel MVP "Terry Bennett" wrote in message ... Many thanks Pete/Biff - that's what I needed! "T. Valko" wrote in message ... Correction... If you want to exactly emulate the RANK function: =SUMPRODUCT(--(ABS(A1)ABS(A$1:A$10)))+1 -- Biff Microsoft Excel MVP "T. Valko" wrote in message ... Try this... Data in the range A1:A10 Enter this formula in B1 and copy down to B10: =SUMPRODUCT(--(ABS(A1)=ABS(A$1:A$10))) -- Biff Microsoft Excel MVP "Terry Bennett" wrote in message ... I'm sure there is an easy way of doing this ... I have a list of data values that I want to rank in terms of the nearest/furthest away from zero. Some of the values are positive and some are negative. How do I use RANK such that No 1 is the value (positive or negative) that is nearest to zero, No 2 is next nearest, etc? Many thanks. . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need help with RANK function | Excel Worksheet Functions | |||
| using RANK function | Excel Discussion (Misc queries) | |||
| Rank function | Excel Worksheet Functions | |||
| Rank Function | Excel Worksheet Functions | |||
| Rank Function | Excel Discussion (Misc queries) | |||
Linear Mode
