forcing a rank on 1 through 5 (no dups) using the rank formula in Excel

I calculated spread percents on 12 months, and I would like to rank them so that they are mutually exclusive (by row) with ranks of 1, 2, 3, 4, and 5 (and only 5, with no dups).

These spread percents are based on the number of days in each month, and add up to 1 (100%) across 12 months.

My formula is =IF(RANK(Month1,$Month1:$Month12,0)>5,0,(RANK(Month1,$Month1:$Month12,0)))

I only want to end up with 5 months (I am using this to allocate unit variance due to rounding - the maximum variance is 5 units).

Is there a way to force a rank of 1, 2, 3, 4, and 5? Since a lot of the months have equal numbers of days (and therefore the spread percents are equal), I am ending up with ranks equal ranks, and more than 5 for a lot of my rows. I understand this mathematically, I just wondered if anyone knows of a formula that would force what I am trying to achieve.

I tried pasting in my table, sorry it's so messy. The first table is the spread percents, the second table are the ranks I am getting using my rank formula. If you can let me know a better way to paste the table I will, I wasn't sure how to save it as a jpg file.

Thanks for any help!


spread percents
Entity Type Service 01 02 03 04 05 06 07 08 09 10 11 12
3201 IP Acute 0.0838 0.0862 0.0831 0.0873 0.0816 0.0798 0.0859 0.0776 0.0837 0.0826 0.0854 0.0830
3201 IP Cardiac 0.0863 0.0896 0.0793 0.0891 0.0891 0.0859 0.0884 0.0725 0.0827 0.0791 0.0791 0.0789
3201 IP CardiacSurg 0.0863 0.0896 0.0793 0.0891 0.0891 0.0859 0.0884 0.0725 0.0827 0.0791 0.0791 0.0789
3201 IP Detox 0.0808 0.0961 0.0727 0.0823 0.0786 0.0720 0.0900 0.0738 0.0874 0.0852 0.0911 0.0900
3201 IP Maternity 0.0869 0.0895 0.0876 0.0869 0.0733 0.0768 0.0823 0.0779 0.0838 0.0787 0.0865 0.0898
3201 IP Medical 0.0876 0.0863 0.0812 0.0839 0.0792 0.0838 0.0884 0.0770 0.0838 0.0832 0.0857 0.0799
3201 IP MedSurg 0.0837 0.0854 0.0820 0.0879 0.0816 0.0815 0.0872 0.0777 0.0833 0.0830 0.0850 0.0817
3201 IP Newborn 0.0866 0.0895 0.0854 0.0866 0.0732 0.0783 0.0828 0.0777 0.0832 0.0786 0.0870 0.0911
3201 IP SARehab 0.0818 0.0864 0.0820 0.0864 0.0841 0.0798 0.0838 0.0811 0.0871 0.0820 0.0818 0.0837
3201 IP SPARC (Days) 0.0818 0.0864 0.0820 0.0864 0.0841 0.0798 0.0838 0.0811 0.0871 0.0820 0.0818 0.0837
3201 IP Surgical 0.0776 0.0829 0.0837 0.0931 0.0828 0.0771 0.0852 0.0801 0.0828 0.0839 0.0858 0.0850
3201 IP NICU 0.0841 0.0841 0.0877 0.0841 0.0835 0.0773 0.0800 0.0771 0.0856 0.0841 0.0925 0.0800
3201 OP Dental Van 0.0000 0.0000 0.0918 0.1111 0.0966 0.0918 0.1014 0.0966 0.1063 0.1014 0.1014 0.1016
3201 OP Peds 0.0828 0.0861 0.0828 0.0861 0.0762 0.0828 0.0828 0.0795 0.0894 0.0861 0.0795 0.0859
3201 OP NumDays 0.0840 0.0840 0.0840 0.0840 0.0760 0.0840 0.0800 0.0800 0.0920 0.0840 0.0800 0.0880
3225 OP NumDays 0.0859 0.0820 0.0820 0.0859 0.0781 0.0859 0.0781 0.0820 0.0898 0.0820 0.0820 0.0863
3226 OP NumDays 0.0840 0.0840 0.0840 0.0840 0.0760 0.0840 0.0800 0.0800 0.0920 0.0840 0.0800 0.0880
3227 OP NumDays 0.0859 0.0820 0.0820 0.0859 0.0781 0.0859 0.0781 0.0820 0.0898 0.0820 0.0820 0.0863
3228 OP NumDays 0.0840 0.0840 0.0840 0.0840 0.0760 0.0840 0.0800 0.0800 0.0920 0.0840 0.0800 0.0880

ranks (using rank formula)
Entity Type Service 01 02 03 04 05 06 07 08 09 10 11 12
3201 IP Acute 5 2 0 1 0 0 3 0 0 0 4 0
3201 IP Cardiac 5 1 0 2 2 0 4 0 0 0 0 0
3201 IP CardiacSurg 5 1 0 2 2 0 4 0 0 0 0 0
3201 IP Detox 0 1 0 0 0 0 3 0 5 0 2 3
3201 IP Maternity 4 2 3 4 0 0 0 0 0 0 0 1
3201 IP Medical 2 3 0 5 0 0 1 0 0 0 4 0
3201 IP MedSurg 5 3 0 1 0 0 2 0 0 0 4 0
3201 IP Newborn 4 2 0 4 0 0 0 0 0 0 3 1
3201 IP SARehab 0 2 0 2 4 0 5 0 1 0 0 0
3201 IP SPARC (Days) 0 2 0 2 4 0 5 0 1 0 0 0
3201 IP Surgical 0 0 0 1 0 0 3 0 0 5 2 4
3201 IP NICU 4 4 2 4 0 0 0 0 3 4 1 0
3201 OP Dental Van 0 0 0 1 0 0 4 0 2 4 4 3
3201 OP Peds 0 2 0 2 0 0 0 0 1 2 0 5
3201 OP NumDays 3 3 3 3 0 3 0 0 1 3 0 2
3225 OP NumDays 3 0 0 3 0 3 0 0 1 0 0 2
3226 OP NumDays 3 3 3 3 0 3 0 0 1 3 0 2
3227 OP NumDays 3 0 0 3 0 3 0 0 1 0 0 2
3228 OP NumDays 3 3 3 3 0 3 0 0 1 3 0 2

one of the two 6(s)

It totally worked!!! Thank you so so much! I really appreciate your help