|
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. |
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. |
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. |
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. |
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. |
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. |
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. . |
| All times are GMT +1. The time now is 04:05 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com