G
Guest
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?
2))