M
Maxi
Stage,Data,1st,2nd,3rd,4th,5th,6th,7th,8th,9th,10th
Stage1,2,2,0,1,3,4,5,6,7,8,9
Stage2,4,4,2,0,1,3,5,6,7,8,9
Stage3,0,0,4,2,1,3,5,6,7,8,9
Stage4,2,2,0,4,1,3,5,6,7,8,9
Stage5,2,2,0,4,1,3,5,6,7,8,9
Stage6,1,2,1,0,4,3,5,6,7,8,9
Stage7,4,2,4,1,0,3,5,6,7,8,9
Stage8,2,2,4,1,0,3,5,6,7,8,9
Stage9,4,2,4,1,0,3,5,6,7,8,9
Stage10,0,2,4,0,1,3,5,6,7,8,9
Stage11,7,2,4,0,7,1,3,5,6,8,9
Stage12,0,2,0,4,7,1,3,5,6,8,9
Stage13,1,2,0,4,1,7,3,5,6,8,9
Stage14,7,2,0,4,7,1,3,5,6,8,9
Stage15,9,2,0,4,7,1,9,3,5,6,8
I have the above data in range A1:L16. Data in range A1:B16 is the real
data and data in range C2:L16 is the work that I did it manually.
Actually I am looking for formulas in the range C2:L16.
I want to rank the numbers in column B based on their frequencies for
all the stages. For example, number in cell B2 is 2 hence the number 2
should hold 1st rank therefore I have put number 2 in cell C2 for 1st
rank.
When you move to stage2, you should consider both stage1 and stage2.
For example, in Stage2, both the numbers 2 and 4 have appeared once, in
this case the number which is at the bottom should get 1st rank
therefore I have put number 4 in cell C3 for 1st rank and the number 2
in cell D3 for second rank.
Whenever two or more numbers are of the same frequencies, then take the
number at the bottom of the range/array as the top rank. For example,
in Stage12 (range B2:B13) , the number 2 has the highest frequency
(4x), hence 1st rank (cell C13). Number 0 and 4 both appeared thrice
but the number 0 is at the bottom of the range hence rank 2nd (cell
D13)for 0 and rank 3rd for 4 (cell E13). Number 1 and 7 appreared once
but 7 is at the bottom of the range hence rank 4th for 7 (cell F13) and
rank 5th for 1 (cell G13).
If a particular number is not appeared even once, then rank them in
ascending order, example, in stage 12, the numbers 3 5 6 8 9 did not
appear even once, hence I have arranged them in ascening order for
their ranks (range H13:L13)
I tried to use temporary columns with the RANK worksheet function with
combination of other formulas like COUNTIF, MATCH, FREQUENCY etc but I
am not able to work it out. I also tried writing a VBA code but I am
getting confused on counters.
A formula based solution will be appreciated but if it is not possible,
then excel vba/macro will also do.
Can anybody help me?
Stage1,2,2,0,1,3,4,5,6,7,8,9
Stage2,4,4,2,0,1,3,5,6,7,8,9
Stage3,0,0,4,2,1,3,5,6,7,8,9
Stage4,2,2,0,4,1,3,5,6,7,8,9
Stage5,2,2,0,4,1,3,5,6,7,8,9
Stage6,1,2,1,0,4,3,5,6,7,8,9
Stage7,4,2,4,1,0,3,5,6,7,8,9
Stage8,2,2,4,1,0,3,5,6,7,8,9
Stage9,4,2,4,1,0,3,5,6,7,8,9
Stage10,0,2,4,0,1,3,5,6,7,8,9
Stage11,7,2,4,0,7,1,3,5,6,8,9
Stage12,0,2,0,4,7,1,3,5,6,8,9
Stage13,1,2,0,4,1,7,3,5,6,8,9
Stage14,7,2,0,4,7,1,3,5,6,8,9
Stage15,9,2,0,4,7,1,9,3,5,6,8
I have the above data in range A1:L16. Data in range A1:B16 is the real
data and data in range C2:L16 is the work that I did it manually.
Actually I am looking for formulas in the range C2:L16.
I want to rank the numbers in column B based on their frequencies for
all the stages. For example, number in cell B2 is 2 hence the number 2
should hold 1st rank therefore I have put number 2 in cell C2 for 1st
rank.
When you move to stage2, you should consider both stage1 and stage2.
For example, in Stage2, both the numbers 2 and 4 have appeared once, in
this case the number which is at the bottom should get 1st rank
therefore I have put number 4 in cell C3 for 1st rank and the number 2
in cell D3 for second rank.
Whenever two or more numbers are of the same frequencies, then take the
number at the bottom of the range/array as the top rank. For example,
in Stage12 (range B2:B13) , the number 2 has the highest frequency
(4x), hence 1st rank (cell C13). Number 0 and 4 both appeared thrice
but the number 0 is at the bottom of the range hence rank 2nd (cell
D13)for 0 and rank 3rd for 4 (cell E13). Number 1 and 7 appreared once
but 7 is at the bottom of the range hence rank 4th for 7 (cell F13) and
rank 5th for 1 (cell G13).
If a particular number is not appeared even once, then rank them in
ascending order, example, in stage 12, the numbers 3 5 6 8 9 did not
appear even once, hence I have arranged them in ascening order for
their ranks (range H13:L13)
I tried to use temporary columns with the RANK worksheet function with
combination of other formulas like COUNTIF, MATCH, FREQUENCY etc but I
am not able to work it out. I also tried writing a VBA code but I am
getting confused on counters.
A formula based solution will be appreciated but if it is not possible,
then excel vba/macro will also do.
Can anybody help me?