We help IT Professionals succeed at work.

excel ranking amounts

Professor J
Professor J asked
on
123 Views
Last Modified: 2014-08-27
I have in column A the amounts.  in column be i want to give them ranks between 1 to 20  
the buffer for change from one rank to another would be 5% one rank to another.  


please let me know if it is not clear.



Column of Amount
254206
324168
358382
514388
564586
832547
884214
1055234
1155106
1218166
1245709
1446298
1796747
1894497
1921254
1933139
2077290
2106388
2169413
2469810
2530759
2537173
2563622
2948527
2959577
2973120
3006114
3135950
3436625
3472997
3862000
3979547
4129793
4298024
4327374
4616533
4703680
226050
710630
343289
640780
43112
171384
495652
324096
923858
267228
16080
108425
423669
190244
348497
559542
470272
893880
738767
5370
524890
562580
289573
92572
136084
407428
235285
177762
814444
276129
306647
363127
853137
990962
911198
816955
396386
755812
891251
805960
870217
136734
854708
768971
629077
819388
53249
74475
439187
506479
386426
536908
865897
617993
944542
468073
881403
165268
732619
681233
581530
936041
484106
155943
384404
622574
11973
711421
712959
212978
116718
733530
210305
141837
476925
698801
755127
315227
285640
345150
289508
508367
749213
942175
81675
416097
342962
420082
432934
797041
669615
457386
635410
34325
99114
54117
40906
57895
712993
168773
375857
145545
366554
649508
162810
139857
398807
988915
525621
217428
219122
549144
29465
592861
945075
844096
155770
686463
978360
85228
325219
934875
625440
849943
105864
697170
457251
254805
990284
978012
38696
544446
938388
Comment
Watch Question

Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Tom FarrarConsultant
CERTIFIED EXPERT

Commented:
You mean like add a column that shows the % where the number falls in increments of 5% so as to group all the numbers from 1-5% in group one, 6-10% in group two, etc?
Professor JMicrosoft Excel Expert
CERTIFIED EXPERT
Top Expert 2014

Author

Commented:
yes yes
Professor JMicrosoft Excel Expert
CERTIFIED EXPERT
Top Expert 2014

Author

Commented:
John,  

your formula seem to work, but there is one small issue.  it also shows 0   .  i want from 1 to 20 not 0
Professor JMicrosoft Excel Expert
CERTIFIED EXPERT
Top Expert 2014

Author

Commented:
John
do i need to change the PERCENTRANK.INC  to PERCENTRANK.EXC?
Professor JMicrosoft Excel Expert
CERTIFIED EXPERT
Top Expert 2014

Author

Commented:
John,

PERCENTRANK.EXC does not work.  all i need is in your original formula that   does not rank 0
Tom FarrarConsultant
CERTIFIED EXPERT

Commented:
Something like the attached.  Credit to John for the PercentRank function.

Commented:
If you add the number 1 to your data it seems to work....1 returns 0, and the other values have a percentile value.
The lowest number seems to fall out of the range.

John
Professor JMicrosoft Excel Expert
CERTIFIED EXPERT
Top Expert 2014

Author

Commented:
John,

the data i attached here was a dummy as a sample.  my data is huge like the range is from 5000 to 10 million.

i changed the significance of formula from 2 to 5 =ROUNDUP(PERCENTRANK.INC($A$2:$A$171,A2,5)/5*100,0)

it solved the problem except leaving one amount which is 5370 with zero .

any idea how to solve this?
Tom FarrarConsultant
CERTIFIED EXPERT

Commented:
Change INC to EXC.  Does that give you what you want?

Commented:
Yes, increasing the significance and changing to EXC seems to do the trick.

John
Professor JMicrosoft Excel Expert
CERTIFIED EXPERT
Top Expert 2014

Author

Commented:
thank you John.

your intial formula and then changing the inc to exc solved the issue.

i accepted your solution
Tom FarrarConsultant
CERTIFIED EXPERT

Commented:
Glad changing to EXC helped.
Professor JMicrosoft Excel Expert
CERTIFIED EXPERT
Top Expert 2014

Author

Commented:
thank you tom

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.