Ranking positive and negative decimal numbers. Actual Inputs and Expected Outputs are shared for help.

[srikk7772]

Hi,

Please find four columns -> 1st and 3rd columns and positive and negative numbers, Input columns, and 2nd and 4th columns are the results I would like to achieve in ms excel with your help.

[TABLE="width: 320"]
<tbody>[TR]
[TD="class: xl65, width: 64, align: right"]0.0001[/TD]
[TD="width: 64, align: right"]6[/TD]
[TD="width: 64"][/TD]
[TD="class: xl65, width: 64, align: right"]-0.0001[/TD]
[TD="width: 64, align: right"]6[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]0.0009[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]-0.0009[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]0.0010[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]-0.0010[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]0.0019[/TD]
[TD="align: right"]5[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]-0.0019[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]0.0020[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]-0.0020[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]0.0029[/TD]
[TD="align: right"]4[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]-0.0029[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]0.0030[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]-0.0030[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]0.0039[/TD]
[TD="align: right"]3[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]-0.0039[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]0.0040[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]-0.0040[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]0.0049[/TD]
[TD="align: right"]2[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]-0.0049[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl65, align: right"]0.0050[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[TD="class: xl65, align: right"]-0.0050[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[bbott]

Welcome to the board.

Assuming your table starts in A1, try this in B1 and copy down:

=CHOOSE(MATCH(ABS(A1)*1000,{0,1,2,3,4,5,6},1),6,5,4,3,2,1)

Then copy the formula into column D.

 

[srikk7772]

Thanks for your quick reply. The expected output I have put in earlier was a sample.

The expected output I should not define in the formula, the output should be rather natural, understanding these rankings. One more set of test data contains (shared below for your help again) both negative and positive numbers, numbers may or may not repeat.

[TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64, align: right"]0.3684[/TD]
[/TR]
[TR]
[TD="align: right"]0.386[/TD]
[/TR]
[TR]
[TD="align: right"]0.3274[/TD]
[/TR]
[TR]
[TD="align: right"]0.2385[/TD]
[/TR]
[TR]
[TD="align: right"]0.134[/TD]
[/TR]
[TR]
[TD="align: right"]0.1204[/TD]
[/TR]
[TR]
[TD="align: right"]0.1604[/TD]
[/TR]
[TR]
[TD="align: right"]0.1234[/TD]
[/TR]
[TR]
[TD="align: right"]0.152[/TD]
[/TR]
[TR]
[TD="align: right"]0.0989[/TD]
[/TR]
[TR]
[TD="align: right"]0.009[/TD]
[/TR]
[TR]
[TD="align: right"]0.0224[/TD]
[/TR]
[TR]
[TD="align: right"]-0.2396[/TD]
[/TR]
[TR]
[TD="align: right"]-0.281[/TD]
[/TR]
[TR]
[TD="align: right"]-0.2451[/TD]
[/TR]
[TR]
[TD="align: right"]-0.279[/TD]
[/TR]
[TR]
[TD="align: right"]-0.2875[/TD]
[/TR]
[TR]
[TD="align: right"]-0.2341[/TD]
[/TR]
[TR]
[TD="align: right"]-0.281[/TD]
[/TR]
[TR]
[TD="align: right"]-0.281[/TD]
[/TR]
[TR]
[TD="align: right"]-0.2611[/TD]
[/TR]
[TR]
[TD="align: right"]-0.1925[/TD]
[/TR]
[TR]
[TD="align: right"]-0.3056[/TD]
[/TR]
[TR]
[TD="align: right"]-0.5661[/TD]
[/TR]
</tbody>[/TABLE]

 

[bbott]

The original formula worked for thousandths. If they will be intermingled, this formula won't work. This will work for tenths. But again, it won't work if the data is intermingled (tenths, hundredths, thousandths together).

=CHOOSE(MATCH(ABS(A1)*10,{0,1,2,3,4,5,6},1),6,5,4,3,2,1)

What are your actual parameters for determining rank?

 

[Marcelo Branco]

Sorry i misunderstood your problem

Disregard my post

M.

 

[Marcelo Branco]

Could you, please, tell us the expected output using the data sample in post #3?

M.

 

[srikk7772]

Few inputs that can share what I'm longing for..

-> Start of the negative series is -0.0001 and end of it can be, say -249.9999.
-> Start of the positive series is 0.0001 and end of it can be, say 249.9999.

In A1 and A2, please type -0.0001 and -0.0009, format these numbers using Format -> Numbers -> 4 digits. In A3 and A4, please type the formula =+A3+(-0.0001) and =+A4+(-0.0009) respectively. Select A3 and A4, drag them down say till row 100. You should now see a negative number series as shared in Post # 1.

-0.0001 and -0.0009 belongs to one family and can be given a rank of 10000.
-0.0010 and -0.0019 belongs to one family and can be given a rank of 10001 and rank should be assigned say till 19999.

Similarly, assign a rank of 20000 to 29999 for positive series.

Use these ranks (10000, 10001, 10002, 25786, 25787 and so on) in turn now on the test data posted in Post # 3 for the actual output.

If there is a quick and natural way to get this done in MS Excel, it would be great. In short, I'm basically looking for Steadiness in the data.

Please use 4 digits in the test numbers. Hope this helps. Eagerly waiting for help.

 

[srikk7772]

Anybody there to help me on this please ? If this is directly not possible in MS Excel, probably you can help me through VBA code. I'm unfamiliar with VBA code, but I can quickly grasp it. Await your hand of help still