Professor J
asked on
excel ranking amounts
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Tom Farrar
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?
ASKER
yes yes
ASKER
John,
your formula seem to work, but there is one small issue. it also shows 0 . i want from 1 to 20 not 0
your formula seem to work, but there is one small issue. it also shows 0 . i want from 1 to 20 not 0
ASKER
John
do i need to change the PERCENTRANK.INC to PERCENTRANK.EXC?
do i need to change the PERCENTRANK.INC to PERCENTRANK.EXC?
ASKER
John,
PERCENTRANK.EXC does not work. all i need is in your original formula that does not rank 0
PERCENTRANK.EXC does not work. all i need is in your original formula that does not rank 0
Something like the attached. Credit to John for the PercentRank function.
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
The lowest number seems to fall out of the range.
John
ASKER
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($
it solved the problem except leaving one amount which is 5370 with zero .
any idea how to solve this?
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($
it solved the problem except leaving one amount which is 5370 with zero .
any idea how to solve this?
Change INC to EXC. Does that give you what you want?
Yes, increasing the significance and changing to EXC seems to do the trick.
John
John
ASKER
thank you John.
your intial formula and then changing the inc to exc solved the issue.
i accepted your solution
your intial formula and then changing the inc to exc solved the issue.
i accepted your solution
Glad changing to EXC helped.
ASKER
thank you tom