Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
RANK ABSOLUTE NUMBER AND WITHOUT GAP
The value that I need to rank include positive and negative number. I need
to treat them all absolute value and also need to rank without gap. VALUE RANK RESULT -15 6 14 5 13 4 -12 3 -10 2 9 1 -9 1 Any ideas? -- M&M |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
RANK ABSOLUTE NUMBER AND WITHOUT GAP
Hi,
If you pull the absolute value out to another column =ABS(A2) and drag down then you can use this formula =SUMPRODUCT(--(A2$A$2:$A$8),1/COUNTIF($A$2:$A$8,$A$2:$A$8&""))+1 I'm sure it can be done without a helper column but I can't see it for the moment. Mike "M&M" wrote: The value that I need to rank include positive and negative number. I need to treat them all absolute value and also need to rank without gap. VALUE RANK RESULT -15 6 14 5 13 4 -12 3 -10 2 9 1 -9 1 Any ideas? -- M&M |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
RANK ABSOLUTE NUMBER AND WITHOUT GAP
Yes, I understand I can do it with the formula Mike suggested but I am trying
to find a solution without a helper column. -- M&M "Mike H" wrote: Hi, If you pull the absolute value out to another column =ABS(A2) and drag down then you can use this formula =SUMPRODUCT(--(A2$A$2:$A$8),1/COUNTIF($A$2:$A$8,$A$2:$A$8&""))+1 I'm sure it can be done without a helper column but I can't see it for the moment. Mike "M&M" wrote: The value that I need to rank include positive and negative number. I need to treat them all absolute value and also need to rank without gap. VALUE RANK RESULT -15 6 14 5 13 4 -12 3 -10 2 9 1 -9 1 Any ideas? -- M&M |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
RANK ABSOLUTE NUMBER AND WITHOUT GAP
M&M <Missie.lucky.com wrote...
Yes, I understand I can do it with the formula Mike suggested but I am trying to find a solution without a helper column. .... "M&M" wrote: .... VALUE *RANK RESULT -15 * 6 14 * *5 13 * *4 -12 * 3 -10 * 2 9 * * 1 -9 * *1 .... If the VALUE column is sorted in descending absolute value, then if the result table had -15 in cell A2, try B2 [array formula]: =SUM(1/MMULT(--(ABS(A2:A8)=TRANSPOSE(ABS(A2:A8))),ROW(A2:A8)^0)) B3: =D2-(ABS(A3)<ABS(A2)) Fill B3 down as far as needed. If the VALUE column were unsorted, it gets trickier. Try B2 [array formula]: =SUM((ABS(A$2:A$8)<=ABS(A2))/(MMULT(--(ABS(A$2:A$8)=TRANSPOSE(ABS(A$2:A $8))), --(ABS(A$2:A$8)<=ABS(A2)))+(ABS(A$2:A$8)ABS(A2)))) Fill B2 down as far as needed. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Absolute $ for a number | Excel Worksheet Functions | |||
Max absolute number | Excel Worksheet Functions | |||
how do I 'shadow rank' a number into a field it isn't a part of | Excel Worksheet Functions | |||
Rank Function and Number that are close together | Excel Worksheet Functions | |||
How do I convert hh:mm:ss to an absolute number (26:01:32 to 26)? | Excel Discussion (Misc queries) |