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

#### srikk7772

##### New Member
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.

 0.0001 6 -0.0001 6 0.0009 6 -0.0009 6 0.001 5 -0.001 5 0.0019 5 -0.0019 5 0.002 4 -0.002 4 0.0029 4 -0.0029 4 0.003 3 -0.003 3 0.0039 3 -0.0039 3 0.004 2 -0.004 2 0.0049 2 -0.0049 2 0.005 1 -0.005 1

<tbody>
</tbody>

Last edited:

### Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.
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.

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.

 0.3684 0.386 0.3274 0.2385 0.134 0.1204 0.1604 0.1234 0.152 0.0989 0.009 0.0224 -0.2396 -0.281 -0.2451 -0.279 -0.2875 -0.2341 -0.281 -0.281 -0.2611 -0.1925 -0.3056 -0.5661

<tbody>
</tbody>

Last edited:
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?

Last edited:

Disregard my post

M.

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

M.

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.

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

Replies
7
Views
336
Replies
10
Views
778
Replies
4
Views
429
Replies
12
Views
3K
Replies
3
Views
787

1,203,045
Messages
6,053,188
Members
444,644
Latest member
keepontruckinc4

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back